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.
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
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.
