fields - order by field mysql

¿Es posible hacer que MySQL use un índice para el ORDEN por 1 DESC, 2 ASC? (3)

Tengo un tablón de anuncios materializado conducido camino. Está utilizando la siguiente consulta para ordenar los mensajes,

SELECT * FROM Board ORDER by root DESC, path ASC LIMIT 0,100

donde root es un id del mensaje raíz para el subproceso y la path es una ruta materializada.

Sin embargo, ninguno de mis esfuerzos para hacer esta consulta para usar índices tuvo ningún éxito.

En este momento está mostrando el número de todas las filas en la tabla debajo de la columna de rows . Me pregunto, ¿hay alguna forma de reducir ese número u optimizar la consulta de otra manera?

CREATE TABLE `Board` ( `id` int(11) NOT NULL AUTO_INCREMENT, `path` varchar(255) NOT NULL DEFAULT ''0'', `root` int(11) NOT NULL DEFAULT ''0'', PRIMARY KEY (`id`), KEY `root` (`root`), KEY `path` (`path`), KEY `rootpath` (`root`,`path`) )

El principal problema con la consulta es la paginación: necesito comenzar la segunda página desde el mensaje que se encuentra al lado de la última página. Por eso lo quiero de manera directa , sin subelecciones y esas cosas.
Sin embargo, la configuración actual no es muy buena, ya que comienza la segunda página desde la mitad del hilo, pero al menos es bastante lógica.

El problema al que te enfrentas se explica muy bien en este artículo . Y la parte importante es:

El caso más típico es cuando desea ordenar por dos columnas en diferentes direcciones: ... ORDEN POR precio ASC, fecha DESC LIMIT 10 Si ha indexado (precio, fecha) en orden ascendente, no podrá optimizar bien esta consulta - Se necesitará una ordenación externa ("filesort"). Si pudiera generar un índice en el precio ASC, en la fecha en que DESC la misma consulta podría recuperar los datos en un orden ordenado por área.

También el artículo menciona una solución válida para el problema: tener la segunda cláusula de "orden" invertida:

Sin embargo, esto es algo que puede solucionar teniendo una columna como "reverse_date" y usándola para ordenar. Con MySQL 5.0, incluso puede utilizar los activadores para actualizarlo como actualizaciones de fecha reales, por lo que se vuelve menos feo. De hecho, esto es, por ejemplo, el motivo por el que vería el campo "reverse_timestamp" en la estructura de la tabla de Wikipedia.

También de la documentación oficial de MySQL :

En algunos casos, MySQL no puede usar índices para resolver el ORDEN POR, aunque aún usa índices para encontrar las filas que coinciden con la cláusula WHERE. Estos casos incluyen los siguientes:

.....

Mezclas ASC y DESC:

SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;

Como sugerencia, es mejor que tenga una columna reversed_root que sea Integer.MAX_VALUE - root Y tenga un índice activado (reversed_root, ruta). Entonces puedes tener una consulta como:

SELECT * FROM Board ORDER by reversed_root ASC,path ASC LIMIT 0,100

En esta situación en la que los datos en sí no se prestan a la recuperación de la manera que necesita, puede ser apropiado crear una columna adicional que tenga la información que necesita, lo que le permitirá recuperar en el orden que desee.

Sería especialmente apropiado en este caso, ya que parece que los datos en sí no se actualizan una vez que los guarda. Una vez que se publican sus mensajes, no se actualizan (o eso parece de mi lectura inicial).

Los pasos que recomendaría, suponiendo que tomes este camino, serían:

Agregue una nueva columna root_path de root_path a la tabla.
Ejecute esta instrucción de update Board set root_path = root + path . (Es posible que tenga que adaptar eso en función de los tipos de datos de las columnas existentes).
Siempre que agregue una nueva fila a la tabla, agregue también esta nueva columna. (Esto podría manejarse con un disparador, aunque desconfiaría de los disparadores, ya que pueden pasarse por alto cuando las personas están cambiando otras partes del código).

Entonces deberías poder establecer un índice en esa nueva columna y escribir tus selecciones en esa columna, presionando tus índices como desees.

Creo que esto funcionará aunque una de las claves tenga que estar ordenada en orden opuesto.

CREATE TABLE foo ( id serial NOT NULL, int_field integer DEFAULT 0, varchar_field character varying(255), composite_field character varying(255), CONSTRAINT foo_pkey PRIMARY KEY (id ) ); CREATE INDEX composite_field_idx ON foo (composite_field); INSERT INTO foo (int_field, varchar_field, composite_field) VALUES (1,''t'',''t1''), (2,''z'',''z2''), (2,''w'',''w2''), (4,''u'',''u4''), (5,''u'',''u5''), (5,''x'',''x5''), (7,''v'',''v7''); explain select * from foo order by composite_field desc;

