Need help optimizing query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Need help optimizing query

Post 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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
aaronhall
DevNet Resident
Posts: 1040
Joined: Tue Aug 13, 2002 5:10 pm
Location: Back in Phoenix, missing the microbrews
Contact:

Post 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.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

Ah ok. That seems like a satisfactory explanation - thanks.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Post Reply