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)
分享到:
相关推荐
MySQL-5.6-全方位效能调优-中文
MySQL SQL高级特性 字段约束-索引-视图-外键学习实践,很不错
MySQL性能调优与架构设计--全册,压缩包文件,欢迎下载
MySQL如何支持事务处理? 什么是主键和外键?它们之间有什么区别? 什么是数据库范式?列举一些常见的数据库范式。 什么是数据库连接池?为什么使用连接池可以提高数据库访问性能? 什么是数据库事务隔离级别?...
MySQL是一个小型关系型数据库管理系统,开发者为瑞典MySQL AB公司。在2008年1月16号被Sun公司收购。而2009年,SUN又被Oracle收购。MySQL是一种关联数据库管理系统,关联数据库将数据保存在不同的表中,而不是将所有...
性能调优专题_-Mysql索引优化与底层数据结构深入剖析
实战MySQL慢SQL调优
MYSQL调优
MySQL性能调优与架构设计-简朝阳 提供4种版本 .epub .mobi .pdf .txt 可复制,非扫描图片版
腾讯的当家数据库!!想看看腾讯这样的大公司使用的数据库的功能吗?本书就比较细致讲解了Mysql的高级性能,想成为MySql高手,就赶紧下载看看吧!!!
整理了MySql在日常开发中一些Sql语句的分析和调优技巧
这是阿里软件工程师和技术人员在mysql数据库应用方面的一个总结,里面涉及到了mysql的架构设计、调优实践、如何构架大规模的数据库集群等,绝对实用。
4.6、唯一性太差的字段,不适合建立索引(如状态字段、类型字段),当一条Query返回的数据超过了全表的15%时 不应该用索引,更新频繁字段不适合索引 4.7、一般情况只会用一个索引,所以可以建组合索引 4.8、不支持索引...
mysql-sql优化-小米开源-soar 。。。。。
《DB2 SQL性能调优秘笈》是一本不可多得的DB2数据库性能调优秘笈,由拥有20余年DB2工作经验的资深数据库专家撰写,Amazon全五星评价畅销书。《DB2 SQL性能调优秘笈》不仅详尽阐述了100余条SQL语句优化的技巧和最佳...
MySQL性能调优与架构设计 - 简朝阳
《MySQL性能调优与架构设计》书中应用到的数据库的创建脚本--书中附录部分--example数据库脚本