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?
