Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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              |
share|improve this question

2 Answers 2

up vote 3 down vote accepted

There are three aspects you need to rework

ASPECT #1 : THE QUERY

Please look at the Query

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';

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

SELECT COUNT(*) AS `count`
FROM `yuldi`.`businesses` AS `Business`;

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

  • fk_businesses_has_categories_categories1_idx
  • fk_businesses_has_categories_businesses_idx

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

ALTER TABLE BusinessesCategory
    ADD INDEX bus_cat_ndx (business_id,category_id),
    ADD INDEX cat_bus_ndx (category_id,business_id)
;

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

  • Key_read_requests : The number of requests to read a key block from the MyISAM key cache.
  • Key_reads : The number of physical reads of a key block from disk into the MyISAM key cache. If Key_reads is large, then your key_buffer_size value is probably too small. The cache miss rate can be calculated as Key_reads/Key_read_requests.

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.

share|improve this answer
    
how to prevent caching while testing queries execution times ? –  Surjit Sidhu Apr 29 at 14:11

Nothing is bad on the first time. MySQL just uses the collected statistics to further optimize the query and it makes it much faster from the second run.

With the query

EXPLAIN SELECT ...

you can see the execution strategy of a given query. With repeated EXPLAINs between the executions you can see that significant changes there.

share|improve this answer
    
It is not possible to make it more faster ? (businesses tb rows 1 millions and categories table 500 only) system 16 GB i7 proccessor –  Surjit Sidhu Apr 29 at 10:12
    
@SurjitSidhu Is it a question or an info? –  Peter Horvath Apr 29 at 10:13
    
its question, i have edited with question explain select data , if that helps –  Surjit Sidhu Apr 29 at 10:16
    
@SurjitSidhu Yes, but it is already a different question. You should create some index, but it is not clear, how. I suggest to make a new question with the title "How could I create indices to make this query as fast as possible?", and give a detailed table structure there. –  Peter Horvath Apr 29 at 10:44

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.