Long queries with sorting (2-5 sec instead of 0.1-03)

Question

MySQL

Queries like this take a long time.

SELECT SQL_NO_CACHE news.*, news_category.name AS category FROM news, news_category WHERE news.news_category_id = news_category.id AND news_category.id != 114 AND create_date < CURRENT_DATE AND FIND_IN_SET(50, news.region_ids) AND news.active_status = 1 AND news_category.active_status = 1 ORDER BY id DESC LIMIT 3

And even so, adding sorting to any query leads to a disproportionate increase in its execution time.

Tables - MyISAM

What could be wrong?
Just some kind of stupor.

Settings here - www.cottage.ru/mysql-params.txt (utf-8)

Server php-fpm+nginx

Answer 1

what does EXPLAIN say?

Answer 2

Sorting is a heavy operation, what surprises you? Use inner join, try to join already filtered data like this:
SELECT SQL_NO_CACHE news.*, news_category.name AS category FROM news
INNER JOIN news_category ON (news.news_category_id = news_category.id AND news_category.id != 114 AND news_category. active_status = 1)
WHERE AND create_date < CURRENT_DATE AND FIND_IN_SET(50, news.region_ids) AND news.active_status = 1 ORDER
BY id DESC LIMIT 3

Answer 3

So that sorting does not slow down queries, it is necessary that an index be available, starting with the fields by which sorting is performed and optionally continuing with filtering fields. Do you have one?

Answer 4

Try increasing sort_buffer_size and show dev.mysql.com/doc/refman/5.6/en/show-profile.html for query

Answer 5

As a result, they solved the problem like this - they moved tmdir (temporary directory) to the RAM, and everything worked out.
Query profiling showed that most of the time is taken by Copying to tmp table