MySQL Forums
Forum List  »  Performance

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
5134
March 07, 2010 11:56AM


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.