排序时最快的取出尽量少的字段且索引字段
select company_albums.id,company.name as company_name,company.url as company_url,company_albums.name as company_albums_name,company_albums.url as company_albums_url,view_count,thumbnail_path,qq ,brand_shop_id
from company,company_albums
where company_albums.com_id=company.id
ORDER BY `view_count` desc
LIMIT 69600, 15 ;
explain select company_albums.id,company.name as company_name,company.url as company_url,company_albums.name as company_albums_name,company_albums.url as company_albums_url,view_count,thumbnail_path,qq ,brand_shop_id
from company,company_albums
where company.id=company_albums.com_id
ORDER BY `view_count` desc
LIMIT 69600, 15 \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: company_albums
type: ALL
possible_keys: com_idx
key: NULL
key_len: NULL
ref: NULL
rows: 72441
Extra: Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: company
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: test_01.company_albums.com_id
rows: 1
Extra:
2 rows in set (0.00 sec)
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
select company_albums.id,company_albums.name as company_albums_name,company_albums.url as company_albums_url,view_count,thumbnail_path,company.name as company_name,company.url as company_url,qq ,brand_shop_id
from company_albums ,(select company_albums.id as ca_id
from company,company_albums
where company_albums.com_id=company.id
ORDER BY `view_count` desc
LIMIT 69600, 15) as t,company
where company.id=company_albums.com_id and t.ca_id = company_albums.id
order by view_count desc;
explain select company_albums.id,company_albums.name as company_albums_name,company_albums.url as company_albums_url,view_count,thumbnail_path,company.name as company_name,company.url as company_url,qq ,brand_shop_id
from company_albums ,(select company_albums.id as ca_id
from company,company_albums
where company_albums.com_id=company.id
ORDER BY `view_count` desc
LIMIT 69600, 15) as t,company
where company.id=company_albums.com_id and t.ca_id = company_albums.id
order by view_count desc\G;
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: <derived2>
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 18
Extra: Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table: company_albums
type: eq_ref
possible_keys: PRIMARY,com_idx
key: PRIMARY
key_len: 4
ref: t.ca_id
rows: 1
Extra:
*************************** 3. row ***************************
id: 1
select_type: PRIMARY
table: company
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: test_01.company_albums.com_id
rows: 1
Extra:
*************************** 4. row ***************************
id: 2
select_type: DERIVED
table: company_albums
type: ALL
possible_keys: com_idx
key: NULL
key_len: NULL
ref: NULL
rows: 72441
Extra: Using filesort
*************************** 5. row ***************************
id: 2
select_type: DERIVED
table: company
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: test_01.company_albums.com_id
rows: 1
Extra: Using index
5 rows in set (0.34 sec)
