EXPLAIN EXTENDED

CREATE TABLE t_text (
       id INT NOT NULL AUTO_INCREMENT,
     value VARCHAR(200) NOT NULL,
        PRIMARY KEY(id),
    KEY ix_text_value (value)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TEMPORARY TABLE t_filler (
 id INT NOT NULL PRIMARY KEY
);

CREATE TEMPORARY TABLE t_letters (
 id INT NOT NULL PRIMARY KEY
);

DELIMITER $$

CREATE PROCEDURE prc_fill_filler (cnt INT)
BEGIN
    DECLARE _cnt INT;
   SET _cnt := 1;
      WHILE _cnt <= cnt DO
                INSERT
              INTO    t_filler
            VALUES  (_cnt);
             SET _cnt := _cnt + 1;
       END WHILE;
  SET _cnt := 1;
      WHILE _cnt <= 20 DO
         INSERT
              INTO    t_letters
           VALUES  (_cnt);
             SET _cnt := _cnt + 1;
       END WHILE;
END
$$

DELIMITER ;

START TRANSACTION;
CALL prc_fill_filler (100000);
COMMIT;

SET @l_vowels = 'aeiouy';
SET @u_vowels = UPPER(@l_vowels);
SET @l_consonants = 'bcdfghjklmnpqrstvwxz';
SET @u_consonants = UPPER(@l_consonants);

INSERT
INTO   t_text (id, value)
SELECT    id,
 CONCAT(
             CASE
                        WHEN rnd < 0.05 THEN 'A '
                 WHEN rnd < 0.1 THEN 'The '
                        ELSE ''
           END,
                (
           SELECT  GROUP_CONCAT(
                       IF(
                 (slen - id) % 2 = 0,
                        SUBSTR(IF(l.id = 1, @u_vowels, @l_vowels), FLOOR(RAND(20090323) * LENGTH(@l_vowels) + 1), 1), 
                      SUBSTR(IF(l.id = 1, @u_consonants, @l_consonants), FLOOR(RAND() * LENGTH(@l_consonants) + 1), 1)
                    )
                   ORDER BY l.id
                       SEPARATOR '')
             FROM    t_letters l
         WHERE   l.id <= slen
                )
   )
FROM       (
   SELECT  id,
         RAND(20090323) AS rnd,
              FLOOR(RAND() * 10) + 4 AS slen
      FROM    t_filler
    ) q;

DROP TABLE t_filler;
DROP TABLE t_letters;
&#91;/sourcecode&#93;
</div>

At first blush, this seems be a job for <code>REGEXP</code>'s.


SELECT  *
FROM    t_text
WHERE   value REGEXP '^(The |A )?Can'

I put three letters into the filter, but the principle remains the same.

This query is nice and elegant, but it works way too long, because it doesn't use the index on value.

For the index to be used, we need MySQL to understand that there are three range conditions: 'Can ', 'A Can' and 'The Can'.

Fortunately, MySQL is smart enough to use the range access method if we use a LIKE condition without a wildcard in the beginning:

SELECT  *
FROM    t_text
WHERE   value LIKE 'Can%'
        OR value LIKE 'The Can%'
        OR value LIKE 'A Can%'

As was expected, range access is much faster.