Optimizing SQL query

Question

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 > @TwentyDaysAgo;

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

Note that NOW() is a constant expression that represents when the statement began to execute. So there's no reason why MySQL cannot convert UNIX_TIMESTAMP(NOW()) - 864000 into a constant expression. ( I don't know if it does... just that it easily could.) — Matthew
– Matthew, Commented Apr 25, 2011 at 17:12

johannes · Accepted Answer · 2011-04-25 17:06:57Z

3

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)

answered Apr 25, 2011 at 17:06

johannes

16k3 gold badges46 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

luigi7up Over a year ago

I set my fecha_publicado (or published) to be index key also but performance hasn't really improved ? I checked with EXPLAIN and it says that this attribute is an index key. strange...

johannes Over a year ago

can you post the EXPLAIN output?

BraedenP · Accepted Answer · 2011-04-25 17:10:40Z

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.

Dave Costa · Accepted Answer · 2011-04-25 17:05:39Z

1

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

answered Apr 25, 2011 at 17:05

Dave Costa

48.2k8 gold badges61 silver badges73 bronze badges

Comments

Ronen Botzer · Accepted Answer · 2011-04-25 17:05:18Z

0

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.

answered Apr 25, 2011 at 17:05

Ronen Botzer

7,13727 silver badges41 bronze badges

2 Comments

johannes Over a year ago

Partitioning is a feature only needed when there is loooooots of data. and MySQL has partitioning, too.

Ronen Botzer Over a year ago

If such a query takes too long, and he already has an index on the published field, then most likely there is enough data in that table. And I did provide a link to the MySQL partitioning documentation :)

Tony Lukasavage · Accepted Answer · 2011-04-25 17:08:12Z

0

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.

answered Apr 25, 2011 at 17:08

Tony Lukasavage

1,9351 gold badge14 silver badges26 bronze badges

Collectives™ on Stack Overflow

Optimizing SQL query

5 Answers 5

2 Comments

Comments

Comments

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

Comments

2 Comments

Comments

Related