Counting records based on date

I have a table which contains a datetime field. It is filled with the date at which the record was inserted.

I want to count the number of records that have been inserted after a specific date (say, the last 7 days):


SELECT COUNT(*) AS counted FROM page_hit WHERE logtime > DATE_SUB(CURDATE(), INTERVAL 7 DAY);

This works fine, but I’m just wondering if there’s anything I need to take into account as far as optimising this query goes? Indexes?

This query runs about 200 times slower than simply counting all the records in the table, and EXPLAIN shows it uses the ‘where’.

Thanks!

Try an index on logtime? It’s the only field used in the WHERE clause of this query.

I added an index and it speeds the query up quite a bit. But is this efficient? As almost every record will need an entry in the index (as the datetime value is different on almost all the records).

yes, the index is efficient

Thanks guys!

That was the confirmation I needed :wink: