Quentin Bennett wrote:
>
> Hi,
>
> For an indexed column, the index is used if the start of the string is used:
>
> LIKE 'a string of text%' may use an index
> LIKE '%any old string%' will not, since the start of the string is unknown.
>
> The index will only be used if the server decides that it will be quicker
> than a full table scan.
>
> Have you got the results of 'explain select ....' to see if your index is
> actually being used.
>
> Regards
>
> Quentin
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....
---------------------------------------------------------------------
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
