IN vs OR nella clausola SQL WHERE

Quando si ha a che fare con grandi database, che funzionano meglio, IN o OR nella SQL Where -clause?

C’è qualche differenza nel modo in cui vengono eseguiti?

Presumo che tu voglia conoscere la differenza di prestazioni tra quanto segue:

 WHERE foo IN ('a', 'b', 'c') WHERE foo = 'a' OR foo = 'b' OR foo = 'c' 

Secondo il manuale di MySQL se i valori sono costanti, ordina la lista e quindi usa una ricerca binaria. Immagino che OR li valuti uno per uno senza un ordine particolare. Quindi IN è più veloce in alcune circostanze.

Il modo migliore per sapere è quello di profilare entrambi nel database con i dati specifici per vedere quale è più veloce.

Ho provato entrambi su un MySQL con 1000000 righe. Quando la colonna è indicizzata, non ci sono differenze evidenti nelle prestazioni, entrambe sono quasi istantanee. Quando la colonna non è indicizzata, ho ottenuto questi risultati:

 SELECT COUNT(*) FROM t_inner WHERE val IN (1000, 2000, 3000, 4000, 5000, 6000, 7000, 8000, 9000); 1 row fetched in 0.0032 (1.2679 seconds) SELECT COUNT(*) FROM t_inner WHERE val = 1000 OR val = 2000 OR val = 3000 OR val = 4000 OR val = 5000 OR val = 6000 OR val = 7000 OR val = 8000 OR val = 9000; 1 row fetched in 0.0026 (1.7385 seconds) 

Quindi in questo caso il metodo che usa OR è circa il 30% più lento. Aggiungere più termini fa la differenza più grande. I risultati possono variare su altri database e su altri dati.

Il modo migliore per scoprirlo è guardare il piano di esecuzione.


L’ho provato con Oracle , ed era esattamente lo stesso.

 CREATE TABLE performance_test AS ( SELECT * FROM dba_objects ); SELECT * FROM performance_test WHERE object_name IN ('DBMS_STANDARD', 'DBMS_REGISTRY', 'DBMS_LOB' ); 

Anche se la query utilizza IN , il piano di esecuzione dice che utilizza OR :

 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 8 | 1416 | 163 (2)| 00:00:02 | |* 1 | TABLE ACCESS FULL| PERFORMANCE_TEST | 8 | 1416 | 163 (2)| 00:00:02 | -------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OBJECT_NAME"='DBMS_LOB' OR "OBJECT_NAME"='DBMS_REGISTRY' OR "OBJECT_NAME"='DBMS_STANDARD') 

Penso che l’oracle sia abbastanza intelligente da convertire quello meno efficiente (qualunque esso sia) nell’altro. Quindi penso che la risposta dovrebbe piuttosto dipendere dalla leggibilità di ciascuno (dove penso che IN vince chiaramente)

L’operatore OR ha bisogno di un processo di valutazione molto più complesso rispetto al costrutto IN perché consente molte condizioni, non solo come IN.

Ecco un esempio di ciò che è ansible utilizzare con OR ma che non sono compatibili con IN: greater. maggiore o uguale, minore, minore o uguale, LIKE e alcuni altri simili all’oracle REGEXP_LIKE. Inoltre, considera che le condizioni potrebbero non confrontare sempre lo stesso valore.

Per Query Optimizer è più facile gestire l’operatore IN perché è solo un costrutto che definisce l’operatore OR su più condizioni con l’operatore = sullo stesso valore. Se si utilizza l’operatore OR, l’ottimizzatore potrebbe non considerare che si sta sempre utilizzando l’operatore = sullo stesso valore e, se non esegue un’elaborazione più profonda e molto più complessa, potrebbe probabilmente escludere che ci possa essere solo = operatori per gli stessi valori su tutte le condizioni coinvolte, con conseguente preclusione di metodi di ricerca ottimizzati come la già citata ricerca binaria.

[EDIT] Probabilmente un ottimizzatore potrebbe non implementare un processo di valutazione IN ottimizzato, ma ciò non esclude che una volta potrebbe accadere (con un aggiornamento della versione del database). Quindi se usi l’operatore OR che l’elaborazione ottimizzata non verrà utilizzata nel tuo caso.

OR ha senso (dal punto di vista della leggibilità), quando ci sono meno valori da confrontare. IN è utile esp. quando hai una sorgente dynamic, con la quale vuoi confrontare i valori.

Un’altra alternativa è usare un JOIN con una tabella temporanea.
Non penso che le prestazioni dovrebbero essere un problema, a patto di avere gli indici necessari.

Ho fatto una query SQL in un gran numero di OR (350). Postgres lo fa 437.80ms .

Usa OR

Ora usa IN:

Usare in

23.18ms