mysql中explain看效能

標籤：

select distinct col_name from table where a=X and b=Y and date(time)=‘xx-xx-xx‘;
執行時間 27.9772 秒

explain select distinct col_name from table where a=X and b=Y and date(time)=‘xx-xx-xx‘;

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	access	ALL	NULL	NULL	NULL	NULL	38714243	Using where; Using temporary

 

select distinct col_name from table where a=X and b=Y and time<‘xx-xx-xx‘;
執行時間 0.0038 秒

explain select distinct col_name from table where a=X and b=Y and time<‘xx-xx-xx‘;

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	access	range	idx_access_dt	idx_access_dt	4	NULL	1	Using where; Using temporary

select distinct col_name from table where a=X and b=Y and time=‘xx-xx-xx xx:xx:xx‘;
執行時間 0.0031 秒

explain select distinct col_name from table where a=X and b=Y and time=‘xx-xx-xx xx:xx:xx‘;

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	access	ref	idx_access_dt	idx_access_dt	4	const	82	Using where; Using temporary

 

參考：
http://www.cnitblog.com/aliyiyi08/archive/2008/09/09/48878.html 

mysql中explain看效能