Will moving the MATCH...AGAINST part in different part of the query (e.g. before evaluation of JOIN) will improve the performance? Basically, I have the following query:

select `t`.*
from `tracking` `t` 
where exists 
(   
    select `ti`.`id` from `tracking_items` `ti`
    inner join `cars` `c` on (`c`.`car_id` = `ti`.`tracking_object_id`)
    where `t`.`id` = `ti`.`tracking_id` and match(`c`.`car_text`) against('+bulgaria +turkey' in boolean mode) 
    limit 1
) and ... limit 5


The problem I have?

There's FULLTEXT index for c.car_text column, but however the query is running a bit slowly (especially for records that rarely occurs, e.g. searching for non-existing keyword).

I haven't published the result of EXPLAIN, as I'd like to know the theory behind my question, e.g. for different possible cases.

I use MySQL 5.6 with InnoDB storage engine, also it's worth to mention that cars table have like ~160m records.
I am glad you came here. I have always suggested using FULLTEXT indexes to collect keys first and then join it to other tables.


Oct 25, 2011 : FULLTEXT index ignored in BOOLEAN MODE with &#39;number of words&#39; conditional
Jul 18, 2012 : Why full-text-search returns less rows than LIKE
May 07, 2012 : MySQL EXPLAIN doesn&#39;t show &#39;use index&#39; for FULLTEXT
Jan 29, 2012 : Mysql fulltext search my.cnf optimization
Aug 01, 2016 : How to make a FULLTEXT search with ORDER BY fast?


In your particular case, I would suggest rewriting your query as an all-out JOIN:

SELECT t.* FROM tracking t
INNER JOIN tracking_items ti ON t.id = ti.tracking_id
INNER JOIN (SELECT car_id,car_text FROM cars WHERE MATCH(car_text)
AGAINST ('+bulgaria +turkey' in boolean mode)) c
ON ti.tracking_object_id = c.car_id LIMIT 5;


or maybe

SELECT t.* FROM
(SELECT car_id,car_text FROM cars WHERE MATCH(car_text)
AGAINST ('+bulgaria +turkey' in boolean mode)) c
INNER JOIN tracking_items ti ON c.car_id = ti.tracking_object_id
INNER JOIN tracking t ON ti.tracking_id = t.id  LIMIT 5;


If these queries give you an idea, please incorporate it if you still want to use WHERE EXISTS.

Please run the explain plan on these queries and your and see the differences

EXPLAIN select `t`.*
from `tracking` `t` 
where exists 
(   
    select `ti`.`id` from `tracking_items` `ti`
    inner join `cars` `c` on (`c`.`car_id` = `ti`.`tracking_object_id`)
    where `t`.`id` = `ti`.`tracking_id` and match(`c`.`car_text`)
    against('+bulgaria +turkey' in boolean mode) 
    limit 1
) and ... limit 5;

EXPLAIN SELECT t.* FROM
(SELECT car_id,car_text FROM cars WHERE MATCH(car_text)
AGAINST ('+bulgaria +turkey' in boolean mode)) c
INNER JOIN tracking_items ti ON c.car_id = ti.tracking_object_id
INNER JOIN tracking t ON ti.tracking_id = t.id LIMIT 5;

EXPLAIN SELECT t.* FROM tracking t
INNER JOIN tracking_items ti ON t.id = ti.tracking_id
INNER JOIN (SELECT car_id,car_text FROM cars WHERE MATCH(car_text)
AGAINST ('+bulgaria +turkey' in boolean mode)) c
ON ti.tracking_object_id = c.car_id LIMIT 5;


Give it a try, and let us know what happens !!!