List:General Discussion« Previous MessageNext Message »
From:gerald_clark Date:June 23 2005 1:19pm
Subject:Re: Indexing not working
View as plain text  
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.

Thread
Indexing not workingSajith A23 Jun
  • Re: Indexing not workingmfatene23 Jun
    • Re: Indexing not workingSajith A23 Jun
      • Re: Indexing not workingmfatene23 Jun
        • Re: Indexing not workingSajith A24 Jun
          • Re: Indexing not workingSGreen24 Jun
    • Re: Indexing not workingMichael Stassen23 Jun
      • Re: Indexing not workingmfatene23 Jun
  • Re: Indexing not workinggerald_clark23 Jun
    • Re: Indexing not workingSajith A24 Jun
      • Re: Indexing not workinggerald_clark24 Jun