How to use the following variable content for an IN query...
SET @var = '1,2,3';
SELECT * FROM Table WHERE ID IN (@var);
Asked
Active
Viewed 51 times
-2
-
1Show the schema of your tables – 1000111 Jun 30 '16 at 13:05
-
you have no `GROUP BY` or `DISTINCT` in original query `SELECT OtherID FROM Table WHERE X = 1` so it is not 100% equivalent. – Alex Jun 30 '16 at 13:58
-
I have editted (instead of deleting) my question to re focus it on the core element of the question... the existing answer was trying to focus on the wrong part.. which is why it is not relevant anymore. – Barry Jul 03 '16 at 07:33
2 Answers
1
Use a JOIN
SELECT t1.*
FROM Table t1
JOIN Table t2 on t1.OtherID = t2.OtherID
WHERE t2.X = 1
juergen d
- 201,996
- 37
- 293
- 362
-
-
@Barry what do you mean by _much, much faster_ ? can you provide some numbers? to me the only reason why that trick could be faster is you just did not properly tested mysql caching. so try to run both query with `SQL_NO_CACHE` to see original performance of each. – Alex Jun 30 '16 at 13:45
-
@Barry: And using my query is still slow? Then add an index to column `X`. That should solve your problem – juergen d Jun 30 '16 at 14:01
0
Solution was found by looking at this answer
SET @var = '1,2,3';
SET @Expression=CONCAT('SELECT * FROM TableWHERE ID IN (',@var,')');
PREPARE myquery FROM @Expression;
EXECUTE myquery
