Un tema con una respuesta un poco extensa
Te desgloso que tiene internamente el motor MySQL como tal :
Donde:
Connection Thread/HandlingSe refiere al conector que utilizas para acceder al motor mediante algun lenguaje de programacion (un modulo o jdbc tu entiendes).
Query CacheDespues de conectarte y enviar tu consulta SQL en tu codigo, lo primero que MySQL hace es verificar su buffer cache (query cache) que guarda en una cache del motor todas las consultas SELECT (Tu consulta SQL pasa por aqui primeramente haber si ya se realizo y retorno datos anteriormente, asi se ahorra tener que realizar todo el proceso otra vez y saca todo de la cache...) Pero no en todas las ocasiones Query Cache esta habilitado en el motor.. y no del todo conviene habilitarlo tampoco... yo solo queria informarte que luego de conectarte, lo segundo que haces es pasar por la cache.
ParserSino hay nada en la cache similar a tu consulta SQL, entonces sigues al 'parser' del motor, aqui es donde este se encarga de buscar toda la informacion dentro de las tablas y organizarlas acorde al predicado (eso que pones despues de WHERE).
Obviare el punto del Optimizador y de los motores, ya que no haces referencia de que quieras usar EXPLAIN.
El ORDER BY, Ordena la tabla y luego busca los resultados y los devuelve ordenados... O Devuelve los resultados Ordenados pero sin haberlos ordenado para devolverlos??? (notan la diferencia?)
Depende.... si haces tu consulta a traves de un lenguaje (PHP, Perl, Python, Java, etc..) recuerda que si no utilizas funciones tipo mysql_ubbuffered_cache o use_results o similares, aqui tu consulta lo que hace es una especie de SELECT * al motor para que este retorne todos los registros disponibles y estos son guardados en la buffer cache del modulo o api que usa tu lenguaje para conectarse a MySQL, luego que tienes ahi guardado todos esos registros en el buffer de tu API es entonces ahi que este funciona como el parser de MySQL, aplica todo lo que viene despues del WHERE de tu consulta pero a los datos guardados en el buffer de tu API no de MySQL como tal. Ahora si haces la consulta usando las funciones mencionada anteriormente, o por el cliente de Mysql directamente, entonces aca se cumple todo lo expuesto mas arriba (lo de verificar query cache, parser, etc) y entonces dentro del motor (aunque esto es transparente para ti) se retorna toda la data pero al buffer cache del motor y entonces ahi actua el 'parser' y organiza todo y entonces retorna lo que pediste. Primero se obtiene todos los registros y luego que estan todos retornados es que se aplica lo que viene despues del WHERE, entonces podria decirse:
ORDER BY busca en la tabla, obtiene los resultados y luego los ordena y los devuelve.
Esto funciona asi, es como cuando en un lenguaje de programacion abres un archivo de texto con mucha informacion, y dentro de tu lenguaje dices... sacame y ordename los registros que encuentres que empiezen por la letra 'A' lo primero que hace tu aplicacion obviamente es abrir el archivo de texto, leerlo por completo.. y despues de tener toda la info en memoria entonces procede a ir separando todos los que empiezan por la letra 'A'... el motor de base de datos hace el mismo proceso
Sobre tu segunda pregunta, generalmente hacen un rejuego con la clausula LIMIT por ejemplo si tienes una tabla con 100 registros, van haciendo en un bucle algo asi:
SELECT * FROM TABLA ORDER BY CAMPO1 LIMIT $numero, $numerito;
Donde $numero es una variable que digamos su valor inicial en su bucle es 90 y el valor inicial en el bucle de $numerito es 100, entonces la consulta te retornara los 10 ultimos registros de los 100 (digase los registros que van del 90 al 100) luego vas restando tu bucle y el valor de $numerito pasa a ser 90 y el de $numero 80 entonces ahora te retornara los registros del 80 al 90 y asi sucesivamente... (todo controlado a nivel de codigo en el lenguaje). La manera de ordenarlo como usaras ORDER BY y sino especificas si es ASC o DESC el por defecto lo hace de manera Ascendente, por ende si tu ultimo registro dice 1 y tu antepenultimo registro dice 2 el te ordenara todo colocando primero el 1 despues el 2.