I have below query that takes 7 secs to run
SELECT COUNT(*) AS `count`
FROM `yuldi`.`businesses` AS `Business`
LEFT JOIN `yuldi`.`businesses_categories` AS `BusinessesCategory`
ON (`Business`.`id` = `BusinessesCategory`.`business_id`)
LEFT JOIN `yuldi`.`categories` AS `Category`
ON (`BusinessesCategory`.`category_id` = `Category`.`id`)
WHERE `Category`.`slug` = 'building-construction'
when i run it second time it takes 160 ms. what wrong why it takes so long on 1st run on everytime
EXPLAIN:
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
| 1 | SIMPLE | Category | const | PRIMARY,UNIQUE_SLUG,index_lug | UNIQUE_SLUG | 302 | const | 1 | Using index |
| 1 | SIMPLE | BusinessesCategory | ref | PRIMARY,fk_businesses_has_categories_categories1_idx,fk_businesses_has_categorie_businesses_idx | fk_businesses_has_categories_categories1_idx | 4 | const | 49630 | Using where; Using index |
| 1 | SIMPLE | Business | eq_ref | PRIMARY | PRIMARY | 4 | yuldi.BusinessesCategory.business_id | 1 | Using index |
Best Answer
There are three aspects you need to rework
ASPECT #1 : THE QUERY
Please look at the Query
You are doing LEFT JOINs. That is not necessary for doing a COUNT. Why ?
If you are counting the number of businesses, you only need to count from one table
Since you have a WHERE clause on
Category.slug
, the JOINs are needed. You should switch from LEFT JOINs to INNER JOINs. That will produce a smaller internal temp table. That temp table will be searched for the slug 'building-construction'.ASPECT #2 : INDEXES
From the EXPLAIN plan, I see two indexes similarly named
You need to look at the columns for them and make sure the column lists are not identical.
You may want to make two indexes
Having compound indexes could mean less culling through tables for JOIN information.
Keep in mind, this suggestion is a blind one since I do not know the key distribution of your data.
ASPECT #3 : CACHING
Gathering index statistics is the sake of query evaluation does not immediately translate into a slow query. That slowness is manifested by the real culprit : CACHING. The reason your query runs faster the second time around has to do with the data you read the first time. The data you needed was most likely not available in MySQL's caches when you issued the query.
The two main caches for MySQL are the InnoDB Buffer Pool (for queries hitting InnoDB tables) and the MyISAM key cache (for queries hitting MyISAM tables).
InnoDB caches data and index pages, while MyISAM caches only index pages.
When retrieving data for comparison, mysqld tries to determine if the data it needs is in RAM first. There are server status variables to monitor for this:
InnoDB Caching
MyISAM Caching
If your query was slow the first time, it simply means the data you need for the query was not in a cache. This would have been indicated by either your Innodb_buffer_pool_reads or Key_reads incrementing.
The second time, your data and/or index pages for the same query would be in RAM and more available for your queries and queries from others.
I suggest you look over the sizes of your InnoDB Buffer Pool and/or MyISAM key cache.