Re: No Key on LIKE% (was: Performance issues.)
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
Re: No Key on LIKE% (was: Performance issues.)
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
No Key on LIKE% (was: Performance issues.)
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
