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;
select type: SIMPLE
possible keys: NULL
key len: 452
extra: Using where
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:
All of these involve some programming work.