[EMAIL PROTECTED] wrote:
Hi,
have you created an index on (id, testId) ?
i can't see the other indexes ? The only ones are the PK and testStudent, so the
PK is used.
The PK is not used, nor should it be. Look at the EXPLAIN output.
Let me rewrite the query with explicit joins, and reformat it for readability:
SELECT <long list of columns>
FROM qb_test_result
JOIN qb_test_result_details
ON qb_test_result.id = qb_test_result_details.resultId
JOIN qb_test
ON qb_test_result.testId = qb_test.id
JOIN qb_question
ON qb_test_result_details.questionId = qb_question.id
WHERE qb_test.companyId =1
AND qb_test.author = '2';
There are no restrictions on rows from qb_test_result in the WHERE clause, so
every row matches. Hence, a full table scan of qb_test_result is required,
and no index will be used. Adding indexes to qb_test_result won't help.
There is an index on (companyId,author) in qb_test which might have helped,
but apparently the optimizer decided that there would be more matching rows in
qb_test than the total number of rows in qb_test_result. I do notice that
author is an INT, but the query compares it to the string '2'. I doubt that
confused the optimizer, but it should be fixed anyway. Change that comparison to
AND qb_test.author = 2;
Michael
Selon Sajith A <[EMAIL PROTECTED]>:
I was trying to analyze a query that was taking almost 4 seconds to
execute. While trying to create additional indexes - found that the
query is not using any index from table qb_test_result . The type
returned is ALL for qb_test_result .
I have given the tables and query below. It would have been a help if
some one could throw some light on why this is behaving so.. I tried
to read the mysql manual and follow the
steps given there.
<snip>
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
