Optimize mysql query

Question

Hi All,

I want to optimize this query as it is taking long to execute almost a second

Here's the query:

IF Exists(
 Select CustFirstName From Customers
  Where (CustFirstName = InputCustFirstName)
     OR (CustLastName= InputCustLastName)
     OR (Email = InputEmail)
);

All these three columns have Unique index on it. and I have 765704 records in it.

This is the explain result set of my query :

----+-------------+-------+------+----------------------+------+---------+------+--------+-----------------------------------+
| id | select_type | table | type | possible_keys        | key  | key_len | ref  | rows   | Extra                             |
+----+-------------+-------+------+----------------------+------+---------+------+--------+-----------------------------------+
|  1 | SIMPLE      | Customers | ALL  | CustName | NULL | NULL    | NULL | 765704 | Using where with pushed condition |
+----+-------------+-------+------+----------------------+------+---------+------+--------+-----------------------------------+

Can anyone assist me on how to optimize it.

Answer 1

One second to query a 3/4 million record index three times and return the union of all three queries? Sounds reasonable unless you have a really fast server with 15K RPM SAS or SCSI disks.

You might try recoding it as a union of three separate queries, one for each column criterion. That might allow it to use an index for each column.

Answer 2

You don't have enough indexes, it would seem. There's only one possible_keys, where you probably need three. Having a unique index on all three of them as a single tuple isn't enough.

Suppose you have all three columns indexed. Where (CustFirstName = InputCustFirstName) OR (CustLastName= InputCustLastName) OR (Email = InputEmail)) will usually frustrate the query optimizer.

Change the predicate using OR to one using UNION:

To paraphrase your query somewhat, you would change

SELECT * FROM Customers
WHERE CustFirstName = InputCustFirstName
OR CustLastName = InputCustLastName
OR Email = InputEmail

to

SELECT * FROM Customers
WHERE CustFirstName = InputCustFirstName
UNION
SELECT * FROM Customers
WHERE CustLastName = InputCustLastName
UNION
SELECT * FROM Customers
WHERE Email = InputEmail