On 7/4/07, Dan Nelson <[EMAIL PROTECTED]> wrote:
In the last episode (Jul 04), David T. Ashley said: > On 7/4/07, gary <[EMAIL PROTECTED]> wrote: > > SELECT column FROM table WHERE column LIKE "CAT\_%"; > > Would it be reasonable to assume that if "column" is indexed, the > query would execute quickly, i.e. I would assume that the indexing > would facilitate this kind of query? Yes, but only for prefix checks like in this example. ` LIKE "%CAT%" ' or ` LIKE "%CAT" ' can't use an index.
Thanks. I was able to confirm the behavior by creating a table with three identical varchars, populating them randomly with a string of 6 digits but setting each varchar within a row the same, and executing queries. s3 is indexed (below). "LIKE CAT%" was obscenely fast on an indexed column. "LIKE %CAT%" was obscenely slow. Thanks for the help. --------- mysql> explain stest; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | a | bigint(20) | | PRI | 0 | | | s1 | varchar(200) | YES | | NULL | | | s2 | varchar(200) | YES | | NULL | | | s3 | varchar(200) | YES | MUL | NULL | | +-------+--------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> select count(*) from stest; +----------+ | count(*) | +----------+ | 1480131 | +----------+ 1 row in set (0.00 sec) mysql> select * from stest where s1="123455"; +--------+--------+--------+--------+ | a | s1 | s2 | s3 | +--------+--------+--------+--------+ | 246823 | 123455 | 123455 | 123455 | +--------+--------+--------+--------+ 1 row in set (2.16 sec) mysql> select * from stest where s3="123455"; +--------+--------+--------+--------+ | a | s1 | s2 | s3 | +--------+--------+--------+--------+ | 246823 | 123455 | 123455 | 123455 | +--------+--------+--------+--------+ 1 row in set (0.00 sec) mysql> select count(*) from stest where s1 like "0000%"; +----------+ | count(*) | +----------+ | 136 | +----------+ 1 row in set (2.10 sec) mysql> select count(*) from stest where s3 like "0000%"; +----------+ | count(*) | +----------+ | 136 | +----------+ 1 row in set (0.00 sec) mysql> select count(*) from stest where s1 like "%000%"; +----------+ | count(*) | +----------+ | 5585 | +----------+ 1 row in set (2.19 sec) mysql> select count(*) from stest where s3 like "%000%"; +----------+ | count(*) | +----------+ | 5585 | +----------+ 1 row in set (2.78 sec)
