List:General Discussion« Previous MessageNext Message »
From:Thimble Smith Date:August 16 1999 10:54pm
Subject:Re: optimizing varchar range search
View as plain text  
I'll be the jerk and ask you, why not use a DATETIME column instead
of a VARCHAR?  That should be quite a bit faster, I'd think.  I'm not
thinking too clearly now, so I can't come up with a good answer to
your real question.

Tim


At 17:02, 19990816, Steve Ruby wrote:
>any sugestions on further optimizing a range search on
>varchar.  I have a project where a major table has
>varchar for date and time and I get some strange
>results on range searches, there are keys on the date
>column and a key on DATE+TIME columns.
>
>How can I speed up queries thatsearch for ranges
>of these fields, including server tweaks? 
>
>as faras strange results, any idea why it takes
> less time to count the larger range?
>
>mysql> select count(*) from _data where datadate between '1999/05/01' and
>'1999/06/21';
>+----------+
>| count(*) |
>+----------+
>|   264909 |
>+----------+
>1 row in set (5.11 sec)
>
>mysql> select count(*) from _data where datadate between '1999/05/01' and
>'1999/06/21';
>+----------+
>| count(*) |
>+----------+
>|   264909 |
>+----------+
>1 row in set (5.04 sec)
>
>mysql> select count(*) from _data where datadate between '1999/05/01' and
>'1999/05/31';
>+----------+
>| count(*) |
>+----------+
>|   156632 |
>+----------+
>1 row in set (7.64 sec)
>
>mysql> select count(*) from _data where datadate between '1999/05/01' and
>'1999/05/31';
>+----------+
>| count(*) |
>+----------+
>|   156632 |
>+----------+
>1 row in set (7.91 sec)
>
>mysql> explain select count(*) from _data where datadate between
>'1999/05/01' and '1999/05/31';
>+-------+-------+---------------------+------------+---------+------+--------+-------+
>| table | type  | possible_keys       | key        | key_len | ref  | rows
>| Extra |
>+-------+-------+---------------------+------------+---------+------+--------+-------+
>| _data | range | dataDate,DataDate_2 | DataDate_2 |    NULL | NULL |
>116129 |       |
>+-------+-------+---------------------+------------+---------+------+--------+-------+
>1 row in set (0.00 sec)
>
>
>
>TIA much
>Steve
>
>
>---------------------------------------------------------------------
>Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
>posting. To request this thread, e-mail mysql-thread10379@stripped
>
>To unsubscribe, send a message to the address shown in the
>List-Unsubscribe header of this message. If you cannot see it,
>e-mail mysql-unsubscribe@stripped instead.
>
Thread
optimizing varchar range searchSteve Ruby17 Aug
  • Re: optimizing varchar range searchThimble Smith17 Aug
    • Re: optimizing varchar range searchSteve Ruby17 Aug