EXPLAIN EXTENDED

How to create fast database queries

Article-aware title filtering

Comments enabled. I *really* need your comment

From Stack Overflow:

I need to make an alphabetical listing of movie titles, so I need to show only items that begin with a chosen letter. To make this slightly more complicated, many titles start with "the" or "a", which needs to be ignored.

How would the mysql query look to achieve such a task?

Let's create sample tables and see:

Table creation details

At first blush, this seems be a job for REGEXP's.

SELECT  *
FROM    t_text
WHERE   value REGEXP '^(The |A )?Can'
id value
8434 A Cananubyne
35646 A Canijibaci
82365 Canahoze
93410 Canaje
54309 Canala
80958 Cane
85230 Canekoho
33636 Cani
93363 Cani
93440 Cani
20711 Canihu
33477 Canikelizimy
15834 Canoko
19532 Canu
97934 Canuhuwymuva
89303 Canuhy
13475 Canuluny
9754 Canyle
33991 Canynedowe
21664 The Canylynyjeca
20 rows fetched in 0.0005s (0.3810s)

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%'
id value
8434 A Cananubyne
35646 A Canijibaci
82365 Canahoze
93410 Canaje
54309 Canala
80958 Cane
85230 Canekoho
33636 Cani
93363 Cani
93440 Cani
20711 Canihu
33477 Canikelizimy
15834 Canoko
19532 Canu
97934 Canuhuwymuva
89303 Canuhy
13475 Canuluny
9754 Canyle
33991 Canynedowe
21664 The Canylynyjeca
20 rows fetched in 0.0004s (0.0016s)

As was expected, range access is much faster.

Written by Quassnoi

March 23rd, 2009 at 11:00 pm

Posted in MySQL

Leave a Reply