mysql - 在 mysql 查询中使用 Join 我哪里出错了 - Explain result posted

Question

我的这个查询只需要大约 3.5 秒就可以获取 2 条记录。然而，推荐中有超过 10 万行，用户中有 13,000 行，类(class)中有 850 行，考试中有 2 行。

SELECT t.*, u.name, f.feedback
FROM testmonials t
INNER JOIN user u ON u.id = t.userid
INNER JOIN courses co ON co.id = t.courseid
LEFT JOIN exam ex ON ex.id = t.exam_id
WHERE t.status = 4
AND t.verfication_required = 'Y'
AND t.verfication_completed = 'N'
ORDER BY t.submissiondate DESC

.解释结果:.

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  co  ALL     PRIMARY     NULL    NULL    NULL    850     Using temporary; Using filesort
1   SIMPLE  t   ref     CID,nuk_tran_user   CID     4   kms.co.id   8   Using where
1   SIMPLE  u   eq_ref  PRIMARY     PRIMARY     4   kms.t.userid    1   Using where
1   SIMPLE  ex  eq_ref  PRIMARY     PRIMARY     3   kms.t.eval_id   1   

如果我删除 courses 表连接，那么查询会很快返回结果。我不明白为什么这个查询必须选择所有类(class)行，即 850？

知道我做错了什么吗？

编辑:我在推荐表中有一个 courseid 和 userid 的索引，这些是它们各自表的主键。

编辑 2

我刚刚从推荐表中删除了 courseid 索引(只是为了测试)，有趣的是查询在 0.22 秒内返回了结果!!!？？其他一切同上，只是删除了这个索引。

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  t   ALL     nuk_tran_user   NULL    NULL    NULL    130696  Using where; Using filesort
1   SIMPLE  u   eq_ref  PRIMARY     PRIMARY     4   kms.t.userid    1   Using where
1   SIMPLE  co  eq_ref  PRIMARY     PRIMARY     4   kms.t.courseid  1   
1   SIMPLE  ex  eq_ref  PRIMARY     PRIMARY     3   kms.t.exam_id   1   

编辑 3

编辑 3

CREATE TABLE IF NOT EXISTS `courses` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `duration` varchar(100) NOT NULL DEFAULT '',
  `objectives` text NOT NULL,
  `updated_at` datetime DEFAULT NULL,
  `updated_by` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=851 ;


Testimonials


CREATE TABLE IF NOT EXISTS `testimonials` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `feedback` text NOT NULL,  
  `userid` int(10) unsigned NOT NULL DEFAULT '0',
  `courseid` int(10) unsigned NOT NULL DEFAULT '0',
  `eventid` int(10) unsigned NOT NULL DEFAULT '0',
  `emr_date` datetime DEFAULT NULL,
  `exam_required` enum('Y','N') NOT NULL DEFAULT 'N',
  `exam_id` smallint(5) unsigned NOT NULL DEFAULT '0',
  `emr_completed` enum('Y','N') NOT NULL DEFAULT 'N',
  PRIMARY KEY (`id`),
  KEY `event` (`eventid`),
  KEY `nuk_tran_user` (`userid`),
  KEY `emr_date` (`emr_date`),
  KEY `courseid` (`courseid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=134691 ;

..这是现在最新的Explain查询结果...

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  t   ALL     nuk_tran_user,courseid  NULL    NULL    NULL    130696  Using where; Using filesort
1   SIMPLE  u   eq_ref  PRIMARY     PRIMARY     4   kms.t.userid    1   Using where
1   SIMPLE  co  eq_ref  PRIMARY     PRIMARY     4   kms.t.courseid  1   
1   SIMPLE  ex  eq_ref  PRIMARY     PRIMARY     3   kms.t.exam_id   1   

Answer 1

已知执行没有相应索引可用的 ORDER BY 会导致延迟问题。即使这没有具体回答您的类(class)表问题。

您的原始查询看起来基本没问题，但您引用了“f.feedback”并且查询中没有“f”别名。您还提到了“verification_required”和“verification_completed”，但在表结构中看不到它们，但确实找到了“exam_required”和“emr_completed”。

但是我会改变一件事。在 testimonials 表中，我将添加一个包含多个列的索引，而不是单独的列索引，以利用您的多条件查询和 order by

create table ...
KEY StatVerifySubmit ( status, verification_required, verification_completed, submissionDate )

但您的查询似乎指的是未在您的表结构列表中列出的列，但可能是

KEY StatVerifySubmit ( status, exam_required, emr_completed, emr_Date)