CODEWITHSUNDEEP
mysqlsqlquery-optimization
Fabricio
Fabricio

Reputation: 7925

Is it possible to optimize my query?

(You can understand better reading here)

I'd like to know if its possible to optimize this query:

SELECT id_type, value FROM Action, Action_set WHERE id_action = id_action_p1 AND id_action_set = 1
UNION ALL
SELECT id_type, value FROM Action, Action_set WHERE id_action = id_action_p2 AND id_action_set = 1
UNION ALL
SELECT id_type, value FROM Action, Action_set WHERE id_action = id_action_p3 AND id_action_set = 1
UNION ALL
SELECT id_type, value FROM Action, Action_set WHERE id_action = id_action_p4 AND id_action_set = 1
UNION ALL
SELECT id_type, value FROM Action, Action_set WHERE id_action = id_action_p5 AND id_action_set = 1
UNION ALL
SELECT id_type, value FROM Action, Action_set WHERE id_action = id_action_p6 AND id_action_set = 1
UNION ALL
SELECT id_type, value FROM Action, Action_set WHERE id_action = id_action_p7 AND id_action_set = 1
UNION ALL
SELECT id_type, value FROM Action, Action_set WHERE id_action = id_action_p8 AND id_action_set = 1

Upvotes: 1

Views: 59

Answers (2)

David Manheim
David Manheim

Reputation: 2626

OK; first, your query does not do what one would expect. Let's say that one of your actions has 3 action sets; id_action_p1,id_action_p2, and id_action_p3. This means it will show up in 3 different items in your Union All query, but have the same values - because neither id_type nor value depends on the id_action.

If this is intended, you can't just use or - it will only match once - but you are returning items more than once with no way to tell them apart, which seems strange. If that is intended, we can make it simpler by having the list of items in a table itself, and joining it as well.

First, we set up the table:

Select into Table_name id_action_p1 as action
Insert into Table_name (Select id_action_p2)
Insert into Table_name (Select id_action_p3)
Insert into Table_name (Select id_action_p4)
Insert into Table_name (Select id_action_p5)
...

Next, we write our new query:

SELECT id_type, value 
FROM Action, Action_set, Table_name 
WHERE id_action=Table_name.action
AND id_action_set = 1

This should do exactly what your previous query does.

Upvotes: 0

John Conde
John Conde

Reputation: 219814

Try:

SELECT id_type, value 
FROM Action, Action_set 
WHERE id_action IN (id_action_p1,id_action_p2,id_action_p3,id_action_p4,id_action_p5,id_action_p6,id_action_p7,id_action_p8) 
AND id_action_set = 1

If there is more then one row try:

SELECT id_type, value 
FROM Action, Action_set 
WHERE (id_action = id_action_p1
     OR id_action = id_action_p2
     OR id_action = id_action_p3
     OR id_action = id_action_p4
     OR id_action = id_action_p5
     OR id_action = id_action_p6
     OR id_action = id_action_p7
     OR id_action = id_action_p8) 
AND id_action_set = 1

Upvotes: 6

Related Questions