REGEXP is not optimized?

[Maarten Oosten]

Hi,

I'm using REGEXP to search records by the prefix of an indexed text field, but it has much worse performance than LIKE, even though I'm searching for the same thing.

Example:

Compare:

EXPLAIN SELECT * FROM table WHERE name LIKE "a%";
+----+-------------+-------+-------+---------------+---------+---------+------+-------+-------------+
|-id-|-select_type-|-table-|-type--|-possible_keys-|-key-----|-key_len-|-ref--|-rows--|-Extra-------|
+----+-------------+-------+-------+---------------+---------+---------+------+-------+-------------+
|--1-|-SIMPLE------|-table-|-range-|-keyname-------|-keyname-|-61------|-NULL-|-13902-|-Using-where-|
+----+-------------+-------+-------+---------------+---------+---------+------+-------+-------------+

with:

EXPLAIN SELECT * FROM table WHERE name REGEXP "^a";
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
|-id-|-select_type-|-table-|-type-|-possible_keys-|-key--|-key_len-|-ref--|-rows---|-Extra-------|
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
|--1-|-SIMPLE------|-table-|-ALL--|-NULL----------|-NULL-|-NULL----|-NULL-|-133470-|-Using-where-|
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+

Now MySQL want to scan the whole table and doesn't use the index! This is horrible for complex queries.

I need REGEXP because I want to chop my query in to 27 pieces, to make it more manageable. I want to search for 'name LIKE "a%"' to 'name LIKE "z%"' and finally for 'name REGEXP "^[^a-z]"'.

I tried some tricks with LEFT, but it has the same problem as REGEXP. How can I do
this? The problem is much worse than 133470 rows, because I need to search for combinations of combination of names.

I hope someone can give me some advice.
Thanks for your attantion,

Maarten


PS. I see my post will look ugly. Better copy-paste it to notepad for easy reading. Are there any tags supported to make it look better?