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 13, 2012 11:07PM

> I am trying to optimize a query that joins 2 tables, using a column in one table to filter the data, and using a column in the other table to sort and limit the results.

Classis performance problem.

> limit 20 offset 20;

Smells like "pagination" done in the classic inefficient way. Instead, remember where you "left off". (We can discuss that if you like.)

> in ( select ...

Notoroiusly inefficient -- Unless the select greatly diminishes the number of rows (via GROUP BY, LIMIT, etc)

Are you thrashing the disk? Check the tunables:
http://mysql.rjweb.org/doc.php/memory

Options: ReplyQuote


Subject Views Written By Posted
Queries using JOIN and ORDER BY LIMIT 2313 Nikita Dubrovsky 03/10/2012 01:11PM
Re: Queries using JOIN and ORDER BY LIMIT 753 irek kordirko 03/12/2012 06:18PM
Re: Queries using JOIN and ORDER BY LIMIT 748 Rick James 03/13/2012 11:07PM
Re: Queries using JOIN and ORDER BY LIMIT 707 Nikita Dubrovsky 03/14/2012 08:45AM
Re: Queries using JOIN and ORDER BY LIMIT 869 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.