- 论坛徽章:
- 0
|
先说表结构
CREATE TABLE `keyword` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`key` char(64) NOT NULL,
`value` char(32) DEFAULT NULL,
`type` char(16) NOT NULL,
`update_time` datetime DEFAULT NULL,
`weighing` tinyint(4) DEFAULT '0',
`count` int(11) DEFAULT '0',
PRIMARY KEY (`id`,`key`),
KEY `dudu_keyword_key` (`key`,`value`,`count`)
) ENGINE=MyISAM AUTO_INCREMENT=950 DEFAULT CHARSET=utf8
每次根据key做like,然后对value做distinct操作,根据count以及weighing做排序,取前10条,key和value是多对一的关系,例如'sina'=>'新浪','新浪'=>'新浪','xinlang'=>'新浪'....
大家肯定很奇怪,为什么要不id和key做联合主键,我本意是想用innodb,在主键上做查询,效率应该要高些,结果我失望了,like的时候压根就没有用主键.
于是对sql中用到的key,value,count做联合索引
最开始设想sql如下:select distinct value from keyword where key like "x%" order by count limit 10;
mysql> explain select distinct value from keyword where `key` like "x%" order by count limit 10\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: dudu_keyword
type: range
possible_keys: dudu_keyword_key
key: dudu_keyword_key
key_len: 192
ref: NULL
rows: 38
Extra: Using where; Using temporary; Using filesort
1 row in set (0.00 sec)
杯具,使用了临时表还进行了排序操作
于是继续尝试:SELECT value FROM dudu_keyword where `key` like 'x%' group by value order by count limit 10
explain还是基本和上面一样:1, 'SIMPLE', 'dudu_keyword', 'range', 'dudu_keyword_key', 'dudu_keyword_key', '192', '', 38, 'Using where; Using index; Using temporary; Using filesort'
确是没办法不让group by或distinct不使用临时表!有经验的同仁停下脚,呵呵 |
|