0

Possible Duplicate:
MySQL query with limit and large offset taking forever

My table contains 25,000 rows and im using pagination to display all rows on page. If my page limit is set to 10 rows per page then it displays very fast each page. However if i change the page limit size to 250 rows per page it takes 30 seconds to 1 minute to load the table (display records). The query that im using is this:

SELECT SQL_CALC_FOUND_ROWS Merchants.*, DataSources.DsISOName, PrName
FROM Merchants 
INNER JOIN DataSources ON MDsID=DsID 
INNER JOIN Processors ON PrID = DsType
ORDER BY MDBA LIMIT 0, 250

This is what i get when i load the page first. Limit numbers 0, 250 get changes if i change the page, so if i visit page 2 Limit will change to : 

  LIMIT 250, 500

If anyone can see a problem with my query please let me know. I can not understand why does it take 1 minute to load the table when 250 rows are being fetched per page. On mysql table i tried using indexes on various ID's but still same speed.

Community
  • 1
  • 1
Giorgi
  • 609
  • 2
  • 15
  • 29
  • Debug it, output the actual query and 'explain' it. – Alfabravo Oct 01 '12 at 22:21
  • See the question I just linked to. The short versions is that `limit` with an offset is slow. You're probably seeing this issue with page size of 250 because you're moving through the table 25x faster (so you see the issue much sooner). – Brendan Long Oct 01 '12 at 22:22

0 Answers0