mysql like query on several fields

I am trying to do a search query where the search string could be in several fields. This table has > 20K records and the query is taking 6-7 seconds. I am trying to figure a way to speed this up.

开发者_如何学PythonSELECT SQL_CALC_FOUND_ROWS cl.fname, 
                           cl.email, 
                           e.e_id 
FROM   clients cl, 
       records e 
WHERE  (( cl.fname LIKE 'smith%' 
           OR cl.lname LIKE 'smith%' 
           OR cl.email LIKE 'smith%' )) 
       AND cl.id = e.client_id 

I was wondering about making one field that had all three text strings and do a fulltext search on one field instead of three.

Any help is appreciated.

Thom

You could do something like:

SELECT * 
FROM   ((SELECT l.fname, 
                cl.email, 
                e.e_id 
         FROM   clients cl 
                JOIN records e 
                  ON cl.id = e.client_id 
         WHERE  cl.fname LIKE 'smith%') 
        UNION ALL 
        (SELECT l.fname, 
                cl.email, 
                e.e_id 
         FROM   clients cl 
                JOIN records e 
                  ON cl.id = e.client_id 
         WHERE  cl.lname LIKE 'smith%') 
        UNION ALL 
        (SELECT l.fname, 
                cl.email, 
                e.e_id 
         FROM   clients cl 
                JOIN records e 
                  ON cl.id = e.client_id 
         WHERE  cl.email LIKE 'smith%')) d 
GROUP  BY id; 

and you need indexes on the following

• (records.client_id)
• (clients.id)
• (clients.fname)
• (clients.lname)
• (clients.email)