regex - Mysql optimization for REGEXP

Question

This query (with different name instead of "jack") happens many times in my slow query log. Why?

The Users table has many fields (more than these three I've selected) and about 40.000 rows.

select name,username,id from Users where ( name REGEXP '[[:<:]]jack[[:>:]]' ) or ( username REGEXP '[[:<:]]jack[[:>:]]' ) order by name limit 0,5;

id is primary and autoincrement.
name has an index.
username has a unique index.

Sometimes it takes 3 seconds! If I explain the select on MySQL I've got this:

select type: SIMPLE
table: Users
type: index
possible keys: NULL
key: name
key len: 452
ref: NULL
rows: 5
extra: Using where

Is this the best I can do? What can I fix?

Answer 1

If you must use regexp-style WHERE clauses, you definitely will be plagued by slow-query problems. For regexp-style search to work, MySQL has to compare every value in your name column with the regexp. And, your query has doubled the trouble by also looking at your username column.

This means MySQL can't take advantage of any indexes, which is how all DBMSs speed up queries of large tables.

There are a few things you can try. All of them involve saying goodbye to REGEXP.

One is this:

WHERE name LIKE CONCAT('jack', '%') OR username LIKE CONCAT('jack', '%')

If you create indexes on your name and username columns this should be decently fast. It will look for all names/usernames beginning with 'jack'. NOTICE that

WHERE name LIKE CONCAT('%','jack') /* SLOW!!! */

will look for names ending with 'jack' but will be slow like your regexp-style search.

Another thing you can do is figure out why your application needs to be able to search for part of a name or username. You can either eliminate this feature from your application, or figure out some better way to handle it.

Possible better ways:

1. Ask your users to break up their names into given-name and surname fields, and search separately.
2. Create a separate "search all users" feature that only gets used when a user needs it, thereby reducing the frequency of your slow regexp-style query.
3. Break up their names into a separate name-words table yourself using some sort of preprocessing progam. Search the name-words table without regexp.
4. Figure out how to use MySQL full text search for this feature.

All of these involve some programming work.