mysql愿意_有人愿意帮助优化MySQL查询吗?

以下是问题:

SELECT COUNT(*) AS c, MAX(`followers_count`) AS max_fc,

MIN(`followers_count`) AS min_fc, MAX(`following_count`) AS max_fgc,

MIN(`following_count`) AS min_fgc, SUM(`followers_count`) AS fc,

SUM(`following_count`) AS fgc, MAX(`updates_count`) AS max_uc,

MIN(`updates_count`) AS min_uc, SUM(`updates_count`) AS uc

FROM `profiles`

WHERE `twitter_id` IN (SELECT `followed_by`

FROM `relations`

WHERE `twitter_id` = 123);

这两张桌子是

profiles

和

relations

. 都有超过1000000行,InnoDB引擎。两者都有索引

twitter_id

,

关系

在上有额外的索引(

特特特里德

,

followed_by

)执行查询需要6秒钟,这真的让我很沮丧。我知道我可以加入进来,但是我的MySQL知识并不那么酷,这就是为什么我需要你的帮助。

提前谢谢大家=)

干杯,

K~

更新的

好吧,我设法降到了2,5秒。我使用了内部联接并添加了三个索引对。以下是解释结果:

id, select_type, table, type, possible_keys,

key, key_len, ref, rows, Extra

1, 'SIMPLE', 'r', 'ref', 'relation',

'relation', '4', 'const', 252310, 'Using index'

1, 'SIMPLE', 'p', 'ref', 'PRIMARY,twiter_id,id_fc,id_fgc,id_uc',

'id_uc', '4', 'follerme.r.followed_by', 1, ''

希望这有帮助。

另一个更新

以下是两个表的show create table语句:

CREATE TABLE `profiles` (

`twitter_id` int(10) unsigned NOT NULL,

`screen_name` varchar(45) NOT NULL default '',

`followers_count` int(10) unsigned default NULL,

`following_count` int(10) unsigned default NULL,

`updates_count` int(10) unsigned default NULL,

`location` varchar(45) default NULL,

`bio` varchar(160) default NULL,

`url` varchar(255) default NULL,

`image` varchar(255) default NULL,

`registered` int(10) unsigned default NULL,

`timestamp` int(10) unsigned default NULL,

`relations_timestamp` int(10) unsigned default NULL,

PRIMARY KEY USING BTREE (`twitter_id`,`screen_name`),

KEY `twiter_id` (`twitter_id`),

KEY `screen_name` USING BTREE (`screen_name`,`twitter_id`),

KEY `id_fc` (`twitter_id`,`followers_count`),

KEY `id_fgc` (`twitter_id`,`following_count`),

KEY `id_uc` (`twitter_id`,`updates_count`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `relations` (

`id` int(10) unsigned NOT NULL auto_increment,

`twitter_id` int(10) unsigned NOT NULL default '0',

`followed_by` int(10) unsigned default NULL,

`timestamp` int(10) unsigned default NULL,

PRIMARY KEY USING BTREE (`id`,`twitter_id`),

UNIQUE KEY `relation` (`twitter_id`,`followed_by`)

) ENGINE=InnoDB AUTO_INCREMENT=1209557 DEFAULT CHARSET=utf8

哇,真是一团糟!对不起!