Sajith A wrote:
>
>EXPLAIN SELECT qb_test_result.id resultId, qb_question.testId AS
>testId, qb_test.title testName, qb_question.marks, qb_test.passrate,
>qb_test_result.marks testMark, qb_test_result.percentage
>testPercentage, qb_test_result.startTime, qb_test_result.endTime,
>qb_test_result.status
>FROM qb_test_result, qb_test_result_details, qb_test, qb_question
>WHERE qb_test_result.id = qb_test_result_details.resultId
>AND qb_test_result.testId = qb_test.id
>AND qb_test.companyId =1
>AND qb_test.author = '2'
>AND qb_test_result_details.questionId = qb_question.id
>
>
>
>
>
>+------------------------+--------+-----------------------+----------+---------+-----------------------------------+------+-------------+
>| table | type | possible_keys | key |
>key_len | ref | rows | Extra |
>+------------------------+--------+-----------------------+----------+---------+-----------------------------------+------+-------------+
>| qb_test_result | ALL | PRIMARY,testStudent | NULL |
> NULL | NULL | 2494 | |
>| qb_test_result_details | ref | resultId | resultId |
> 4 | qb_test_result.id | 45 | |
>| qb_test | eq_ref | PRIMARY,companyAuthor | PRIMARY |
> 2 | qb_test_result.testId | 1 | Using where |
>| qb_question | eq_ref | PRIMARY | PRIMARY |
> 4 | qb_test_result_details.questionId | 1 | |
>+------------------------+--------+-----------------------+----------+---------+-----------------------------------+------+-------------+
>
>
>
>Thank you
>Sajith A
>
>
>
Try using straight joins to force MySQL to join from qb_test_result first.
The order chosen by the optimizer has no use for keys from this table.
