Optimize count query

[Scooby08]

I have a query that seems to lag a bit and although I'm not entirely sure the issue is this query, but it's a starting point..

 

SELECT count(*) FROM posts WHERE created >= DATE_SUB(NOW(), INTERVAL 1 DAY)

 

Is there any way to optimize this query any further? I have an index on the column "created" and the created date is in the following format "2012-07-13".

 

When running the query I get a count of 43551, but when I run an explain it says the following..

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE posts range created created 13 NULL 251907 Using where; Using index

 

I've also tried the following variations for the heck of it, but they all come up the same..

 

SELECT count(*) FROM posts WHERE created BETWEEN (NOW() - INTERVAL 1 DAY) AND NOW()

SELECT count(*) FROM posts WHERE created BETWEEN DATE_SUB(NOW(), INTERVAL 1 DAY) AND NOW()

 

Any ideas??

[The Little Guy]

I would recommend using the primary key column name instead of * that is usually faster.

[Scooby08]

Something like so?

 

SELECT count(id) FROM posts WHERE created BETWEEN (NOW() - INTERVAL 1 DAY) AND NOW()

[The Little Guy]

yeah something like that

[fenway]

I'm not sure why you expect the EXPLAIN to be any different.

[cpd]

I'm going to go ahead and take a random stab saying the WHERE clause is what's causing the lag, in-case you haven't already assumed - I say this because I honestly can't see why COUNT(*/id) would affect it. How much data are you dealing with?

[Scooby08]

I ended up using the following:

 

SELECT count(id) FROM posts WHERE (created BETWEEN (NOW() - INTERVAL 1 DAY) AND NOW())

 

Changing count(*) to count(id) did help to speed it up a bit, but my overall issue ended up being in the surrounding code outside of that query.. There's about 2,500,000 rows of data and now it's quick as can be and is not lagging in any way..

 

Thanks to all!