Where am I wrong in using the connection in mysql query - Explain also the result

Question

I have this query that takes about 3.5 seconds to get 2 entries. However, in the reviews there are more than 100 thousand lines, 13 thousand 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 

. Get the 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 delete the join of the courses table, then the query will return the result pretty quickly. I can’t understand why this query should select all course lines, i.e. 850?

Any ideas what I'm doing wrong?

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

EDIT 2

I just deleted the course index from the review table (just to check), and it is interesting that the query result returned in 0.22 seconds !!! ?? Everything else, as above, just deleted 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 last result of an explanation request 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

Executing ORDER BY, which does not have a corresponding index that can be used, is known to cause lag problems. Even though this does not specifically answer your question about the course table.

Your original query looks like MOST fine, but you are referencing "f.feedback" and there are no aliases in the query "f". You also refer to verify_required and verify_completed, but you don’t see them in the table structures, but look for exam_required and emr_completed.

I would, however, change one thing. In the review table, instead of individual column indexes, I would add another with multiple columns to use your multiple criteria query and order

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

but it looks like your query is for columns not listed in your table structure list, but could be instead

 KEY StatVerifySubmit ( status, exam_required, emr_completed, emr_Date) 

Answer 2

Can you try the following query instead of the original:

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

Do you need to select columns from the course table?