interesting ‘bus’ ad

Most of the time, I take a bus to go to work. One of the sidelines when you’re riding buses are the ‘personal’ ads scribbled by passengers (stuck in traffic, maybe).

June and I saw  this ad for instance, ‘need boy or girl text mate with 3rd eye‘…

Now, that’s interesting! How would the poster verify if those persons that did reply have a third eye? Maybe this ad was posted by a (not so) secret psychic group…

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)