Skip navigation links

MySQL Forums :: Optimizer & Parser :: Queries using JOIN and ORDER BY LIMIT


Advanced Search

Re: Queries using JOIN and ORDER BY LIMIT
Posted by: Rick James ()
Date: March 15, 2012 10:50AM

> So if I understand correctly, the only way to improve the query performance is to have the filtering and sorting columns in the same table and create a combined index for them?

Yes. This is because it cannot do the LIMIT until after the SORT (ORDER BY), which cannot start until it has looked at all the rows in both tables.
One possibility -- Maintain (redundantly) another table with _only_ the valid rows.

> Is there anything else that can be done to improve the query time? Are there any new features in MySQL 5.5 or later that would help with this kind of query?

Absolutely not. You have a computationally complex problem; the algorithms were optimized decades ago.

Options: ReplyQuote


Subject Views Written By Posted
Queries using JOIN and ORDER BY LIMIT 2401 Nikita Dubrovsky 03/10/2012 01:11PM
Re: Queries using JOIN and ORDER BY LIMIT 772 irek kordirko 03/12/2012 06:18PM
Re: Queries using JOIN and ORDER BY LIMIT 761 Rick James 03/13/2012 11:07PM
Re: Queries using JOIN and ORDER BY LIMIT 717 Nikita Dubrovsky 03/14/2012 08:45AM
Re: Queries using JOIN and ORDER BY LIMIT 886 Rick James 03/15/2012 10:50AM


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.