Where am I going wrong in using a Join in the mysql query - Explain result posted too

Question

I have this query which takes about 3.5 seconds just to fetch 2 records. However there are over 100k rows in testimonials, 13k in users, 850 in courses, 2 in exams.

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

.Explain result: .

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   

If I remove the courses table join then the query returns the result pretty quick. I can't figure out why this query has to select all the courses rows i.e. 850?

Any ideas what I am doing wrong?

Edit: I have an index on courseid, userid in testimonials table and these are primary keys of their respective tables.

EDIT 2

I have just removed the courseid index from the testimonials table (just to test) and interestingly the query returned result in 0.22 seconds!!!?? Everything else the same as above just removed only this index.

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   

EDIT 3

EDIT 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 ;

.. this is the latest Explain query result now ...

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

Doing an ORDER BY that does not have a corresponding index that can be utilized is known to cause delay issues. Even though this does not specifically answer your issue of the courses table.

Your original query looks MOSTLY ok, but you reference "f.feedback" and there is no "f" alias in the query. You also refer to "verification_required" and "verification_completed" but don't see those in the table structures but DO find "exam_required" and "emr_completed".

I would however change one thing. In the testimonials table, instead of individual column indexes, I would add one more with multiple columns to both take advantage of your multiple criteria query AND the order by

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

but appears your query is referring to columns not listed in your table structure listing, but instead might be

KEY StatVerifySubmit ( status, exam_required, emr_completed, emr_Date)