It will use an index for 'act%' but not for '%act%' >From http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html
A B-tree index can be used for column comparisons in expressions that use the =, >, >=, <, <=, or BETWEEN operators. The index also can be used for LIKE comparisons if the argument to LIKE is a constant string that does not start with a wildcard character. For example, the following SELECT statements use indexes: SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%'; SELECT * FROM tbl_name WHERE key_col LIKE 'Pat%_ck%'; In the first statement, only rows with 'Patrick' <= key_col < 'Patricl' are considered. In the second statement, only rows with 'Pat' <= key_col < 'Pau' are considered. The following SELECT statements do not use indexes: SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%'; SELECT * FROM tbl_name WHERE key_col LIKE other_col; In the first statement, the LIKE value begins with a wildcard character. In the second statement, the LIKE value is not a constant. If you use ... LIKE '%string%' and string is longer than three characters, MySQL uses the Turbo Boyer-Moore algorithm to initialize the pattern for the string and then uses this pattern to perform the search more quickly. 2009/10/30 Stig Manning <[email protected]>: > > Hi PHPUG, > > Sorry for the OT post, I have a small problem with a search hinting > table taking a long time to perform LIKE queries that you might have > experience with. > > Would an index on the varchar field speed up the LIKE queries? My > understanding of indexes is that they only work for direct select > queries, and would only improve a field = value query. > > The queries are similar to LIKE 'act%' > > Cheers, > Stig > > > > > -- Chris Hope The Electric Toolbox Ltd Email: [email protected] Web: www.electrictoolbox.com Phone: +64 9 522 9531 Mobile: +64 21 866 529 --~--~---------~--~----~------------~-------~--~----~ NZ PHP Users Group: http://groups.google.com/group/nzphpug To post, send email to [email protected] To unsubscribe, send email to [email protected] -~----------~----~----~----~------~----~------~--~---
