DateTime search optimisation - MySQL

[Salagir]

Hi. I have a datetime field, named 'whenitwas'.

What search would be faster ?

...WHERE whenitwas >= '2006-01-01' AND whenitwas < '2006-01-01'+INTERVAL 1 DAY

...WHERE whenitwas >= '2006-01-01 00:00:00' AND whenitwas <= '2006-01-01 23:59:59'

...WHERE DATE_FORMAT(whenitwas, ,'%Y-%m-%d') = '2006-01-01'

I would say it's the first.
And that second is the same as first.

Same question if the field is an index :)



I also saw the following code :
....WHERE UNIX_TIMESTAMP(whenitwas) + 3600 > UNIX_TIMESTAMP(NOW())

And after yelling in the office there was a crazy or ignorant guy among
us, I had the following questions :

- I thought comparing on UNIX_TIMESTAMP(whenitwas) instead of whenitwas
would take much much time, especially if it's indexed, as a modification
would be done before comparring (and so losing the advantage of the
index), but I compared the 2 ways in a request and it's didn't seem very
different.
After all, I don't know how datetimes are written in a databases, maybe
the format make it readable as easily in text form as in timestamp
form.

- when using " field + 5 < 10 ", is mysql clever and comparing on each
row `field` to 5 or does he stupidly add 5 each time? And even if it
did, would the optimisation of doing " field < 10 - 5 " (of course 10
is from a dynamic var in the code) win us more than 0.00001 seconds?


Thanks for your anwsers and forgive my english :)

--
My Webcomic: [url]www.geeksworld.org[/url] -+- All my websites: manaworld.free.fr
<morganj> 0 is false and 1 is true, correct?
<alec_eso> 1, morganj
<morganj> .

[Axel Schwenke]

Salagir <SalagirjeruCITEDELESPACE.org.invalid> wrote:

> Hi. I have a datetime field, named 'whenitwas'.
> What search would be faster ?
>
> ..WHERE whenitwas >= '2006-01-01' AND whenitwas < '2006-01-01'+INTERVAL 1 DAY
> ..WHERE whenitwas >= '2006-01-01 00:00:00' AND whenitwas <= '2006-01-01 23:59:59'

If you have an index on `whenitwas`, those queries should be blindingly
fast. Also there is no difference in speed.

> ..WHERE DATE_FORMAT(whenitwas, ,'%Y-%m-%d') = '2006-01-01'

This will not use an index on `whenitwas`. It will be even more slower
than the required full table scan suggests, because the DATE_FORMAT()
call is expensive.

> After all, I don't know how datetimes are written in a databases, maybe
> the format make it readable as easily in text form as in timestamp
> form.

Use the source, Luke! DATETIME is held in a C struct, defined in
mysql_time.h

> - when using " field + 5 < 10 ", is mysql clever and comparing on each
> row `field` to 5 or does he stupidly add 5 each time?

IMHO this optimization is out of the scope of an SQL pr.

> And even if it
> did, would the optimisation of doing " field < 10 - 5 " (of course 10
> is from a dynamic var in the code) win us more than 0.00001 seconds?

If field is indexed, this optimization could make the difference
between an index lookup and a full table scan.


XL

[Salagir]

On Fri, 10 Feb 2006 13:14:18 +0100, in comp.databases.mysql,
Axel Schwenke wrote:

> Salagir <SalagirjeruCITEDELESPACE.org.invalid> wrote:

>> Hi. I have a datetime field, named 'whenitwas'.
>> What search would be faster ?
>> ..WHERE whenitwas >= '2006-01-01' AND whenitwas < '2006-01-01'+INTERVAL 1 DAY
>> ..WHERE whenitwas >= '2006-01-01 00:00:00' AND whenitwas <= '2006-01-01 23:59:59'

> If you have an index on `whenitwas`, those queries should be blindingly
> fast. Also there is no difference in speed.

>> ..WHERE DATE_FORMAT(whenitwas, ,'%Y-%m-%d') = '2006-01-01'

> This will not use an index on `whenitwas`. It will be even more slower
> than the required full table scan suggests, because the DATE_FORMAT()
> call is expensive.

As I thought.

>> After all, I don't know how datetimes are written in a databases, maybe
>> the format make it readable as easily in text form as in timestamp
>> form.

> Use the source, Luke! DATETIME is held in a C struct, defined in
> mysql_time.h

^_^ each time I try that, I'm kind of drowned in the complexity.
Plus, all the magic goes away :p

>> - when using " field + 5 < 10 ", is mysql clever and comparing on each
>> row `field` to 5 or does he stupidly add 5 each time?

> IMHO this optimization is out of the scope of an SQL pr.

Humans still rule!

>> And even if it
>> did, would the optimisation of doing " field < 10 - 5 " (of course 10
>> is from a dynamic var in the code) win us more than 0.00001 seconds?

> If field is indexed, this optimization could make the difference
> between an index lookup and a full table scan.

Thank you very much for your answers.
Now I have some queries to change.. :)

--
My Webcomic: [url]www.geeksworld.org[/url] -+- All my websites: manaworld.free.fr