List:General Discussion« Previous MessageNext Message »
From:David T. Ashley Date:July 4 2007 9:54pm
Subject:Re: How to Query by First Part of a VARCHAR?
View as plain text  
On 7/4/07, Dan Nelson <dnelson@stripped> wrote:
>
> In the last episode (Jul 04), David T. Ashley said:
> >  On 7/4/07, gary <byoteki@stripped> 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)

Thread
How to Query by First Part of a VARCHAR?David T. Ashley4 Jul
  • Re: How to Query by First Part of a VARCHAR?gary4 Jul
    • Re: How to Query by First Part of a VARCHAR?David T. Ashley4 Jul
      • Re: How to Query by First Part of a VARCHAR?Dan Nelson4 Jul
        • Re: How to Query by First Part of a VARCHAR?David T. Ashley4 Jul
          • Re: How to Query by First Part of a VARCHAR?gary4 Jul