JOIN, LIMIT, ORDER performance problems
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...