Benjamin Pflugmann wrote:
>
> Hi.
>
> On Thu, Feb 08, 2001 at 02:48:15PM -0700, [EMAIL PROTECTED] wrote:
> [...]
> > While we are (were) on the subject.. Any thoughts why like would not be
> > using an index in this case?
> >
> > mysql> explain select * from _data where datatime like '12:00:%';
> > +-------+------+---------------+------+---------+------+---------+------------+
> > | table | type | possible_keys | key | key_len | ref | rows | Extra |
> > +-------+------+---------------+------+---------+------+---------+------------+
> > | _data | ALL | dataTime | NULL | NULL | NULL | 5751070 | where used |
> > +-------+------+---------------+------+---------+------+---------+------------+
> > 1 row in set (0.06 sec)
> >
> > mysql> explain select * from _data where datatime like '12%';
> > +-------+------+---------------+------+---------+------+---------+------------+
> > | table | type | possible_keys | key | key_len | ref | rows | Extra |
> > +-------+------+---------------+------+---------+------+---------+------------+
> > | _data | ALL | dataTime | NULL | NULL | NULL | 5751070 | where used |
> > +-------+------+---------------+------+---------+------+---------+------------+
> > 1 row in set (0.00 sec)
> >
> > There is a key on dataTime and there are only 94 unique values for datatime...
> >
> > So why in 5.7mil rows it doesn't use key? I have the same issue on a
> > datadatetime column which has 191,000 unique values and it doesn't use
> > the index either....
>
> If datatime is not a string type (I assume type TIME), a comparision
> with '12:00:%' forces a convertion to string type and therefore an
> index could not be used.
>
> You could try
>
> datatime >= '12:00:00' AND datatime <= '12:00:59'
>
> or
>
> datatime BETWEEN '12:00:00' AND '12:00:59'
>
> instead. In case my assumption (about the field type) was correct,
> they should use the index.
>
> If not, please provide more information: The output of DESCRIBE _data
> and SHOW INDEX FROM _data.
>
> Bye,
>
> Benjamin.
You are correct, it is a time column... guess that explains it,
I guess I was under the impression that time and date were simply
some sort of enforced char fields in mysql (since time and date
functions work fine on strings as well)
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
