0

I have a joined table (News, Publishers) with indexing. The query is working fine on MySQL 5.5. After I upgrading one of the server to MySQL 5.7, I start noticing high load, high CPU, and slow query. A query taking almost 0.00 seconds (5.5) took 2 to 5 seconds in MySQL 5.7

Query:

SELECT   news.id FROM news ,publishers
WHERE    news.publisher_id=publishers.id
AND      publishers.language='en'
ORDER BY date_added DESC LIMIT 10;

I tried to figure what happen with EXPLAIN, and here is my finding:

MySQL 5.5
+----+-------------+------------+--------+------------------+----------------+---------+---------------------------------+------+-------------+
| id | select_type | table      | type   | possible_keys    | key            | key_len | ref                             | rows | Extra       |
+----+-------------+------------+--------+------------------+----------------+---------+---------------------------------+------+-------------+
|  1 | SIMPLE      | news       | index  | idx_publisher_id | idx_date_added | 9       | NULL                            |   10 |             |
|  1 | SIMPLE      | publishers | eq_ref | PRIMARY          | PRIMARY        | 8       | klsescre_klse.news.publisher_id |    1 | Using where |
+----+-------------+------------+--------+------------------+----------------+---------+---------------------------------+------+-------------+

MySQL 5.7
+----+-------------+------------+------------+-------+------------------+------------------+---------+-----------------------------+------+----------+-----------------------------------------------------------+
| id | select_type | table      | partitions | type  | possible_keys    | key              | key_len | ref                         | rows | filtered | Extra                                                     |
+----+-------------+------------+------------+-------+------------------+------------------+---------+-----------------------------+------+----------+-----------------------------------------------------------+
|  1 | SIMPLE      | publishers | NULL       | index | PRIMARY          | NULL             | 277     | NULL                        |   47 |    10.00 | Using where; Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | news       | NULL       | ref   | idx_publisher_id | idx_publisher_id | 8       | klsescre_klse.publishers.id | 4962 |   100.00 | NULL                                                      |
+----+-------------+------------+------------+-------+------------------+------------------+---------+-----------------------------+------+----------+-----------------------------------------------------------+

My guess is in 5.7, MySQL scan the PUBLISHERS table before NEWS, thus not making use of INDEX of I created for NEWS, making the query much slower.

Can anyone help me with this? How can I make MySQL 5.7 scan the table like 5.5 ?

0

Damned if you do, damned if you don't. The Optimizer has two choices -- filter on language in one table first, or scan on date in the other table first.

If the recent articles are in English, then using the date is better. If you ask for some language with few articles, the other index is better.

Meanwhile, the Optimizer does not have the smarts to look in two different directions at once to decide which is better.

I would argue that the schema is not 'correct'. Can't a publisher provide news in multiple languages? If so, language should be in news, not in publishers. If you make than change, then INDEX(language, date_added) would become an better index.

I have addressed the general "news" list problem with the addition and maintenance of a separate table.

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.