Optimizing join query

Question

I've trouble with the following query, it became very very slow after adding ORDER BY kp_votes DESC, kp_rating DESC, the thing is I've indexes for those columns.

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                                         |
+----+-------------+---------------+------+--------------------------------------+------------+---------+--------------------+-------+----------------------------------------------+

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      |         |               |
+---------------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

another "orders" work fast... for example if I set ORDER BY fixed desc, date DESC LIMIT 30 it performs 10 times faster.

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)

EDIT*

I tried adding:

on post (approve, allow_main, category) (depending on the cardinality it can be different order) and use category IN () because that can take advantage of good indexes while regexp can't.
on post_plus (news_id, kp_votes, kp_rating) (order matters here) that will help with the sorting

as it was suggested by Károly Nagy, but it didn't help. mysql refuse using kp_votes and kp_rating indexes.

Can you provide an example of what the data looks like? And add table aliases to make it clear which columns come from which tables. — Gordon Linoff, Feb 14 '14 at 12:47
Selecting * instead of just the fields you need will always slow things down. — Dan Bracuk, Feb 14 '14 at 12:48
@Dan: of course you're right. It's always better to transfer the data you need only. But that doesn't explain the performance difference after adding "order by ...". My guess is that the result set is HUGE and it now has to be sorted before the first 30 rows are returned. So a question: how many rows are selected without the limit? — Ronald, Feb 14 '14 at 12:54

score 0 · Answer 1 · answered Feb 14 '14 at 13:13

Without seeing the actual data I can only tell the followings:

The result set is quite big and the tables lack proper indexes. As I see you have single column indexes instead of composite indexes which fits the query better. The news_id index is being used for joining the tables so even if you have indexes on the kp_* columns they cannot be used.

What you need here is two compozite index:

on post (approve, allow_main, category) (depending on the cardinality it can be different order) and use category IN () because that can take advantage of good indexes while regexp can't.
on post_plus (news_id, kp_votes, kp_rating) (order matters here) that will help with the sorting

ps.: You might need to help MySQL in which order should it do the join. See the explain after adding the indexes and if it's not looking good try STRAIGHT_JOIN (http://dev.mysql.com/doc/refman/5.5/en/join.html)

adding indexes and STRAIGHT_JOIN didn't help. it just doesn't use those indexes... — Orlo, Feb 14 '14 at 13:23
Could you please create the schema and put some test data in on sqlfiddle? (http://sqlfiddle.com) I can take a closer look then. — Károly Nagy, Feb 17 '14 at 10:02

score -1 · Answer 2 · answered Feb 14 '14 at 12:58

-1

Instead of Regexp, you can use in

WHERE category in(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)

answered Feb 14 '14 at 12:58

edmondscommerce

1,843
11
20

that's not the problem though... as I mention, without the order it performs 10 times faster. – Orlo Feb 14 '14 at 13:02

Optimizing join query

2 Answers2

Linked