CODEWITHSUNDEEP
mysqlsql
Max Rln
Max Rln

Reputation: 13

Performance increase using two queries instead of a subquery?

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!

Upvotes: 0

Views: 33

Answers (1)

RoMEoMusTDiE
RoMEoMusTDiE

Reputation: 4824

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?

Upvotes: 1

Related Questions