Professional Web Applications Themes

DateTime search optimisation - MySQL

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 ...

  1. #1

    Default DateTime search optimisation

    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> .
    Salagir Guest

  2. #2

    Default Re: DateTime search optimisation

    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
    Axel Schwenke Guest

  3. #3

    Default Re: DateTime search optimisation

    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
    Salagir Guest

Similar Threads

  1. generic array query optimisation help?
    By Nicklas in forum MySQL
    Replies: 2
    Last Post: June 28th, 07:07 PM
  2. Replies: 7
    Last Post: March 9th, 02:45 PM
  3. advice on dual screen optimisation
    By stunt-monkey in forum Macromedia Director 3D
    Replies: 1
    Last Post: June 1st, 05:56 PM
  4. Algorithm for optimisation - using Graph.pm?
    By Clyde Ingram in forum PERL Miscellaneous
    Replies: 0
    Last Post: July 24th, 09:45 PM
  5. functions - optimisation
    By hairybobby webforumsuser@macromedia.com in forum Macromedia Director Lingo
    Replies: 3
    Last Post: July 21st, 09:28 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139