Ejecute el código anterior y la instrucción de explicación debe mostrar la clave composite_field_idx a la que se hace referencia.

El resultado de la consulta es:

select * from foo order by composite_field desc; id | int_field | varchar_field | composite_field ----+-----------+---------------+----------------- 2 | 2 | z | z2 6 | 5 | x | x5 3 | 2 | w | w2 7 | 7 | v | v7 5 | 5 | u | u5 4 | 4 | u | u4 1 | 1 | t | t1

Tu consulta original

SELECT * FROM Board ORDER by root DESC, path ASC LIMIT 0,100;

Cree una tabla para mantener el valor negativo de la raíz llamada BoardDisplayOrder, donde agrega la nueva columna llamada rootinv.

Primero aquí están los datos de muestra y su consulta original:

mysql> drop database if exists YourCommonSense; Query OK, 2 rows affected (0.06 sec) mysql> create database YourCommonSense; Query OK, 1 row affected (0.00 sec) mysql> use YourCommonSense Database changed mysql> CREATE TABLE `Board` ( -> `id` int(11) NOT NULL AUTO_INCREMENT, -> `path` varchar(255) NOT NULL DEFAULT ''0'', -> `root` int(11) NOT NULL DEFAULT ''0'', -> PRIMARY KEY (`id`), -> KEY `root` (`root`), -> KEY `path` (`path`), -> KEY `rootpath` (`root`,`path`) -> ); Query OK, 0 rows affected (0.11 sec) mysql> INSERT INTO Board (path,root) VALUES -> (''Rolando Edwards'',30), -> (''Daniel Edwards'',30), -> (''Pamela Edwards'',30), -> (''Dominiuqe Edwards'',40), -> (''Diamond Edwards'',40), -> (''Richard Washington'',50), -> (''George Washington'',50), -> (''Synora Washington'',50); Query OK, 8 rows affected (0.05 sec) Records: 8 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM Board; +----+--------------------+------+ | id | path | root | +----+--------------------+------+ | 2 | Daniel Edwards | 30 | | 3 | Pamela Edwards | 30 | | 1 | Rolando Edwards | 30 | | 5 | Diamond Edwards | 40 | | 4 | Dominiuqe Edwards | 40 | | 7 | George Washington | 50 | | 6 | Richard Washington | 50 | | 8 | Synora Washington | 50 | +----+--------------------+------+ 8 rows in set (0.00 sec) mysql> SELECT * FROM Board ORDER by root DESC, path ASC LIMIT 0,100; +----+--------------------+------+ | id | path | root | +----+--------------------+------+ | 7 | George Washington | 50 | | 6 | Richard Washington | 50 | | 8 | Synora Washington | 50 | | 5 | Diamond Edwards | 40 | | 4 | Dominiuqe Edwards | 40 | | 2 | Daniel Edwards | 30 | | 3 | Pamela Edwards | 30 | | 1 | Rolando Edwards | 30 | +----+--------------------+------+ 8 rows in set (0.00 sec) mysql> EXPLAIN SELECT * FROM Board ORDER by root DESC, path ASC LIMIT 0,100; +----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------------+ | 1 | SIMPLE | Board | index | NULL | rootpath | 261 | NULL | 8 | Using index; Using filesort | +----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------------+ 1 row in set (0.00 sec) mysql>

A continuación, cree la tabla BoardDisplayOrder usando rootinv y un índice que involucre a rootinv:

