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   

Lottery

did you have an index on courses.ID? – Senator

yes it has index in the testimonials table and its a primary key of the courses table. – Lottery

Because it doesn't look like you have... – Barbitone

the way MYSQL does joins is to calculate the LEFT first, then attempt the joins. So switch to a RIGHT JOIN placing the larger tables in the where clause dev.mysql.com/doc/refman/5.0/en/left-join-optimization.html – Disvalue

Interesting, are they the same data type? It's not using any key on courses and doing an expensive file sort for the join.. – Barbitone

@Disvalue - could you post an example query - just an sample one. thx – Lottery

@Waygood. oooooooooooh, that's not nice. – Barbitone

@TonyHopkinson - Yes same datatype - courseid column in testimonials table is "int(10), UNSIGNED" – Lottery

Please post the schema (as in the CREATE TABLE export) for the courses table. – Tuck

I think JW was asking if you have an index on ID in the COURSES table. Can you show the DDL for all four tables? – Danaus

Please see Edit 2. I have now removed the courses index - it took 5 seconds to delete this index. I just thought to give this query another go (without an index) but it executed quicker! how strange!! – Lottery

@Danaus - Yes, I can confirm that course.id is the primary key. – Lottery

Please do as asked by more than one of us and post the table definitions! – Tuck

@CaptainPayalytic - please see EDIT 3 ... I have added the index back now.. – Lottery

I cannot see why you left join with exam - in my eyes this join is not necessary? – Derron

@DelPedro - sorry I need to join that as I need to query some values from that table too. There is type in the query above as it should be e.feedback instead of f.feedback. – Lottery

@DelPedro sorry my bad again - i m just testing the changes i m making and replying here ... anyway I have updated the Edit 3 you can see the last one is the course id – Lottery

Maybe I'm missing something (wouldn't be the first time)... But where are the columns t.status, t.verfication_required, t.verfication_completed, and t.submissiondate coming from? I didn't see them in the CREATE TABLE query... – Kimberelykimberlee

did you ever get this resolved? It's still listed as no answer accepted. – Lunulate

You need to update with other tables, even with info provided, when you give us DDL for your tables that don't have rows in your query you need to optimize - we can't help. – Menticide

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)

Lunulate

Answer 2

Could you give a try to 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 courses table?

Fill