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?
Best Answer
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_idto perform the join so can't use indexes onkp_votesandkp_ratingto perform theORDER BYand theLIMIT.An index covering
news_id, kp_votes DESC, kp_rating DESCmay help it, though mySQL currently ignors theDESChint on indexes (see https://stackoverflow.com/questions/10109108/how-do-i-create-a-desc-index-in-mysql) so maybe not, so test and see.