Problems with large MySQL table using ORDER BY and LIMIT
Posted on 2010-09-23
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?