Question 301005 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql query IN or NOT IN faster?

I have two simple queries below:

SELECT * from table_name where status IN (0, 1, 2, 3, 4)
SELECT * from table_name where status NOT IN (5, 6)

The possible value of status is 0 to 6, and column status is indexed.

In the queries above, WHERE IN has 5 statuses, but WHERE NOT IN only has 2 statuses. I don’t have much data in my DB so the query speed does not have any different.

But what if the data is big, which query will be faster?

Tags:

2

Answers


  1. 0

    The clauses IN and NOT IN are both performance intensive as the result depends on the size of the datasets used in operation. However an optimised approach depends on using JOIN operator or EXISTS or NOT EXISTS subqueries and indexing the column will give signficant improvement in performance.

    Login or Signup to reply.
  2. 0

    In my opinion,

    • The WHERE IN query is likely to be faster, especially if the status column is indexed. This is because the IN condition with a list of values can take advantage of the index to quickly locate the matching rows. On the other hand, the NOT IN condition, with fewer values, may still benefit from the index but might involve scanning a larger portion of it.

    • If your dataset is large, and the status column is indexed, prefer the WHERE IN query for better performance.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search