Optimizing SQL query

I have to get all entries in database that have a publish_date between two dates. All dates are stored as integers because dates are in UNIX TIMESTAMP format... Following query works perfect but it takes "too long". It returns all entries made between 10 and 20 dazs ago.

SELECT * FROM tbl_post WHERE published <  (UNIX_TIMESTAMP(NOW())-864000) 
AND published> (UNIX_TIMESTAMP(NOW())-1728000)

Is there any way to optimize this query? If I am not mistaken it is calling the NOW() and UNIX_TIMESTAMP on evey entry. I thought that saving the result of these 2 repeating functions into mysql @var make the comparison much faster but it didn't. 2nd code I run was:

SET @TenDaysAgo = UNIX_TIMESTAMP(NOW())-864000;
SET @TwentyDaysAgo = UNIX_TIMESTAMP(NOW())-1728000;
SELECT * FROM tbl_post WHERE fecha_publicado <  @TenDaysAgo 
AND fecha_publicado > @TwentyDays开发者_JAVA百科Ago;

Another confusing thing was that PHP can't run the bove query throught mysql_query(); ?!

Please, if you have any comments on this problem it will be more than welcome :)

Luka

Be sure to have an index on published.And make sure it is being used.

EXPLAIN SELECT * FROM tbl_post WHERE published <  (UNIX_TIMESTAMP(NOW())-864000)  AND published> (UNIX_TIMESTAMP(NOW())-1728000)

should be a good start to see what's going on on the query. To add an index:

ALTER TABLE tbl_post ADD INDEX (published)

PHP's mysql_query function (assuming that's what you're using) can only accept one query per string, so it can't execute the three queries that you have in your second query.

I'd suggest moving that stuff into a stored procedure and calling that from PHP instead.

As for the optimization, setting those variables is about as optimized as you're going to get for your query. You need to make the comparison for every row, and setting a variable provides the quickest access time to the lower and upper bounds.

One improvement in the indexing of the table, rather than the query itself would be to cluster the index around fecha_publicado to allow MySQL to intelligently handle the query for that range of values. You could do this easily by setting fecha_publicado as PRIMARY KEY of the table.

The obvious things to check are, is there an index on the published date, and is it being used?

The way to optimize would be to partition the table tbl_post on the published key according to date ranges (weekly seems appropriate to your query). This is a feature that is available for MySQL, PostgreSQL, Oracle, Greenplum, and so on.

This will allow the query optimizer to restrict the query to a much narrower dataset.

I agree with BraedenP that a stored procedure would be appropriate here. If you can't use one or really don't want to, you can always either generate the dates on the PHP side, but they might not match exactly with the database unless you have them synced.

You can also do it more quickly as 3 separate queries likely. Query for the begin data, query for the end date, then use those values as input into your target query.