MySQL Forums :: Performance :: JOIN, LIMIT, ORDER performance problems


Advanced Search

JOIN, LIMIT, ORDER performance problems
Posted by: Julian Bogdani ()
Date: March 07, 2010 11:56AM

Hello,
I'm trying to split a SELECT query with JOIN LEFT statement in smaller ones using LIMIT (trying to build a ajax application), and trying to ordinate the result using ORDER, and I had some performance problems.
The query looks like this:

SELECT TB1.FLD1, TB1.FLD2, TB2.FLD1, TB2.FLD2 FROM TB1 LEFT JOIN TB2 ON (...) WHERE 1 ORDER BY TB1.FLD1 LIMIT 0, 100 (and then LIMIT 100, 100; LIMIT 200, 100 and so on..)

First of all I solved the problem of ordering by creating a sub-query like this:

SELECT * FROM (SELECT TB1.FLD1, TB1.FLD2, TB2.FLD1, TB2.FLD2 FROM TB1 LEFT JOIN TB2 ON (...) WHERE 1 LIMIT 0, 100) AS SUBTB ORDER BY TB1.FLD1 (...and so on...)

It worked fine at first, whit test single queries, but when I built my application I experienced big troubles (very slow queries, up to 20 sec).
I think thee is a problem with big offsets for LIMIT,
Can anyone help me?
Thank you...

Options: ReplyQuote


Subject Views Written By Posted
JOIN, LIMIT, ORDER performance problems 5081 Julian Bogdani 03/07/2010 11:56AM
Re: JOIN, LIMIT, ORDER performance problems 2560 Rick James 03/08/2010 11:59AM
Re: JOIN, LIMIT, ORDER performance problems 1405 Julian Bogdani 03/08/2010 04:30PM
Re: JOIN, LIMIT, ORDER performance problems 1551 Rick James 03/08/2010 05:45PM
Re: JOIN, LIMIT, ORDER performance problems 1328 Ventsislav Alexandriyski 03/09/2010 01:07PM
Re: JOIN, LIMIT, ORDER performance problems 1466 Rick James 03/09/2010 09:42PM
Re: JOIN, LIMIT, ORDER performance problems 1980 Julian Bogdani 03/10/2010 01:41AM
Re: JOIN, LIMIT, ORDER performance problems 1416 Julian Bogdani 03/10/2010 01:48AM
Re: JOIN, LIMIT, ORDER performance problems 1656 Julian Bogdani 03/10/2010 01:51AM
Re: JOIN, LIMIT, ORDER performance problems 1334 Ventsislav Alexandriyski 03/10/2010 06:35AM
Re: JOIN, LIMIT, ORDER performance problems 1483 Rick James 03/10/2010 09:26AM


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.