Consideraciones de velocidad con LIMIT

Un compañero de trabajo que es un crack en temas de optimización de bases de datos me ha pasado la siguiente información. Cuando hacemos una consulta con un LIMIT x, y sobre una tabla con millones de registros, a medida que x aumenta, la consulta se va haciendo cada vez más lenta. Veamos un ejemplo:

SELECT version FROM car LIMIT 300,100; (100 results, 0.01 sec)

SELECT version FROM car LIMIT 3000000,100; (100 results, 2.5 sec)

Si además agregamos un par de JOINS a esta consulta, la complicamos un poco y le añadimos un ORDER BY, los problemas de rendimiento pueden llegar a causar un colapso en la base de datos.

¿Por qué pasa esto?

Para determinar el primer valor que tiene que seleccionar, MySQL debe recorrer desde el primer registro e ir contando hasta llegar al 300, a partir de ahí, cogerá 100. Obviamente, a medida que el valor de X va aumentando, la búsqueda del primer elemento es más lenta y como podemos ver en el ejemplo de arriba, llega a tardar 2,5 segundos en recorrer 3.000.000 de registros.

¿Cómo lo podemos solucionar?

Mi compañero propone hacer la búsqueda acotando a partir de primary key de la tabla, de este modo, al ser un campo indexado la búsqueda es realmente eficiente y nos evitamos cargar la máquina:

SELECT version FROM car WHERE id_car BETWEEN 3000000 AND 3000100; (~100 results, 0.01 sec)

Esta solución no nos sería válida a la hora de construir un paginador, porque en el caso de tener vehículos desactivados u eliminados, unas páginas contendrían 100 vehículos, otras quizá 74… sin embargo, si por ejemplo construimos un script que se recorra toda la tabla car esta es una buena manera de realizar la búsqueda.