Geek asks and answers

SQL NOT IN [identifier list] (performance)

I'm just wondering if the number of identifiers in the list will affect query performance.

request example:

SELECT * FROM foos WHERE foos.ID NOT IN (2, 4, 5, 6, 7)

Where (2, 4, 5, 6, 7)is an infinitely long list.

And how much is too much (in the context of order)?

UPDATE: The reason why I ask this is because I have two dB. One of them (read-only) is the source of the elements, and the other contains elements that are processed by the operator. Every time an operator requests a new item from a read-only database, I want to exclude an item that has already been processed.

+9
source share
4 answers

, . , , :

WHERE foo.ID <> 2
AND foo.ID <> 4
AND foo.ID <> 5
AND ...

, .

+9

,

SELECT * FROM foos
LEFT JOIN
(
    SELECT 2 id UNION
    SELECT 4    UNION
    SELECT 5    UNION
    SELECT 6    UNION
    SELECT 7
) NOT_IDS
USING (id) WHERE NOT_IDS.id IS NULL;

NOT_IDS , :

mysql> SELECT * FROM
    -> (
    ->     SELECT 2 id UNION
    ->     SELECT 4    UNION
    ->     SELECT 5    UNION
    ->     SELECT 6    UNION
    ->     SELECT 7
    -> ) NOT_IDS;
+----+
| id |
+----+
|  2 |
|  4 |
|  5 |
|  6 |
|  7 |
+----+
5 rows in set (0.00 sec)

mysql>
+4

, :

...

insert into db1.foos (cols) 
  select cols
    from db2.foos src
  left join db1.foos dst
    on src.pk = dst.pk
  where dst.othercolumn is null

, , ( , ), , .

+3

DBs are on the same server? If so, you can make a multi-db request with a left connection and accept nulls. (here's an example: Querying multiple databases at once ). Otherwise, you can make a stored procedure, pass an identifier with a string, and split them inside with a regular expression. I have a similar problem, but inside db in memory and postgres db. Fortunately, my situation (In ...)

0
source

More articles:


All Articles