Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
1  
How about using IN? –  Eric Higgins Mar 16 '12 at 16:05
    
Also, is there a reason why first_name would be allowed to accept what look to be both first and last names? –  swasheck Mar 16 '12 at 16:07
    
There could be spaces in first names. Like Jon claude is a first name. –  Stewie Mar 16 '12 at 16:13

3 Answers 3

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.

share|improve this answer
    
This will be bad for relevancy. I want the most relevant search first. If I search for "Bill Gates" I want "Bill Gates" to result first rather than Bill Mates –  Stewie Mar 16 '12 at 17:12
    
No disagreement, just working with what you've posted. –  Eric Higgins Mar 16 '12 at 18:21

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);
share|improve this answer
    
Hey, thought of that earlier, but our tables are innoDB. Can I create a view which is a myiasm for an innodb table ? –  Stewie Mar 16 '12 at 17:07

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.