MySQL Forums
Forum List  »  Optimizer & Parser

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:

Options: ReplyQuote

Written By
Re: Queries using JOIN and ORDER BY LIMIT
March 13, 2012 11:07PM

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.