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: Reply•Quote


Subject Views Written By Posted
Queries using JOIN and ORDER BY LIMIT 1262 Nikita Dubrovsky 03/10/2012 01:11PM
Re: Queries using JOIN and ORDER BY LIMIT 526 irek kordirko 03/12/2012 06:18PM
Re: Queries using JOIN and ORDER BY LIMIT 555 Rick James 03/13/2012 11:07PM
Re: Queries using JOIN and ORDER BY LIMIT 511 Nikita Dubrovsky 03/14/2012 08:45AM
Re: Queries using JOIN and ORDER BY LIMIT 654 Rick James 03/15/2012 10:50AM


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

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.