0

I can't get rid-off "temporary; Using filesort" when ordering columns from the second table

explain SELECT * 
          FROM post 
          JOIN post_plus 
          ON post_plus.news_id = post.id 
         WHERE category regexp '[[:<:]](131|138|139|140|141|142|143|144|145|146|147|148|149|150|151|152|153|154|155|156|157|171|172|134|136|137|23|123)[[:>:]]' 
           AND approve=1 
           AND allow_main=1 
      ORDER BY kp_votes DESC, 
               kp_rating DESC LIMIT 30;
+----+-------------+---------------+------+--------------------------------------+------------+---------+--------------------+-------+----------------------------------------------+
| id | select_type | table         | type | possible_keys                        | key        | key_len | ref                | rows  | Extra                                        |
+----+-------------+---------------+------+--------------------------------------+------------+---------+--------------------+-------+----------------------------------------------+
|  1 | SIMPLE      | post          | ref  | PRIMARY,allow_main,approve,approve_2 | allow_main | 1       | const              | 12273 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | post_plus     | ref  | news_id                              | news_id    | 5       | online.post.id     |     1 | NULL                                         |
+----+-------------+---------------+------+--------------------------------------+------------+---------+--------------------+-------+----------------------------------------------+

as you can see here, without the order kp_votes DESC kp_rating DESC I don't get "temporary; Using filesort"

explain SELECT *            FROM post            JOIN post_plus            ON post_plus.news_id = post.id           WHERE category regexp '[[:<:]](131|138|139|140|141|142|143|144|145|146|147|148|149|150|151|152|153|154|155|156|157|171|172|134|136|137|23|123)[[:>:]]'             AND approve=1             AND allow_main=1    LIMIT 30;
+----+-------------+---------------+------+--------------------------------------+------------+---------+--------------------+-------+-------------+
| id | select_type | table         | type | possible_keys                        | key        | key_len | ref                | rows  | Extra       |
+----+-------------+---------------+------+--------------------------------------+------------+---------+--------------------+-------+-------------+
|  1 | SIMPLE      | post      | ref  | PRIMARY,allow_main,approve,approve_2 | allow_main | 1       | const              | 13521 | Using where |
|  1 | SIMPLE      | post_plus | ref  | news_id,top_order                    | news_id    | 5       | online.post.id |     1 | NULL        |
+----+-------------+---------------+------+--------------------------------------+------------+---------+--------------------+-------+-------------+

SHOW INDEX FROM post_plus;

+-----------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table     | Non_unique | Key_name         | Seq_in_index | Column_name      | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| post_plus |          0 | PRIMARY          |            1 | pid              | A         |       32317 |     NULL | NULL   |      | BTREE      |         |               |
| post_plus |          1 | user_id          |            1 | user_id          | A         |          26 |     NULL | NULL   |      | BTREE      |         |               |
| post_plus |          1 | news_id          |            1 | news_id          | A         |       32317 |     NULL | NULL   | YES  | BTREE      |         |               |
| post_plus |          1 | kp_votes         |            1 | kp_votes         | A         |        4616 |     NULL | NULL   | YES  | BTREE      |         |               |
| post_plus |          1 | kp_rating        |            1 | kp_rating        | A         |        5386 |     NULL | NULL   | YES  | BTREE      |         |               |
| post_plus |          1 | kp_id            |            1 | kp_id            | A         |       16158 |     NULL | NULL   | YES  | BTREE      |         |               |
| post_plus |          1 | post_uuid        |            1 | post_uuid        | A         |       32317 |     NULL | NULL   | YES  | BTREE      |         |               |
| post_plus |          1 | blockedCountries |            1 | blockedCountries | A         |          10 |     NULL | NULL   | YES  | BTREE      |         |               |
| post_plus |          1 | kp_votes_2       |            1 | kp_votes         | A         |        5386 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

describe post;

+------------------+-----------------------+------+-----+---------------------+----------------+
| Field            | Type                  | Null | Key | Default             | Extra          |
+------------------+-----------------------+------+-----+---------------------+----------------+
| id               | int(11)               | NO   | PRI | NULL                | auto_increment |
| autor            | varchar(40)           | NO   | MUL |                     |                |
| date             | datetime              | NO   | MUL | 0000-00-00 00:00:00 |                |
| full_story       | text                  | NO   | MUL | NULL                |                |
| xfields          | text                  | NO   |     | NULL                |                |
| title            | varchar(255)          | NO   | MUL |                     |                |
| descr            | varchar(200)          | NO   | MUL |                     |                |
| keywords         | text                  | NO   |     | NULL                |                |
| category         | varchar(200)          | NO   | MUL | 0                   |                |
| alt_name         | varchar(200)          | NO   | MUL |                     |                |
| comm_num         | mediumint(8) unsigned | NO   | MUL | 0                   |                |
| allow_comm       | tinyint(1)            | NO   |     | 1                   |                |
| allow_main       | tinyint(1) unsigned   | NO   | MUL | 1                   |                |
| approve          | tinyint(1)            | NO   | MUL | 0                   |                |
| fixed            | tinyint(1)            | NO   |     | 0                   |                |
| allow_br         | tinyint(1)            | NO   |     | 1                   |                |
| symbol           | varchar(3)            | NO   | MUL |                     |                |
| tags             | varchar(255)          | NO   | MUL |                     |                |
| metatitle        | varchar(255)          | NO   |     |                     |                |
| FileTempUUID     | varchar(11)           | YES  |     | NULL                |                |
| titleAlternative | varchar(255)          | YES  |     | NULL                |                |
+------------------+-----------------------+------+-----+---------------------+----------------+
21 rows in set (0.01 sec)

