CODEWITHSUNDEEP
mysql
David Bailey
David Bailey

Reputation: 623

Is it optimal to use "OR" for multiple sub-queries?

I have a slow query of the following form.

SELECT DISTINCT * FROM table e
WHERE e.eid in (select eid from view1)
OR e.eid in (select eid from view1)
OR e.eid in (select eid from view2)
OR e.eid in (select eid from view3)
OR e.eid in (select eid from view4)
OR e.eid in (select eid from view5)

I am new to MySQL but is there a faster way to make this query?

Upvotes: 1

Views: 67

Answers (3)

Bohemian
Bohemian

Reputation: 425013

Using id IN (unioned subquery) generally won't perform well.

Try using a standard join on a unioned subquery:

SELECT a.*
FROM (
  SELECT eid FROM view1
  UNION
  SELECT eid FROM view2
  UNION
  SELECT eid FROM view3
  UNION
  SELECT eid FROM view4
  UNION
  SELECT eid FROM view5
) e
JOIN table a ON a.id = e.eid

The important thing here is that the ids from the views come first in the FROM table list, so that table can be accessed efficiently, not the other way around when the in indexed view result would have to be table-scanned for every row of table.

Also note there is no need for DISTINCT because the UNION inside the subquery eliminated duplicates.

This query should perform very well.

Upvotes: 1

Hiren Dhaduk
Hiren Dhaduk

Reputation: 2780

you can use Union in your query .

SELECT DISTINCT e.* 
FROM table e
INNER JOIN
(
SELECT eid FROM view1
UNION ALL
SELECT eid FROM view2
UNION ALL
SELECT eid FROM view3
UNION ALL
SELECT eid FROM view4
UNION ALL
SELECT eid FROM view5
) AS B ON e.eid = B.eid

you should use join query instead of using IN query . IN query will run for each rows of the table so it will take more time . Join query will run only one time .

Upvotes: 1

Himanshu
Himanshu

Reputation: 32602

May be using UNION like this:

SELECT DISTINCT * FROM table e
WHERE e.eid IN
(
    SELECT eid FROM view1
    UNION
    SELECT eid FROM view2
    UNION
    SELECT eid FROM view3
    UNION
    SELECT eid FROM view4
    UNION
    SELECT eid FROM view5
)

Upvotes: 1

Related Questions