mysql> CREATE TABLE BoardDisplayOrder LIKE Board; Query OK, 0 rows affected (0.09 sec) mysql> ALTER TABLE BoardDisplayOrder DROP INDEX root; Query OK, 0 rows affected (0.11 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE BoardDisplayOrder DROP INDEX path; Query OK, 0 rows affected (0.09 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE BoardDisplayOrder DROP INDEX rootpath; Query OK, 0 rows affected (0.08 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE BoardDisplayOrder ADD COLUMN rootinv int(11) NOT NULL; Query OK, 0 rows affected (0.17 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE BoardDisplayOrder ADD INDEX rootpathid (rootinv,path,id,root); Query OK, 0 rows affected (0.11 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SHOW CREATE TABLE BoardDisplayOrder /G *************************** 1. row *************************** Table: BoardDisplayOrder Create Table: CREATE TABLE `boarddisplayorder` ( `id` int(11) NOT NULL AUTO_INCREMENT, `path` varchar(255) NOT NULL DEFAULT ''0'', `root` int(11) NOT NULL DEFAULT ''0'', `rootinv` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `rootpathid` (`rootinv`,`path`,`id`,`root`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql>

Luego, rellene BoardDisplayOrder:

mysql> INSERT INTO BoardDisplayOrder (id,path,root,rootinv) -> SELECT id,path,root,-root FROM Board; Query OK, 8 rows affected (0.06 sec) Records: 8 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM BoardDisplayOrder; +----+--------------------+------+---------+ | id | path | root | rootinv | +----+--------------------+------+---------+ | 7 | George Washington | 50 | -50 | | 6 | Richard Washington | 50 | -50 | | 8 | Synora Washington | 50 | -50 | | 5 | Diamond Edwards | 40 | -40 | | 4 | Dominiuqe Edwards | 40 | -40 | | 2 | Daniel Edwards | 30 | -30 | | 3 | Pamela Edwards | 30 | -30 | | 1 | Rolando Edwards | 30 | -30 | +----+--------------------+------+---------+ 8 rows in set (0.00 sec) mysql>

Ahora, ejecute su consulta en BoardDisplayOrder pero sin DESC en rootinv:

mysql> SELECT id,path,root FROM BoardDisplayOrder ORDER by rootinv, path LIMIT 0,100; +----+--------------------+------+ | id | path | root | +----+--------------------+------+ | 7 | George Washington | 50 | | 6 | Richard Washington | 50 | | 8 | Synora Washington | 50 | | 5 | Diamond Edwards | 40 | | 4 | Dominiuqe Edwards | 40 | | 2 | Daniel Edwards | 30 | | 3 | Pamela Edwards | 30 | | 1 | Rolando Edwards | 30 | +----+--------------------+------+ 8 rows in set (0.00 sec) mysql> EXPLAIN SELECT id,path,root FROM BoardDisplayOrder ORDER by rootinv, path LIMIT 0,100; +----+-------------+-------------------+-------+---------------+------------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------------+-------+---------------+------------+---------+------+------+-------------+ | 1 | SIMPLE | BoardDisplayOrder | index | NULL | rootpathid | 269 | NULL | 8 | Using index | +----+-------------+-------------------+-------+---------------+------------+---------+------+------+-------------+ 1 row in set (0.00 sec) mysql>

¡¡¡Darle una oportunidad!!!

ADVERTENCIA

Esto fue fácil de hacer porque la raíz era INT.

Si root fuera un VARCHAR, rootinv tendría que ser un flipflop de caracteres. En otras palabras,

A -> Z
B -> Y
...
M -> N
N -> M
...
Y -> B
Z -> A

Esto funcionaría principalmente para cualquier campo en el que necesite realizar DESC. El problema se deriva del hecho de que MySQL no ordena claves internamente dentro del índice como ASC o DESC. Todo en un índice es ascendente. Es por eso que cuando ves las estadísticas del controlador en SHOW GLOBAL STATUS LIKE ''handler%''; , ves lo siguiente:

Etcétera.

Según la documentación actual de MySQL.

Una especificación de index_col_name puede terminar con ASC o DESC. Estas palabras clave están permitidas para futuras extensiones para especificar el almacenamiento de valor de índice ascendente o descendente. Actualmente, son analizados pero ignorados; Los valores de índice siempre se almacenan en orden ascendente.

¡¡¡Darle una oportunidad!!!

ACTUALIZACIÓN 2012-05-04 06:54 EDT

Comentario de @ frail sobre mi respuesta

ALTER TABLE BoardDisplayOrder ADD INDEX rootpathid (rootinv, path, id, root) me parece bastante innecesario, ALTER TABLE BoardDisplayOrder ADD INDEX rootpathid (rootinv, path) debería ser suficiente

La razón por la que mi solución tenía ALTER TABLE BoardDisplayOrder ADD INDEX rootpathid (rootinv,path,id,root) es para proporcionar un índice de cobertura. Un índice de cobertura en este caso:

Siempre tenga las columnas necesarias para su recuperación.
Mejorará la calidad del plan explicativo porque
- La consulta nunca se leerá de la tabla para la recuperación de datos.
- La consulta solo se leerá del índice para la recuperación de datos.
- resulta en un escaneo de rango de índice

Piense en la consulta original,

SELECT * FROM Board ORDER by root DESC, path ASC LIMIT 0,100;

Esto requiere recuperar las tres columnas ruta, id y raíz. Por lo tanto, necesitan estar en el índice. Por supuesto, el aumento del tamaño del índice sería la compensación. Si la mesa de la Junta fuera muy grande, algunos no se preocuparían por el espacio si la recuperación se pudiera hacer más rápido. Si el índice de ruta de raíz fuera solo (rootinv, ruta), entonces cada exploración de rango de índice iría acompañada de una búsqueda de referencia en la tabla para las columnas restantes. Esta es la razón por la que elegí ALTER TABLE BoardDisplayOrder ADD INDEX rootpathid (rootinv,path,id,root);