CODEWITHSUNDEEP
mysqldatelimit
user1751126
user1751126

Reputation: 11

Limit large table according to date

I have a table with 5 million rows, and I want to get only rows that have the field date between two dates (date1 and date2). I tried to do 

select column from table where date > date1 and date < date2

but the processing time is really big. Is there a smarter way to do this? Maybe access directly a row and make the query only after that row? My point is, is there a way to discard a large part of my table that does not match to the date period? Or I have to read row by row and compare the dates?

Upvotes: 1

Views: 242

Answers (2)

RC.
RC.

Reputation: 28267

You can try/do a couple of things:

1.) If you don't already have one, index your date column

2.) Range partition your table on the date field

When you partition a table, the query optimizer can eliminate partitions that are not able to satisfy the query without actually processing any data.

For example, lets say you partitioned your table by the date field monthly and that you had 6 months of data in the table. If you query for a date between range of a week in OCT-2012, the query optimizer can throw out 5 of the 6 partitions and only scan the partition that has records in the month of OCT in 2012.

For more details, check the MySQL Partitioning page. It gives you all the necessary information and gives a more through example of what I described above in the "Partition Pruning" section.

Note, I would recommend creating/cloning your table in a new partitioned table and do the query in order to test the results and whether it satisfies your requirements. If you haven't already indexed the date column, that should be your first step, test, and if need be check out partitioning.

Upvotes: 2

tadman
tadman

Reputation: 211720

Usually you apply some kind of condition before retrieving the results. If you don't have anything to filter on you might want to use LIMIT and OFFSET:

SELECT * FROM table_name WHERE date BETWEEN ? AND ? LIMIT 1000 OFFSET 1000

Generally you will LIMIT to whatever amount of records you'd like to show on a particular page.

Upvotes: 1

Related Questions