Optimize mysql query

Question

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.

David M · Accepted Answer · 2009-11-11 04:33:45Z

3

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

answered Nov 11, 2009 at 4:33

David M

4,3962 gold badges32 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jim Garrison · Accepted Answer · 2009-11-11 04:29:56Z

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.

answered Nov 11, 2009 at 4:29

Jim Garrison

87k20 gold badges162 silver badges197 bronze badges

Collectives™ on Stack Overflow

Optimize mysql query

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related