Reputation: 16162
Is the syntax of this query
SELECT * FROM table1
WHERE var_c IN(
SELECT var_a FROM table2
WHERE var_b =55554444
);
Equivalent to this?
SELECT table1.* FROM table1, table2
WHERE (table2.var_a=table1.var_c AND table2.var_b=55554444);
The first one takes about 7-8 seconds to run and the 2nd one takes about .75 seconds to run. When I use a Join statement it takes about 4-5 seconds to run.
Also is the syntax of this
DELETE FROM table1
WHERE var_c IN(
SELECT var_a FROM table2
WHERE var_b =55554444
);
And this:
DELETE table1.* FROM table1, table2
WHERE (table2.var_a=table1.var_c AND table2.var_b=55554444);
The same?
Upvotes: 0
Views: 511
Reputation: 115660
No, the 2 queries are not equivalent. The second can return duplicate rows, if (var_a, var_b)
is not UNIQUE
in table2
The first query though:
SELECT *
FROM table1
WHERE var_c IN
( SELECT var_a
FROM table2
WHERE var_b =55554444
)
and if table2.var_a
does not contain any NULL
values, then it's equivalent to this:
SELECT table1.*
FROM table1
, table2
WHERE table2.var_a = table1.var_c
AND table2.var_b = 55554444
GROUP BY table1.PK --- Primary Key of table1
(which is better to be written with explicit JOIN
syntax as:
SELECT table1.*
FROM table1
JOIN table2
ON table2.var_a = table1.var_c
WHERE table2.var_b = 55554444
GROUP BY table1.PK --- Primary Key of table1
and this:
SELECT *
FROM table1
WHERE EXISTS
( SELECT *
FROM table2
WHERE table2.var_b = 55554444
AND table2.var_a = table1.var_c
)
About performance, the best advice you could get is to test, test and test again with your data. Try with different indexes and figure how to read the EXPLAIN plans.
You'll probably find that IN (SELECT ... FROM ...)
has not the best performance with current MySQL optimizer (although I hear that MariaDB plans some major improvements in next release, Maria 5.3) and that JOIN
and EXISTS
variants perform usually better.
But this heavily depends on the indexes you have on the tables. Without any index, all of them will be slow. And query time < 1 sec
does not mean it's fast. With a billion rows in the tables, yes, it's pretty good. For a table with only a few thousand rows, time < 0.01 sec
should be expected.
Upvotes: 3
Reputation: 1780
Try using an inner join:
SELECT * FROM table1
INNER JOIN table2
ON table2.var_a = table1.var_c
WHERE table2.var_b =55554444
Upvotes: 1