MySQL Forums
Forum List  »  Newbie

Re: Slow query. Why is it slow?
Posted by: ben jaz
Date: May 23, 2013 06:21PM

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.

Options: ReplyQuote


Subject
Written By
Posted
May 19, 2013 06:35AM
Re: Slow query. Why is it slow?
May 23, 2013 06:21PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.