Need help optimizing query
Posted: Fri Nov 16, 2007 2:57 pm
Hi all,
I've got a query that I think can be optimized more - MySQL agrees.
Here's my query:
And the two tables involved:
Here's the result of the EXPLAIN statement:
Phew - lot of info there. The point is that the query isn't using the primary key index on the `question` table to do this query. I'm wondering why. The Docs say that having a `Type` value of "ALL" and an `Extra` value of "Using temporary" or "Using filesort" is bad - but not a lot of info on how to remedy that situation.
Thanks for any & all input.
I've got a query that I think can be optimized more - MySQL agrees.
Here's my query:
Code: Select all
SELECT
question.id as id,
question.text as text,
question.plaintext,
question.date_added as date_added,
question.tags as tags,
question.asked as asked,
answer.text as answer_text,
answer.position
FROM
question
LEFT JOIN
answer
ON
answer.question_id = question.id
ORDER BY
date_added desc,
position ascCode: Select all
mysql> desc question;
+---------------+-----------------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-----------------------------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| instance_id | int(11) | NO | MUL | | |
| text | text | NO | | | |
| plaintext | text | NO | MUL | | |
| tags | text | YES | | NULL | |
| question_type | enum('multiple','single','qualitative') | YES | | single | |
| date_added | int(11) | NO | MUL | 0 | |
| asked | tinyint(1) | NO | | 0 | |
+---------------+-----------------------------------------+------+-----+---------+----------------+
mysql> show index from question;
+----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| question | 0 | PRIMARY | 1 | id | A | 3 | NULL | NULL | | BTREE | |
| question | 1 | instance_id | 1 | instance_id | A | NULL | NULL | NULL | | BTREE | |
| question | 1 | date_added | 1 | date_added | A | NULL | NULL | NULL | | BTREE | |
| question | 1 | plaintext | 1 | plaintext | NULL | NULL | NULL | NULL | | FULLTEXT | |
+----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
mysql> desc answer;
+-------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| instance_id | int(11) | NO | MUL | | |
| question_id | int(11) | NO | MUL | 0 | |
| text | text | NO | | | |
| position | smallint(6) | NO | | 1 | |
| date_added | int(11) | NO | | 0 | |
| asked | tinyint(1) | NO | | 0 | |
+-------------+-------------+------+-----+---------+----------------+
mysql> show index from answer;
+--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| answer | 0 | PRIMARY | 1 | id | A | 8 | NULL | NULL | | BTREE | |
| answer | 1 | id | 1 | id | A | NULL | NULL | NULL | | BTREE | |
| answer | 1 | instance_id | 1 | instance_id | A | NULL | NULL | NULL | | BTREE | |
| answer | 1 | question_id | 1 | question_id | A | NULL | NULL | NULL | | BTREE | |
+--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+Code: Select all
+----+-------------+----------+------+---------------+-------------+---------+---------------------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+---------------+-------------+---------+---------------------+------+---------------------------------+
| 1 | SIMPLE | question | ALL | NULL | NULL | NULL | NULL | 3 | Using temporary; Using filesort |
| 1 | SIMPLE | answer | ref | question_id | question_id | 4 | wassail.question.id | 2 | |
+----+-------------+----------+------+---------------+-------------+---------+---------------------+------+---------------------------------+Phew - lot of info there. The point is that the query isn't using the primary key index on the `question` table to do this query. I'm wondering why. The Docs say that having a `Type` value of "ALL" and an `Extra` value of "Using temporary" or "Using filesort" is bad - but not a lot of info on how to remedy that situation.
Thanks for any & all input.