inner - tipos de select mysql
MySQL: cómo indexar una cláusula “OR” (2)
La forma típica de romper los predicados OR es con UNION .
Tenga en cuenta que su ejemplo no encaja bien con sus índices. Incluso si omitiste field1 del predicado, tendrías field2 >= 1000 OR field3 >= 2000 , que no puede usar un índice. Si tuviera índices en (field1, field2) y (field1,field3) (field1, field2) o field2 o field2 separado, obtendría una consulta razonablemente rápida.
SELECT COUNT(*) FROM
(SELECT * FROM table WHERE field1 = ''value'' AND field2 >= 1000
UNION
SELECT * FROM table WHERE field1 = ''value'' AND field3 >= 2000) T
Tenga en cuenta que debe proporcionar un alias para la tabla derivada, razón por la cual la subconsulta tiene un alias como T
Un ejemplo del mundo real. Los nombres de columnas y tablas han sido anonimizados!
mysql> SELECT COUNT(*) FROM table;
+----------+
| COUNT(*) |
+----------+
| 3059139 |
+----------+
1 row in set (0.00 sec)
mysql> SELECT COUNT(*) FROM table WHERE columnA = value1;
+----------+
| COUNT(*) |
+----------+
| 1068 |
+----------+
1 row in set (0.00 sec)
mysql> SELECT COUNT(*) FROM table WHERE columnB = value2;
+----------+
| COUNT(*) |
+----------+
| 947 |
+----------+
1 row in set (0.00 sec)
mysql> SELECT COUNT(*) FROM table WHERE columnA = value1 OR columnB = value2;
+----------+
| COUNT(*) |
+----------+
| 1616 |
+----------+
1 row in set (9.92 sec)
mysql> SELECT COUNT(*) FROM (SELECT * FROM table WHERE columnA = value1
UNION SELECT * FROM table WHERE columnB = value2) T;
+----------+
| COUNT(*) |
+----------+
| 1616 |
+----------+
1 row in set (0.17 sec)
mysql> SELECT COUNT(*) FROM (SELECT * FROM table WHERE columnA = value1
UNION ALL SELECT * FROM table WHERE columnB = value2) T;
+----------+
| COUNT(*) |
+----------+
| 2015 |
+----------+
1 row in set (0.12 sec)
Estoy ejecutando la siguiente consulta.
SELECT COUNT(*)
FROM table
WHERE field1=''value'' AND (field2 >= 1000 OR field3 >= 2000)
Hay un índice sobre campo1 y otro compuesto sobre campo2 y campo3.
Veo que MySQL siempre selecciona el índice field1 y luego hace una combinación usando los otros dos campos, lo cual es bastante malo porque necesita unir 146.000 filas.
¿Sugerencias sobre cómo mejorar esto? Gracias
(EDITAR DESPUÉS DE PROBAR LA SOLUCIÓN PROPUESTA)
Basado en la solución propuesta, he visto esto en Mysql cuando juego con esto.
SELECT COUNT(*) FROM (SELECT * FROM table WHERE columnA = value1
UNION SELECT * FROM table WHERE columnB = value2) AS unionTable;
es mucho más lento que ejecutar:
SELECT COUNT(*)
FROM table
WHERE (columnA = value1 AND columnB = value2)
OR (columnA = value1 AND columnC = value3)
Teniendo dos índices compuestos:
index1 (columnA,columnB)
index2 (columnA,columnC)
Lo suficientemente interesante es que pedirle a Mysql que "explique" la consulta está tomando siempre index1 en ambos casos y no se usa index2.
Si cambio los índices a:
index1 (columnB,columnA)
index2 (columnC,columnA)
Y la consulta a:
SELECT COUNT(*)
FROM table
WHERE (columnB = value2 AND columnA = value1)
OR (columnC = value3 AND columnA = value1)
Entonces es la forma más rápida en que he encontrado que funciona Mysql.
Soy nuevo aquí, así que no puedo comentar sobre las publicaciones de otras personas, pero esto está relacionado con las publicaciones de David M. y soulmerge.
La tabla temporal no es necesaria. La UNIÓN que sugirió David M. no hace doble conteo, ya que la UNIÓN implica un carácter distinto (es decir, si existe una fila en la mitad de la unión, ignórela en la otra). Si usaste UNION ALL, obtendrías dos registros.
El comportamiento predeterminado para UNION es que las filas duplicadas se eliminan del resultado. La palabra clave DISTINCT opcional no tiene otro efecto que el valor predeterminado porque también especifica la eliminación de filas duplicadas. Con la palabra clave ALL opcional, la eliminación de la fila duplicada no se produce y el resultado incluye todas las filas coincidentes de todas las declaraciones SELECT.