Create index for period of time

[Frank Schuster]

Hello,

I have a table of a few million data records and this table has to indizies (ID, timestamp).
Now I want to get all data records for one month.
Select count(*) from logs where timestamp between '2009-06-01 00:00:00' and '2009-06-31 23:59:59';

Explain shows:
+----+-------------+--------+-------+---------------+-----------+---------+------+----------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------+-----------+---------+------+----------+--------------------------+
| 1 | SIMPLE | logs | index | timestamp | timestamp | 8 | NULL | 111454543 | Using where; Using index |
+----+-------------+--------+-------+---------------+-----------+---------+------+----------+--------------------------+

So I can see, that the Select-Command runs through all data records in database.

Have anybody an idea to solve this problem?

Frank

Note:
I have done additional a list partitioning of month (1-12).



Edited 1 time(s). Last edit at 07/28/2009 02:51AM by Frank Schuster.