mysql> describe post_plus;

+------------------+--------------+------+-----+---------+----------------+
| Field            | Type         | Null | Key | Default | Extra          |
+------------------+--------------+------+-----+---------+----------------+
| pid              | int(11)      | NO   | PRI | NULL    | auto_increment |
| news_id          | int(11)      | YES  | MUL | NULL    |                |
| kp_votes         | int(11)      | YES  | MUL | NULL    |                |
| kp_rating        | decimal(5,3) | YES  | MUL | NULL    |                |
| kp_id            | varchar(100) | YES  | MUL | NULL    |                |
| pdate            | datetime     | YES  |     | NULL    |                |
| news_read        | int(11)      | NO   |     | 0       |                |
| user_id          | int(11)      | NO   | MUL | 0       |                |
| allow_rate       | tinyint(1)   | NO   |     | 1       |                |
| rating           | mediumint(8) | NO   |     | 0       |                |
| vote_num         | mediumint(8) | NO   |     | 0       |                |
| votes            | tinyint(1)   | NO   |     | 0       |                |
| editdate         | int(11)      | YES  |     | NULL    |                |
| view_edit        | tinyint(1)   | NO   |     | 0       |                |
| editor           | varchar(40)  | NO   |     |         |                |
| reason           | varchar(255) | NO   |     |         |                |
| access           | varchar(150) | NO   |     |         |                |
| cover            | text         | YES  |     | NULL    |                |
| quality          | varchar(100) | YES  |     | NULL    |                |
| post_uuid        | varchar(11)  | YES  | MUL | NULL    |                |
| encoded          | int(1)       | YES  |     | NULL    |                |
| embed_views      | int(11)      | NO   |     | 0       |                |
| blockedCountries | varchar(128) | YES  | MUL | NULL    |                |
+------------------+--------------+------+-----+---------+----------------+
25 rows in set (0.00 sec)

I also tried using STRAIGHT_JOIN

explain SELECT * FROM post STRAIGHT_JOIN post_plus ON post_plus.news_id = post.id WHERE category in(130,3,4,5,6,7,8,9,10,11,                                                                                                                                            C, post_plus.kp_rating DESC LIMIT 10;
+----+-------------+---------------+------+-----------------------------------------------+------------+---------+--------------------+-------+----------------------------------------------+
| id | select_type | table         | type | possible_keys                                 | key        | key_len | ref                | rows  | Extra                                        |
+----+-------------+---------------+------+-----------------------------------------------+------------+---------+--------------------+-------+----------------------------------------------+
|  1 | SIMPLE      | post      | ref  | PRIMARY,category,allow_main,approve,approve_2 | allow_main | 1       | const              | 13521 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | post_plus | ref  | news_id,top_order                             | news_id    | 5       | online.post.id |     1 | NULL                                         |
+----+-------------+---------------+------+-----------------------------------------------+------------+---------+--------------------+-------+----------------------------------------------+

EDIT

I think I'm hitting the following limit:

If there is an ORDER BY clause and a different GROUP BY clause, or if the ORDER BY or GROUP BY contains columns from tables other than the first table in the join queue, a temporary table is created.

https://dev.mysql.com/doc/refman/5.0/en/where-optimizations.html

But what are my alternatives than?

2

It can't use two indexes on the same table, some DBMS can in some circumstances but I'm pretty sure mySQL never does (and I don't think any could in this case).

It is using the index on news_id to perform the join so can't use indexes on kp_votes and kp_rating to perform the ORDER BY and the LIMIT.

An index covering news_id, kp_votes DESC, kp_rating DESC may help it, though mySQL currently ignors the DESC hint on indexes (see https://stackoverflow.com/questions/10109108/how-do-i-create-a-desc-index-in-mysql) so maybe not, so test and see.

  • Never say never :) – ypercubeᵀᴹ Feb 20 '14 at 11:59
  • Well, "never does currently"! They may improve the query planner/running in later versions so it can make use of multiple indexes in certain cases like other DBs sometimes can. – David Spillett Feb 20 '14 at 12:02
  • Index merge – ypercubeᵀᴹ Feb 20 '14 at 12:08
  • So why everybody suggest using multiple tables? this limitation is a big deal as far as I'm concerned, If I knew I would have created one big table. – Orlo Feb 20 '14 at 13:26
  • @ypercube: it looks like my knowledge of mysql's internals is somewhat out-of-date in that area! The index covering all the properties being filtered and ordered over would still be what I'd try to optimise the situation. – David Spillett Feb 20 '14 at 14:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.