MySQL Forums
Forum List  »  Optimizer & Parser

REGEXP is not optimized?
Posted by: Maarten Oosten
Date: December 15, 2006 07:54AM

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?

Options: ReplyQuote


Subject
Views
Written By
Posted
REGEXP is not optimized?
3517
December 15, 2006 07:54AM
2466
January 05, 2007 08:57AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.