mysql query sql 效能调优记录-排序时最快的取出尽量少的字段且索引字段(三)

十二12

　　
mysql>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 ;
+--------+--------------+--------------------------------+------------------------+------------------------------------------------------+------------+----------------+------------+---------------+
| id     | company_name | company_url                    | company_albums_name    | company_albums_url                                   | view_count | thumbnail_path | qq         | brand_shop_id |
+--------+--------------+--------------------------------+------------------------+------------------------------------------------------+------------+----------------+------------+---------------+
----------------------
+--------+--------------+--------------------------------+------------------------+------------------------------------------------------+------------+----------------+------------+---------------+
2 rows in set (3.10 sec)


mysql>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)




mysql> 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
    -> ;
   
2 rows in set (0.34 sec)


mysql> 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 69510, 18 ) 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)