Re: Slow query. Why is it slow?
Rick James Wrote:
-------------------------------------------------------
> Once you have added the INDEX I recommended, you
> can further speed it up by reformulating the
> SELECT thus:
>
> SELECT t3.`id`, t3.`code`, t3.`class`,
> t3.`category`,
> t3.`status`, t3.`price`,
> t3.`production_date`,
> s.`title` AS STATUS
> FROM
> ( SELECT t1.id
> FROM products_table AS t1
> WHERE t1.`id` > 0
> AND t1.`class` = '6'
> AND t1.`category` = 'E'
> ORDER BY t1.`id`
> LIMIT 50
> ) AS t2
> JOIN products_table AS t3 ON t3.id = t1.id
> JOIN `products_status` AS s ON s.`id` =
> t3.`status`
>
>
> The 'trick' here is to
> (1) Use the new index (index only, no data) to
> find the 50 ids needed from t1;
> (2) Use a "self JOIN" to get the other fields
> needed for those 50;
> (3) Get the titles
>
> Note: each of these steps will hit only 50 rows,
> never 7 million, as your original query had to do.
> Phillip explained why that was happening.
>
> This might work just as well:
>
> SELECT t3.`id`, t3.`code`, t3.`class`,
> t3.`category`,
> t3.`status`, t3.`price`,
> t3.`production_date`,
> s.`title` AS STATUS
> FROM
> ( SELECT *
> FROM products_table AS t1
> WHERE t1.`id` > 0
> AND t1.`class` = '6'
> AND t1.`category` = 'E'
> ORDER BY t1.`id`
> LIMIT 50
> ) AS t3
> JOIN `products_status` AS s ON s.`id` =
> t3.`status`
>
> (Maybe even faster)
Hi.
Thank you for your reply. This solutin (joining a table to itself) has speeded up things a lot! It seems to be a good solution.
I did some experimentation and uploaded the results in a readable format here:
http://webmoosh.com/sql.html
Saying that I still have a question:
When you look at query 3 in the link above, how do you choose the right index or how do you approach to optimise this question?
My guess would be for query 3:
add an index on (id, status, class, category)
OR
add an index on (status, class, category, id)
Or query 5. I cant think of any other index for that situation! What would you recommend?
I appreciate if you help with that.
Many thanks
Edited 2 time(s). Last edit at 05/23/2013 06:38PM by ben jaz.