The following is the piece describing LIKE and INDEX from the manual. The real question is does REGEXP use indexes.
Using explain shows that it actually does, probably with the same rules as LIKE!!! Thanks for your help. MySQL also uses indexes for LIKE comparisons if the argument to LIKE is a constant string that doesn't start with a wild-card character. For example, the following SELECT statements use indexes: mysql> select * from tbl_name where key_col LIKE "Patrick%"; mysql> 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 will not use indexes: mysql> select * from tbl_name where key_col LIKE "%Patrick%"; mysql> select * from tbl_name where key_col LIKE other_col; In the first statement, the LIKE value begins with a wild-card character. In the second statement, the LIKE value is not a constant. mysql> explain select CompanyName from Companies where CompanyName REGEXP '^AT|^BE'; +-----------+-------+---------------+----------------+---------+------+------+-------------------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-----------+-------+---------------+----------------+---------+------+------+-------------------------+ | Companies | index | NULL | companyname_dx | 81 | NULL | 3854 | where used; Using index | +-----------+-------+---------------+----------------+---------+------+------+-------------------------+ 1 row in set (0.03 sec) Matthew Dougherty Consultant: Network management architecture and implementation. [EMAIL PROTECTED] Home Office/Cellular: 973-325-8556 http://resumes.dice.com/mdougher Available for contract positions. Paul DuBois wrote: > At 21:30 -0800 2/10/02, Jeremy Zawodny wrote: > >On Sun, Feb 10, 2002 at 10:01:45PM -0600, Paul DuBois wrote: > >> At 18:07 -0800 2/10/02, Jeremy Zawodny wrote: > >> >On Sun, Feb 10, 2002 at 02:53:53PM -0500, Matthew Dougherty wrote: > >> >> If I use a REGEXP query on an indexed column using something like: > >> >> Field REGEXP '^A|^B|^C' > >> >> > >> >> Will MySQL use the index like it would if I used Field LIKE 'A%' or > >> >> Field LIKE 'B%' or FIeld LIKE 'C%' ?? > >> > > >> >It will not. > >> > > >> >Regular expressions are best used when you need something more complex > >> >than what "%" allows you to achieve. > >> > >> Not to mention that an index won't be used even for the LIKE expression, > >> because it uses OR. At least, I think it won't be. > > > >Hm. It looks like it will in 4.0.2, at least: > > Cool! > > > > >mysql> describe S2H; > >+------------+-------------+------+-----+---------+-------+ > >| Field | Type | Null | Key | Default | Extra | > >+------------+-------------+------+-----+---------+-------+ > >| Symbol | varchar(75) | | PRI | | | > >| HeadlineId | int(11) | | PRI | 0 | | > >+------------+-------------+------+-----+---------+-------+ > >2 rows in set (0.06 sec) > > > >mysql> explain select * from S2H where Symbol like "y%" or Symbol like > >mysql> "c%"; > >+-------+-------+---------------+--------+---------+------+--------+------------+ > >| table | type | possible_keys | key | key_len | ref | rows | > >mysql> Extra | > >+-------+-------+---------------+--------+---------+------+--------+------------+ > >| S2H | range | Symbol | Symbol | 75 | NULL | 129617 | > >where used | > >+-------+-------+---------------+--------+---------+------+--------+------------+ > >1 row in set (0.06 sec) > > > >Jeremy > >-- > >Jeremy D. Zawodny, <[EMAIL PROTECTED]> > >Technical Yahoo - Yahoo Finance > >Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 > > > >MySQL 3.23.47-max: up 3 days, processed 128,435,474 queries (409/sec. avg) > > > >--------------------------------------------------------------------- > >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 > > --------------------------------------------------------------------- > 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 --------------------------------------------------------------------- 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
