MYSQL query slow on sorting

Question

I came across an issue, i have the following query:

SELECT 
  DISTINCT 
  p.id, 
  p.title, 
  p.description
FROM 
  (
    SELECT 
      `post_id`, 
      `s_type` 
    FROM 
      `search` 
    WHERE 
      MATCH(
        keyword_data_one, keyword_data_two, keyword_data_three, keyword_data_four
      ) AGAINST(
        '+prize -bell' IN BOOLEAN MODE
      )
  ) AS s 
  JOIN posts AS p ON p.id = s.post_id 
WHERE 
  s.s_type = '0' 
  ORDER BY p.id DESC
LIMIT 
  0, 20

It works but according 800.000 records on each table (posts and search) query time it's way big (results ex: 20 total, Query took 2.1060 seconds).

If i remove ORDER BY p.id DESC the query time it's way less and the query it is executed faster. (20 total, Query took 0.0016 seconds).

My question is if i can rewrite somehow this query to make it faster like this but having the sort option.

I have composite index created on search table, also indexes on the posts table.

SQL Fiddle here https://www.db-fiddle.com/f/rbbY8fBGePECuy6nuJdcus/0

Again, on fiddle example it is returning results same fast for both situation with sort or without but not with 800k records.

Answer 1

Let's start by moving WHERE s_type = '0' into the inner query. That may drastically decrease the number of rows in s, leading to all sorts of speedups.

Can there be multiple rows, hence your use of DISTINCT? Perhaps that can be eliminated or moved into the inner query?

To explain why ORDER BY makes such a difference...

1. fetch rows (in your query, it involves a subquery and a join)
2. filter on s_type
3. note that result from the above steps is being stored in a [potentially big - 180K rows!] temp table
4. sort that temp table
5. peel of the first 20.

Without the ORDER BY:

1. fetch rows (in your query, it involves a subquery and a join)
2. filter on s_type
3. STOP when 20 rows have been found.

That is, no temp table, no sort.

Imagine that you are the user. And you are paginating through 180K rows, 20 rows per page. How many pages would you get through before you threw a brick through the monitor?

You should not "paginate" through huge datasets; you should design the search mechanism some other way.