¿SQLite optimiza una consulta con múltiples condiciones AND en la cláusula WHERE?

En las bases de datos SQL (uso Python + Sqlite), cómo asegurarse de que, si tenemos 1 millón de filas, la consulta

SELECT * FROM mytable WHERE myfunction(description) < 500 AND column2 < 1000 [-----------------------------] [--------------] high-CPU cost condition easy-to-test requiring 100 µs per test condition 

¿Está optimizado para que la primera condición (CPU cara) solo se pruebe si la segunda condición fácil de probar ya es verdadera? (ya que es un AND lógico, ¿es un AND perezoso?)

Ejemplo:

Nota:

  • column2 no es necesaria una ID, podría ser otra cosa

  • en mi caso de uso, mi myfunction implica el cálculo de la distancia Levenshtein

(Respuesta actualizada basada en comentarios y pruebas posteriores).

La respuesta real a tu pregunta.

¿Cómo asegurarnos de que, si tenemos 1 millón de filas, la consulta … se optimice de modo que la primera condición (CPU cara) solo se pruebe si la segunda condición fácil de probar ya es verdadera?

depende de

  • las condiciones reales en la cláusula WHERE, y
  • lo inteligente que es el optimizador de consultas SQLite para estimar el costo de esas condiciones.

Una prueba simple debe decirle si su consulta estará suficientemente “optimizada” para sus necesidades. La buena noticia es que SQLite realizará primero la condición fácil (económica), al menos en ciertas circunstancias.

Para una mesa de prueba “mytable”

 CREATE TABLE mytable ( description TEXT(50) NOT NULL, column2 INTEGER NOT NULL, CONSTRAINT mytable_PK PRIMARY KEY (column2) ); 

que contiene un millón de filas

 description column2 ----------- ------- row000000 0 row000001 1 row000002 2 ... row999999 999999 

el código de prueba de Python

 import sqlite3 import time log_file_spec = r'C:\Users\Gord\Desktop\log_file.txt' def myfunc(thing): with open(log_file_spec, 'a') as log: log.write('HODOR\n') return(int(thing[-6:])) with open(log_file_spec, 'w'): pass # just empty the file cnxn = sqlite3.connect(r'C:\__tmp\SQLite\test.sqlite') cnxn.create_function("myfunction", 1, myfunc) crsr = cnxn.cursor() t0 = time.time() sql = """\ SELECT COUNT(*) AS n FROM mytable WHERE myfunction(description) < 500 AND column2 < 1000 """ crsr.execute(sql) num_rows = crsr.fetchone()[0] print(f"{num_rows} rows found in {(time.time() - t0):.1f} seconds") cnxn.close() 

devoluciones

 500 rows found in 1.2 seconds 

y contando las líneas en log_file.txt vemos

 C:\Users\Gord>find /C "HODOR" Desktop\log_file.txt ---------- DESKTOP\LOG_FILE.TXT: 1000 

lo que indica que nuestra función solo fue llamada mil veces, no un millón de veces. SQLite ha aplicado claramente la column2 < 1000 primero, y luego aplicó la myfunction(description) < 500 en el subconjunto de filas de la primera condición.


(Original "off the cuff" respuesta.)

La respuesta real a su pregunta depende de qué tan inteligente sea el optimizador de consultas. Una prueba simple debe decirle si su consulta estará suficientemente "optimizada" para sus necesidades.

Sin embargo, tiene un par de opciones si sus pruebas encuentran que su enfoque original es demasiado lento:

Opción 1: intente hacer la comparación simple "primero"

Cambiar el orden podría afectar el plan de consulta, por ejemplo,

 ... WHERE  AND  

podría llegar a ser más rápido que

 ... WHERE  AND  

Opción 2: Intente forzar el pedido utilizando una subconsulta

Una vez más, dependiendo de la inteligencia del optimizador de consultas

 SELECT easy.* FROM (SELECT * FROM mytable WHERE column2 < 1000) easy WHERE myfunction(easy.description) < 500 

