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