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

Does mysql identify duplicate values in a IN query?

Ex:

SELECT * 
 FROM person 
WHERE name IN ('Michael', 'Michael', 'Dan')

Let’s say that the name of the first line in the database is ‘John’. It will check ‘Michael’ two times for John? or it will identify that ‘Michael’ is the same value, so do it only once

I’ve used :

EXPLAIN SELECT * 
FROM person 
WHERE name IN ('Michael', 'Michael', 'Dan')

and the value in field ‘filtered’ was multiplied by 10 for each ‘Micheael’ i added until six ‘Michael’. then it kept in 50.
I’d like to know what it means to.

Tags:

2

Answers


  1. 0

    https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html#operator_in explains this:

    If no type conversion is needed for the values in the IN() list, they are all non-JSON constants of the same type, and expr can be compared to each of them as a value of the same type (possibly after type conversion), an optimization takes place. The values the list are sorted and the search for expr is done using a binary search, which makes the IN() operation very quick.

    So the answer to your question is: maybe. It depends on the values you put inside the IN() list.

    Login or Signup to reply.
  2. 0

    MySQL doesn’t inherently capture duplicate values in the list. Each value in the IN clause will be checked independently, In case there are duplicates, MySQL will process them separately.

    values you get via filtered column when using EXPLAIN indicates the projection of the number of rows after applying all conditions.

    The figure you saw of 50% is basically a stabilizing figure of MySQL after rounds of projection for value Micheal. If you are more keen on optimizing try reducing the number of duplicate values at the earlier stages of the query.

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

Back To Top
Search