podría aplicar la condición económica primero, luego aplicar la condición costosa en el subconjunto de filas resultante. (Sin embargo, un comentario indica que SQLite es demasiado sofisticado para caer en esa estrategia).

Una forma en que puede forzar el orden de ejecución es mediante una expresión de case . En general, los optimizadores de SQL pueden reorganizar las operaciones, la única excepción es el case .

 SELECT * FROM mytable WHERE (CASE WHEN column2 >= 1000 OR column2 IS NULL THEN 0 WHEN myfunction(description) < 500 THEN 1 END) = 1; 

En general, las expresiones de case se recomiendan en las cláusulas WHERE . . . Una de las principales razones es que impiden la optimización. En este caso, eso es algo bueno.

SQLite estará felizmente reordenando las expresiones conectadas AND cada vez que lo desee. Entonces, al reescribir la consulta para verificar que la column2 parece funcionar primero en la versión actual, no hay garantía.

El optimizador de consultas supone que la velocidad está determinada principalmente por la E / S del disco, por lo que estima que el costo de ambas condiciones es el mismo. Las estimaciones de costos están influenciadas por los índices y por las estadísticas de ANALYZE (que funcionan solo para datos indexados). Entonces, la forma más fácil de acelerar esta consulta (y probablemente la mayoría de las otras consultas que usará) es crear un índice en column2 :

 CREATE INDEX my_little_index ON mytable(column2); 

Si no desea usar un índice por algún motivo, debe usar una construcción que el optimizador de consultas no pueda optimizar. Una expresión de CASO como se muestra en la respuesta de Gordon funcionaría bien. En el caso general, mueva la primera condición a una subconsulta y evite el aplanamiento de la subconsulta rompiendo una de las reglas enumeradas; agregar una cláusula LIMIT ficticia a ambas consultas generalmente es más fácil:

 SELECT * FROM (SELECT * FROM mytable WHERE column2 < 1000 LIMIT -1) WHERE myfunction(description) < 500 LIMIT -1; 

Inspirado por la respuesta de @GordThompson, aquí hay un punto de referencia entre:

 (1) SELECT * FROM mytable WHERE col2 < 1000 AND myfunction(col1) < 500 

contra

 (2) SELECT * FROM mytable WHERE myfunction(col1) < 500 AND col2 < 1000 

Prueba (1) (condición fácil de probar primero): 1.02 segundos

 import sqlite3, time, random def myfunc(x): time.sleep(0.001) # wait 1 millisecond for each call of this function return x # Create database db = sqlite3.connect(':memory:') db.create_function("myfunction", 1, myfunc) c = db.cursor() c.execute('CREATE TABLE mytable (col1 INTEGER, col2 INTEGER)'); for i in range(10*1000): a = random.randint(0,1000) c.execute('INSERT INTO mytable VALUES (?, ?)', (a, i)); # Do the evil query t0 = time.time() c.execute('SELECT * FROM mytable WHERE col2 < 1000 AND myfunction(col1) < 500') for e in c.fetchall(): print e print "Elapsed time: %.2f" % (time.time() - t0) 

Resultado: 1,02 segundos, significa que myfunc ha sido llamado máximo 1000 veces, es decir, no para todas las filas de 10k .


Prueba (2) (condición de cálculo lento primero): 10.05 segundos

Ídem con:

 c.execute('SELECT * FROM mytable WHERE myfunction(col1) < 500 AND col2 < 1000') 

en lugar.

Resultado: 10.05 segundos, significa que myfunc ha sido llamado ~ 10k veces, es decir, para todas las filas de 10k , incluso aquellas para las cuales la condición col2 < 1000 no es Verdadera.


Conclusión global: Sqlite realiza una evaluación perezosa para AND , es decir, la condición fácil debe escribirse primero así:

 ... WHERE  AND