MySQL: ooops, regexp and rlike operators won’t use indices

I constructed a database that will hold millions of records last week, data mining purposes. I was in the middle of inserting the needed records when I decided to execute some queries, just for fun. I just want to check if how long (or how short) the response time of the server will be.

mysql> select * from unbilled_transactions where sig like ‘doesn’t_exist%’;
Empty set (0.16 sec)

Not bad, 0.16 seconds.

mysql> explain select * from unbilled_transactions where sig like ‘doesn’t_exist%’;
+—-+————-+———————–+——-+—————+——+———+——+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+———————–+——-+—————+——+———+——+——+————-+
| 1 | SIMPLE | unbilled_transactions | range | sig | sig | 257 | NULL | 1 | Using where |
+—-+————-+———————–+——-+—————+——+———+——+——+————-+
1 row in set (0.15 sec)

How about like,

mysql> select * from unbilled_transactions where sig like ‘%doesn’t_exist’;
Empty set (15.69 sec)

Ooops. What happened here?

mysql> explain select * from unbilled_transactions where sig like ‘%doesn’t_exist’;
+—-+————-+———————–+——+—————+——+———+——+———-+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+———————–+——+—————+——+———+——+———-+————-+
| 1 | SIMPLE | unbilled_transactions | ALL | NULL | NULL | NULL | NULL | 18934700 | Using where |
+—-+————-+———————–+——+—————+——+———+——+———-+————-+
1 row in set (0.20 sec)


It’s doing a sequential search! Oh no, some of our queries looked exactly like this…

Same behaviour with rlike,

mysql> explain select * from unbilled_transactions where sig rlike ‘doesn’t_exist’;
+—-+————-+———————–+——+—————+——+———+——+———-+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+———————–+——+—————+——+———+——+———-+————-+
| 1 | SIMPLE | unbilled_transactions | ALL | NULL | NULL | NULL | NULL | 19001584 | Using where |
+—-+————-+———————–+——+—————+——+———+——+———-+————-+
1 row in set (0.19 sec)

and regexp,

mysql> explain select * from unbilled_transactions where sig regexp ‘doesn’t_exist’;
+—-+————-+———————–+——+—————+——+———+——+———-+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+———————–+——+—————+——+———+——+———-+————-+
| 1 | SIMPLE | unbilled_transactions | ALL | NULL | NULL | NULL | NULL | 19005533 | Using where |
+—-+————-+———————–+——+—————+——+———+——+———-+————-+
1 row in set (0.18 sec)

But not if the string you’re looking for is at the beginning,

mysql> explain select * from unbilled_transactions where sig like ‘doesn’t_exist%’;
+—-+————-+———————–+——-+—————+——+———+——+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+———————–+——-+—————+——+———+——+——+————-+
| 1 | SIMPLE | unbilled_transactions | range | sig | sig | 257 | NULL | 1 | Using where |
+—-+————-+———————–+——-+—————+——+———+——+——+————-+
1 row in set (0.19 sec)

But not with regexp though,

mysql> explain select * from unbilled_transactions where sig regexp ‘^doesn’t_exist’;
+—-+————-+———————–+——+—————+——+———+——+———-+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+———————–+——+—————+——+———+——+———-+————-+
| 1 | SIMPLE | unbilled_transactions | ALL | NULL | NULL | NULL | NULL | 19044284 | Using where |
+—-+————-+———————–+——+—————+——+———+——+———-+————-+
1 row in set (0.18 sec)

mysql> explain select * from unbilled_transactions where sig regexp ‘^d.*’;
+—-+————-+———————–+——+—————+——+———+——+———-+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+———————–+——+—————+——+———+——+———-+————-+
| 1 | SIMPLE | unbilled_transactions | ALL | NULL | NULL | NULL | NULL | 19044878 | Using where |
+—-+————-+———————–+——+—————+——+———+——+———-+————-+
1 row in set (0.18 sec)

The only solution so far that I can think of is to use another indexed column…

mysql> explain select * from unbilled_transactions where sig regexp ‘doesn’t_exist.*’ and tcsd <> ”;
+—-+————-+———————–+——-+—————+——+———+——+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+———————–+——-+—————+——+———+——+——+————-+
| 1 | SIMPLE | unbilled_transactions | range | tcsd | tcsd | 8 | NULL | 3 | Using where |
+—-+————-+———————–+——-+—————+——+———+——+——+————-+
1 row in set (0.00 sec)


Using another index,

mysql> select * from unbilled_transactions where sig regexp ‘doesn’t_exist.*’ and tcsd <> ”;
Empty set (0.00 sec)

is a lot better than this,

mysql> select * from unbilled_transactions where sig regexp ‘doesn’t_exist%’;
Empty set (17.68 sec)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s