mysql high performance上有下面一段话,不太明白为啥会better?
mysql> SELECT film_id, description FROM sakila.film ORDER BY title LIMIT 50, 5;
If the table is very large, this query is better written as follows:
mysql> SELECT film.film_id, film.description
-> FROM sakila.film
-> INNER JOIN (
-> SELECT film_id FROM sakila.film
-> ORDER BY title LIMIT 50, 5
-> ) AS lim USING(film_id);
mysql> SELECT film_id, description FROM sakila.film ORDER BY title LIMIT 50, 5;
If the table is very large, this query is better written as follows:
mysql> SELECT film.film_id, film.description
-> FROM sakila.film
-> INNER JOIN (
-> SELECT film_id FROM sakila.film
-> ORDER BY title LIMIT 50, 5
-> ) AS lim USING(film_id);
-> ORDER BY title LIMIT 50, 5这个只是产生了一个5条记录的结果集,然后再和sakila.film做连接,这样总记录集会很小。
SELECT film_id FROM sakila.film LIMIT 50,1 )LIMIT 5;
--这条怎样?
SELECT film_id FROM sakila.film ORDER BY title LIMIT 50, 5
结果只有51-55的5条记录,且只有film_id字段 ,再与工作表连接
差一点就说到点子上了。
真正的原因在于“覆盖索引”,
SELECT film_id FROM sakila.film ORDER BY title LIMIT 50, 5,
如果没有覆盖索引(film_id,title),那么第二种方法就不会快。
SELECT film_id, description FROM sakila.film ORDER BY title LIMIT 50, 5;
就说这个跟
SELECT film.film_id, film.description
-> FROM sakila.film
-> INNER JOIN (
-> SELECT film_id FROM sakila.film
-> ORDER BY title LIMIT 50, 5
-> ) AS lim USING(film_id);
有什么区别吧?第一个肯定是是用索引来进行排序的?
覆盖索引跟这个有有什么关系呢?
【第一个排序不也是用索引了吗?】