List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:February 11 2002 5:50am
Subject:Re: REGEXP and INDEX
View as plain text  
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, <jzawodn@stripped>
>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 <mysql-thread99108@stripped>
>To unsubscribe, e-mail <mysql-unsubscribe-paul=snake.net@stripped>
>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 <mysql-thread99109@stripped>
To unsubscribe, e-mail <mysql-unsubscribe-cyon=bestweb.net@stripped>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Thread
Re: Mysql and viewsNoam Cassif6 Feb
  • REGEXP and INDEXMatthew Dougherty10 Feb
    • Re: REGEXP and INDEXJeremy Zawodny11 Feb
      • Re: REGEXP and INDEXPaul DuBois11 Feb
        • Re: REGEXP and INDEXJeremy Zawodny11 Feb
          • Re: REGEXP and INDEXPaul DuBois11 Feb
Re: Mysql and viewsNoam Cassif6 Feb
Re: Mysql and viewsVictoria Reznichenko6 Feb
Re: Mysql and viewsNoam Cassif12 Feb
Re: Mysql and viewsVictoria Reznichenko12 Feb
Re: REGEXP and INDEXPaul DuBois12 Feb
  • Re: REGEXP and INDEXMatthew Dougherty13 Feb