MySQL regexp on Indexes

tags:

mysql

views:

19

answers:

1

Q:

MySQL regexp on Indexes

HI,

I have query having multiple regexp in where clause. The coloumns contained in the where clause have already been indexed. But the query is not using the indexes. Does MySql regexp cause use of indexes ? If not, what could be the workaround for this ?

+1 A:

No, a regular expression lookup can't use an index. If the thing to which you're applying the regex is an index, it might go a bit faster, but you're essentially table scanning.

The only workaround I know is to use LIKE 'foo%' instead of RLIKE 'foo.*' if that's your regex. An index on such a column can use the LIKE but not the RLIKE

David M 2010-05-11 06:11:58

Thanks David. This is exactly what i wanted to confirm..But , have already worked with 'LIKE'.My problem is that i want to search on the both sides. Test can be at any position in the field.So, the query is LIKE '%foo%'. Is there any way to make indexes work in such a case ?Thanks Vivek

Vivek 2010-05-11 06:24:33

This is correct. If you can narrow down using a LIKE "constant%", that clause will atleast use an index to already narrow down the results.

Konerak 2010-05-11 06:40:34

@Vivek unfortunately no. As it turns out, I was working on exactly the same thing recently, trying to cleverly cache the results of `LIKE '%foo%'` because an index can't help that.

David M 2010-05-11 16:03:42

ansaurus

tags:

views:

answers:

MySQL regexp on Indexes

related questions