查找此MySQL查询的最佳索引

I'm struggling to understand if I've indexed this query properly, it's somewhat slow and I feel it could use optimization. MySQL 5.1.70

我很难理解我是否正确地索引了这个查询,它有点慢,我觉得它可以使用优化。 MySQL 5.1.70

  select snaps.id, snaps.userid, snaps.ins_time, usr.gender  
    from usersnaps as snaps  
    join user as usr on usr.id = snaps.userid  
    left join user_convert as conv on snaps.userid = conv.userid  
    where (conv.level is null or conv.level = 4) and snaps.active = 'N' 
and (usr.status = "unfilled" or usr.status = "unapproved") and usr.active = 1 
        order by snaps.ins_time asc

usersnaps table (irrelevant deta removed, size about 250k records) :

usersnaps表(删除了无关的详细信息,大小约为250k记录):

CREATE TABLE IF NOT EXISTS `usersnaps` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `userid` int(11) unsigned NOT NULL DEFAULT '0',
  `picture` varchar(250) NOT NULL,
  `active` enum('N','Y') NOT NULL DEFAULT 'N',
  `ins_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`,`userid`),
  KEY `userid` (`userid`,`active`),
  KEY `ins_time` (`ins_time`),
  KEY `active` (`active`)
) ENGINE=InnoDB;

user table (irrelevant deta removed, size about 300k records) :

用户表(删除了无关的详细信息,大小约为300k记录):

 CREATE TABLE IF NOT EXISTS `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `active` tinyint(1) NOT NULL DEFAULT '1',
  `status` enum('15','active','approval','suspended','unapproved','unfilled','rejected','suspended_auto','incomplete') NOT NULL DEFAULT 'approval',
  PRIMARY KEY (`id`),
  KEY `status` (`status`,`active`)
  ) ENGINE=InnoDB;

user_convert table (size about : 60k records) :

user_convert表(大小约为:60k记录):

CREATE TABLE IF NOT EXISTS `user_convert` (
  `userid` int(10) unsigned NOT NULL,
  `level` tinyint(4) NOT NULL,
  UNIQUE KEY `userid` (`userid`),
  KEY `level` (`level`)
) ENGINE=InnoDB;

Explain extended returns :

解释延长回报:

id  select_type table   type    possible_keys               key     key_len ref             rows    filtered    Extra
1   SIMPLE      snaps   ref     userid,default_pic,active   active  1       const           65248   100.00      Using where; Using filesort
1   SIMPLE      usr     eq_ref  PRIMARY,active,status       PRIMARY 4       snaps.userid    1       100.00      Using where
1   SIMPLE      conv    eq_ref  userid                      userid  4s      snaps.userid    1       100.00      Using where

2 个解决方案

#1

Using filesort is probably your performance killer.

使用filesort可能是你的性能杀手。

You need the records from usersnaps where active = 'N' and you need them sorted by ins_time.

您需要来自usersnaps的记录,其中active ='N',您需要按ins_time排序。

ALTER TABLE usersnaps ADD KEY active_ins_time (active,ins_time);

Indexes are stored in sorted order, and read in sorted order... so if the optimizer chooses that index, it will go for the records with active = 'N' and -- hey, look at that -- they're already sorted by ins_time -- because of that index. So as it reads the rows referenced by the index, the result-set internally is already in the order you want it to ORDER BY, and the optimizer should realize this... no filesort required.

索引以排序顺序存储,并按排序顺序读取...因此,如果优化器选择该索引,它将用于具有active ='N'的记录,并且 - 嘿,看看它们 - 它们已经排序by ins_time - 因为那个索引。因此,当它读取索引引用的行时,结果集内部已经按照您希望ORDER BY的顺序排列,优化器应该实现这一点......不需要文件输出。

#2

I would recommend changing the userid index (assuming you're not using it right now) to have active first and userid later.

我建议更改用户标识索引(假设您现在不使用它)以便稍后激活first和userid。

That should make it more useful for this query.

这应该使它对这个查询更有用。

#1