Problems with large MySQL table using ORDER BY and LIMIT
I have a MySQL table that is pretty huge. There are 21 columns and (at this point) about 3 million rows. The interface I'm writing in C# to display data from this table will obviously need the ability to break results into pages. The way I was going to do this was using LIMIT. All results would be sorted by date. So the query for the first page would look like this:
SELECT * FROM BigTable ORDER BY ItemDate ASC LIMIT 0,250;
This takes forever though, because before it applies the limit it goes through and orders the entire table by the date field. So I'm not really sure how to speed this up and make it work efficiently. Any recommendations?
SELECT * FROM BigTable ORDER BY ItemDate ASC LIMIT 0,250;
This takes forever though, because before it applies the limit it goes through and orders the entire table by the date field. So I'm not really sure how to speed this up and make it work efficiently. Any recommendations?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi elorc,
Just so you know how this works, MySQL recognizes that there is an index on the ItemDate column and grabs the first 250 dates from the index instead of reading the table.
It then joins only those 250 rows from the table.
Kent
Just so you know how this works, MySQL recognizes that there is an index on the ItemDate column and grabs the first 250 dates from the index instead of reading the table.
It then joins only those 250 rows from the table.
Kent
ASKER