j.Stark j.Stark -4 years ago 79
MySQL Question

optimize a Select

How can I optimize my select :

SELECT Id, FirstName, LastName, Country
FROM Customer
WHERE (Country ="x" and Id ="y")
or (Country ="x1" and Id ="y1")
or (Country ="x2" and Id ="y2")
or (Country ="x3" and Id ="y3")
or (Country ="x4" and Id ="y4")
or (Country ="x5" and Id ="y5")

Answer Source

I would suggest writing it using tuples:

SELECT Id, FirstName, LastName, Country
FROM Customer c
WHERE (Country, id) in ( ('x', 'y'), ('x1', 'y1'), . . . )

(This should work in both MySQL and Oracle but not in all databases.)

Then the database should be able to readily use an index on Customer(Country, Id). It might be able to use that index with the original version, as well.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download