Speed up search across multiple columns

Question

We have a table:

user_info (user_id,domain_id,first_name,middle_name,last_name,work_place,.,.,.)

Now, we have a user search that search across three columns first_name,last_name and work_place. If a user enters a query like: "Bill Gates" we have to search following conditions either

1. First name, last name or work place is "Bill Gates" OR
2. first name is bill, last name gates
3. first name bill, workplace gates
4. ..
5. ..
6. so on

My query is:

SELECT * FROM users WHERE active=1
AND ((first_name = 'Bill Gates' OR last_name = 'Bill Gates' OR work_place = 'Bill Gates') 
OR ((first_name = 'Bill' OR last_name = 'Bill' OR work_place = 'Bill') 
AND (first_name = 'Gates' OR last_name = 'Gates' OR work_place = 'Gates')) );

and I have a composite index on (first_name,last_name,work_place) but the query is extremely slow. Takes about 3 minutes to execute.

Any idea how to speed this one up ? I have tried unions but its still slow

Answer 1

You could try something like this to reduce the ORs. This was validated on SQL Server so you may need to tweak a bit:

SELECT * FROM users 
WHERE active=1
AND first_name  IN ('Bill Gates', 'Bill', 'Gates')
OR last_name    IN ('Bill Gates', 'Bill', 'Gates')
OR workplace    IN ('Bill Gates', 'Bill', 'Gates')

Seems like some data validation is called for overall though.

Answer 2

This looks like a job for FULLTEXT indexes

CREATE TABLE users
(
    user_id int not null,
    domain_id int not null,
    first_name varchar(20) not null
    middle_name varchar(20) not null,
    last_name varchar(20) not null,
    work_place varchar (30) not null,
    primary key (user_id),
    FULLTEXT NamePlaceIndex (first_name,last_name,work_place)
) ENGINE=MyISAM;

Still Updating...

You should be able to query against the users table as follows:

SELECT * from users WHERE active=1 AND
MATCH(first_name,last_name,work_place)
AGAINST ('Bill Gates'); 

There are different ways to evaluate the searches, such as

SELECT *,MATCH(first_name,last_name,work_place) AGAINST ('Bill Gates') search_score
FROM users WHERE active=1 AND MATCH(first_name,last_name,work_place) AGAINST ('Bill Gates'); 

Keep in mind that FULLTEXT indexes are only available for MyISAM.

It is slated to be in InnoDB in MySQL 5.6

UPDATE 2012-03-16 13:27 EDT

Here is your original query

SELECT * FROM users WHERE active=1 
AND ((first_name = 'Bill Gates' OR last_name = 'Bill Gates' OR work_place = 'Bill Gates')  
OR ((first_name = 'Bill' OR last_name = 'Bill' OR work_place = 'Bill')  
AND (first_name = 'Gates' OR last_name = 'Gates' OR work_place = 'Gates')) ); 

The composite index is not enough. You are going to need the following indexes:

ALTER TABLE users ADD INDEX (first_name,work_place);
ALTER TABLE users ADD INDEX (last_name,work_place);

After making these indexes

You could

1) go back and try your UNION queries and see...

or

2) you could try the following

SELECT B.* FROM
(
    SELECT user_id FROM users WHERE first_name='Bill' and last_name='Gates'  UNION
    SELECT user_id FROM users WHERE last_name='Bill'  and first_name='Gates' UNION
    SELECT user_id FROM users WHERE first_name='Bill' and work_place='Gates' UNION
    SELECT user_id FROM users WHERE work_place='Bill' and first_name='Gates' UNION
    SELECT user_id FROM users WHERE last_name='Bill' and work_place='Gates'  UNION
    SELECT user_id FROM users WHERE work_place='Bill' and last_name='Gates'
) A INNER JOIN users B USING (user_id);

Answer 3

Have you done an explain-plan?

Why do you have a composite index of all three, when success only depends on any one of them matching? I'd suggest re-doing that index as three separate indices (one for each of the columns) and then trying the query again.