Performance increase using two queries instead of a subquery?

Question

I do use InnoDB with MySQL

Hey,

I do have two tables: First one is called adtells and contains very few elements and is rarely updated. Second one contains a lot of elements and gets new inserts on a regular base.

The second table stores, which user has hidden an element out of the first table. I think I'm not able to join these tables.

My first query was something like this: SELECT xy FROM firstTable WHERE 1 NOT IN (SELECT abc FROM secondTable)

My second approach are two queries: The first selects all needed elements out of the second table. With those results, I access the first table.

What are your thoughts to those approaches? Is the first one or the second one more performant or do you have any other ideas?

Further explanation of the given example: Table 1: id | content Table 2: userId, tableOneId

The second table contains an assignment from a given user to an element of the first table. If there is an assignment, the affected tableOneId shall not be retrieved by the queries above.

Would love to hear from you!

use NOT EXISTS rather than in http://stackoverflow.com/questions/915643/select-where-not-exists — RoMEoMusTDiE, May 16 '17 at 00:30
Well, I do need In, I want to remove the elements which are in the IN-clause — Max Rln, May 16 '17 at 00:33
you might want to check this to give you a better understanding of the usage http://stackoverflow.com/questions/24929/difference-between-exists-and-in-in-sql — RoMEoMusTDiE, May 16 '17 at 00:36
The measuring is a good idea. If I add the query time of the two indepentend queries I receive half of the time of the one-query approach — Max Rln, May 16 '17 at 00:47

score 1 · Accepted Answer · edited May 23 '17 at 12:34

1

Use NOT EXISTS

SELECT t1.* FROM firstTable T1
  WHERE 
    NOT EXISTS 
        (SELECT 1 FROM secondTable T2 where t2.id = t1.id)

NOT EXISTS/EXISTS will terminate when the first match is found

IN will search all rows regardless of how many matches.. if there are 100 matches then 100 iterations.

Based on rule optimizer:

EXISTS is much faster than IN, when the sub-query results is very large. IN is faster than EXISTS, when the sub-query results is very small. Based on cost optimizer:

There is no difference.

Difference between EXISTS and IN in SQL?

edited May 23 '17 at 12:34

Community

1
1

answered May 16 '17 at 00:32

RoMEoMusTDiE

4,739
1
17
26

Are you sure that this is faster than the two queries approach? And I'm not sure if I understand the query exactly, but I still want to receive all other results of the first table, even if one is hidden by the second table. – Max Rln May 16 '17 at 00:42
you are only looking for a "no match" at the 2nd table but wanting to display fields from the first table right? – RoMEoMusTDiE May 16 '17 at 00:45
Exactly, I want to display all tables of the first table except those who have a second table match – Max Rln May 16 '17 at 00:52
yup.. why don't you try it. – RoMEoMusTDiE May 16 '17 at 00:54
Well you're right. This seems to be much more faster than my approaches. Thank you! – Max Rln May 16 '17 at 01:11
Could you explain me how the query is executed? Why is this faster than the two query approach? And do you think it is faster with much more elements in both tables? – Max Rln May 16 '17 at 01:12
explanation should be here http://stackoverflow.com/questions/24929/difference-between-exists-and-in-in-sql – RoMEoMusTDiE May 16 '17 at 01:17

Performance increase using two queries instead of a subquery?

1 Answers1