Page 1 of 1

Need help optimizing query

Posted: Fri Nov 16, 2007 2:57 pm
by pickle
Hi all,

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 asc
And the two tables involved:

Code: 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      |         |
+--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
Here's the result of the EXPLAIN statement:

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.

Posted: Wed Nov 21, 2007 6:29 am
by aaronhall
Looks right to me. The "ALL" indicates that a full table scan is performed (fine, since you're returning every row in the table), so the only primary key that's useful to MySQL is the one on answers, and it's using it. If you don't need the extra rows from `question` that aren't matched against `answer`, an inner join will force mysql to use the primary on `question`. The filesort and temporary table are a result of the ORDER BY clause... you can play around with that a little, but the engine won't use indexes for sorting if you mix DESC and ASC.

Posted: Wed Nov 21, 2007 11:02 am
by pickle
Ah ok. That seems like a satisfactory explanation - thanks.