0

Here is the mysql query which i am using:

select * from `jobs` where MATCH(jobs.name,jobs.number,jobs.alt_id) AGAINST('*keyword*' IN BOOLEAN MODE) and company_id = 1

This query is taking around 2 seconds to execute but if i run it without company_id clause then it gives result within some milliseconds, after some investigation i came to know that my company_id index is not working if i run it along with full text search index.

rahul anand
  • 33
  • 4
  • Only one index per each source table copy can be used. How many rows are selected with and without the condition by `company_id`? – Akina Jan 09 '23 at 09:23
  • Without company_id condition the query examine 1500 rows and returning 1500 rows but with company_id condition query examine 7100 rows and returning 400 rows – rahul anand Jan 09 '23 at 09:27
  • Those numbers do not tell the whole story. The structure of the FT index makes it very fast to get the 400 rows, but much slower to check each of the 1500 rows. This may outweigh the very fast search by id. – Rick James Jan 09 '23 at 15:46

0 Answers0