comparacion - operadores de igualdad en mysql
¿Cuál es la diferencia de rendimiento en las implementaciones de división relacional de MySQL(IN AND en lugar de IN OR)? (1)
Hice algunas mejoras en la versión JOIN ; vea abajo.
Voto por el enfoque JOIN para la velocidad. Así es como lo determiné:
TENIENDO, versión 1
mysql> FLUSH STATUS;
mysql> SELECT city
-> FROM us_vch200
-> WHERE state IN (''IL'', ''MO'', ''PA'')
-> GROUP BY city
-> HAVING count(DISTINCT state) >= 3;
+-------------+
| city |
+-------------+
| Springfield |
| Washington |
+-------------+
mysql> SHOW SESSION STATUS LIKE ''Handler%'';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Handler_external_lock | 2 |
| Handler_read_first | 1 |
| Handler_read_key | 2 |
| Handler_read_last | 1 |
| Handler_read_next | 4175 | -- full index scan
(etc)
+----+-------------+-----------+-------+-----------------------+------------+---------+------+------+--------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------+-----------------------+------------+---------+------+------+--------------------------------------------------+
| 1 | SIMPLE | us_vch200 | range | state_city,city_state | city_state | 769 | NULL | 4176 | Using where; Using index for group-by (scanning) |
+----+-------------+-----------+-------+-----------------------+------------+---------+------+------+--------------------------------------------------+
El ''Extra'' señala que decidió abordar GROUP BY y usar INDEX(city, state) aunque INDEX(state, city) podría tener sentido.
TENIENDO, versión 2
Haciendo que cambie a INDEX(state, city) produce:
mysql> FLUSH STATUS;
mysql> SELECT city
-> FROM us_vch200 IGNORE INDEX(city_state)
-> WHERE state IN (''IL'', ''MO'', ''PA'')
-> GROUP BY city
-> HAVING count(DISTINCT state) >= 3;
+-------------+
| city |
+-------------+
| Springfield |
| Washington |
+-------------+
mysql> SHOW SESSION STATUS LIKE ''Handler%'';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Handler_commit | 1 |
| Handler_external_lock | 2 |
| Handler_read_key | 401 |
| Handler_read_next | 398 |
| Handler_read_rnd | 398 |
(etc)
+----+-------------+-----------+-------+-----------------------+------------+---------+------+------+------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------+-----------------------+------------+---------+------+------+------------------------------------------+
| 1 | SIMPLE | us_vch200 | range | state_city,city_state | state_city | 2 | NULL | 397 | Using where; Using index; Using filesort |
+----+-------------+-----------+-------+-----------------------+------------+---------+------+------+------------------------------------------+
UNIRSE
mysql> SELECT x.city
-> FROM us_vch200 x
-> JOIN us_vch200 y ON y.city= x.city AND y.state = ''MO''
-> JOIN us_vch200 z ON z.city= x.city AND z.state = ''PA''
-> WHERE x.state = ''IL'';
+-------------+
| city |
+-------------+
| Springfield |
| Washington |
+-------------+
2 rows in set (0.00 sec)
mysql> SHOW SESSION STATUS LIKE ''Handler%'';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Handler_commit | 1 |
| Handler_external_lock | 6 |
| Handler_read_key | 86 |
| Handler_read_next | 87 |
(etc)
+----+-------------+-------+------+-----------------------+------------+---------+--------------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+-----------------------+------------+---------+--------------------+------+--------------------------+
| 1 | SIMPLE | y | ref | state_city,city_state | state_city | 2 | const | 81 | Using where; Using index |
| 1 | SIMPLE | z | ref | state_city,city_state | state_city | 769 | const,world.y.city | 1 | Using where; Using index |
| 1 | SIMPLE | x | ref | state_city,city_state | state_city | 769 | const,world.y.city | 1 | Using where; Using index |
+----+-------------+-------+------+-----------------------+------------+---------+--------------------+------+--------------------------+
Solo INDEX(state, city) es necesario. Los números de Handler son los más pequeños para esta formulación, por lo que deduzco que es el más rápido.
Observe cómo el optimizador hizo su propia mente con qué tabla comenzar, probablemente debido a
+-------+----------+
| state | COUNT(*) |
+-------+----------+
| IL | 221 |
| MO | 81 | -- smallest
| PA | 96 |
+-------+----------+
Conclusiones
JOIN (sin la innecesaria tabla t ) es probablemente el más rápido. Además, se necesita este índice compuesto: INDEX(state, city) .
Para volver a traducir a su caso de uso:
city --> documentid
state --> termid
Advertencia: YMMV porque la distribución de los valores de documentid y termid podría ser bastante diferente que el caso de prueba que utilicé.
Debido a que MySQL no tiene un operador de división relacional integrado, los programadores deben implementar los propios. Hay dos ejemplos principales de implementaciones que se pueden encontrar en esta respuesta aquí.
Para la posteridad, los voy a enumerar a continuación:
Usando GROUP BY / HAVING
SELECT t.documentid FROM TABLE t WHERE t.termid IN (1,2,3) GROUP BY t.documentid HAVING COUNT(DISINCT t.termid) = 3La advertencia es que debe usar TENER COUNT (DISTINCT porque duplicados de termid que son 2 para el mismo documentid sería un falso positivo. Y el COUNT tiene que ser igual al número de valores de termid en la cláusula IN.
Usando JOINs
SELECT t.documentid FROM TABLE t JOIN TABLE x ON x.termid = t.termid AND x.termid = 1 JOIN TABLE y ON y.termid = t.termid AND y.termid = 2 JOIN TABLE z ON z.termid = t.termid AND z.termid = 3Pero este puede ser un dolor para el manejo de los criterios que cambia mucho.
De estas dos técnicas de implementación, ¿cuál ofrecería el mejor rendimiento?