Hi VladSun
It's re-assuring to know that even far more complicated queries can be done faster!
I've had a chance to try it now. I started off with the base query as you suggested.
First I added the WHERE
Code: Select all
WHERE `contacts`.`subscribed`='VIP'
When I did EXPLAIN,
no filesort
Added the first join, and also `companies`.`company_name` to the select
Code: Select all
LEFT JOIN `companies` USING(`company_id`)
No filesort
Added the second join: LEFT JOIN `tags_data` ON `companies`.`company_id` = `tags_data`.`company_id`
So my query became:
Code: Select all
SELECT `contacts`.`contact_name` , `contacts`.`contact_id` , `companies`.`company_name`FROM `contacts`FORCE INDEX (PRIMARY )LEFT JOIN `companies`USING ( `company_id` )LEFT JOIN `tags_data` ON `companies`.`company_id` = `tags_data`.`company_id`WHERE `contacts`.`subscribed` = 'VIP'GROUP BY `contacts`.`contact_id`
And
using filesort appeared in my EXPLAIN output!
Code: Select all
+----+-------------+-----------+--------+---------------------------+------------+---------+---------------------------------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+--------+---------------------------+------------+---------+---------------------------------+-------+----------------------------------------------+
| 1 | SIMPLE | contacts | ALL | NULL | NULL | NULL | NULL | 37639 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | companies | eq_ref | PRIMARY | PRIMARY | 4 | dwcintranet.contacts.company_id | 1 | |
| 1 | SIMPLE | tags_data | index | company_id,companies_tags | company_id | 21 | NULL | 41930 | Using index |
So it's something to do with that 2nd join between tags_data.company_id
One of the indexes I have in tags_data is called companies_tags which is on 2 columns, tags_data.company_id and tags_data.tag_id
Adding this index was the one that cut my query down from 82 seconds to the 32 second mark earlier in our discussion.
I tried changing the order of this index, so it was tags_data.tag_id and tags_data.company_id but it made no difference, still using filesort.
The query was a bit quicker: 27 seconds, but I guess that's because the rest of the query is missing