12
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?