How would you optimize this MySQL query

Question

Can someone help me in optimizing this query,

SELECT(X1, X2 FROM TABLEAA WHERE Y IN (SELECT Y FROM TABLEBB WHERE Z=SELECTED) AND Y IN (SELECT Y FROM TABLECC WHERE ZZ=SELECTED) ) 

WHERE AS

 TABLEAA : 1 million enteries TABLEBB : 22 million enteries TABLECC : 1.2 million enteries 

it works, but takes too long, almost 30 seconds

Is there any other way to do this?

edit: Z and ZZ are completely two different columns

Answer 1

I would use JOINs :

 SELECT DISTINCT A.X1, A.X2 FROM TABLEAA A JOIN TABLEBB B ON AY = BY AND BZ='SELECTED' JOIN TABLECC C ON AY = CY AND CZ='SELECTED' 

Also, make sure you have the appropriate indexes on AY, BY, and CY. You can find better performance by adding indexes to the Z columns - this depends on the structure of your table and several other factors.

Answer 2

Instead of using subqueries, attach TABLEBB and TABLECC to TABLEAA and check that ZZ = SLECTED in your WHERE clause for both joined tables.

Verify that columns participating in outer joins are indexed.

Answer 3

Indexes ...

• Add Z Index to TABLEBB
• Add ZZ Index to TABLECC
• Add index Y to TABLEAA

Answer 4

 SELECT X1, X2 FROM TABLEAA JOIN TABLEBB ON Y = Y JOIN TABLECC ON Y = Y WHERE TABLEBB.Z = SLECTED && TABLECC.ZZ = SLECTED