Geek Answers Handbook

A query with LEFT JOIN and ORDER BY ... LIMIT slow, uses Filesort

I have the following query:

SELECT 
    fruit.date,
    fruit.name,
    fruit.reason,
    fruit.id,
    fruit.notes,
    food.name
FROM
    fruit
 LEFT JOIN
    food_fruits AS ff ON fruit.fruit_id = ff.fruit_id AND ff.type='fruit'
 LEFT JOIN
    food USING (food_id)
 LEFT JOIN
    fruits_sour AS fs ON fruits.id = fs.fruit_id
WHERE
    (fruit.date < DATE_SUB(NOW(), INTERVAL 180 DAY))
        AND (fruit.`status` = 'Rotten')
        AND (fruit.location = 'USA')
        AND (fruit.size = 'medium')
        AND (fs.fruit_id IS NULL)
ORDER BY `food.name` asc
LIMIT 15 OFFSET 0

And all the indexes you will ever want, including the following which are used:

fruit        - fruit_filter (size, status, location, date)
food_fruits  - food_type (type)
food         - food (id)
fruits_sour  - fruit_id (fruit_id)

I even have indexes that I thought would work better that aren't used:

food_fruits  - fruit_key (fruit_id, type)
food         - id_name (food_id, name)

The proposal ORDER BY, unfortunately, causes the table temporaryand filesort. Without this, the request is executed by lickety-split. How can I make this request not need filesort? What am I missing?

EDIT:

Explanation: The explain

+4
source share
2 answers

ORDER BY, , , . fruit_filter, filesort ( " ", ).

, , , .

(, , LEFT JOIN INNER JOIN food_fruits - )

UPDATE.

, (), :

SELECT
    fr.date,
    fr.name,
    fr.reason,
    fr.id,
    fr.notes,
    food.name
FROM
  (
  SELECT 
    fruit.date,
    fruit.name,
    fruit.reason,
    fruit.id,
    fruit.notes,
  FROM
    fruit
  LEFT JOIN
    fruits_sour AS fs ON fruit.id = fs.fruit_id
  WHERE
    (fruit.date < DATE_SUB(NOW(), INTERVAL 180 DAY))
        AND (fruit.`status` = 'Rotten')
        AND (fruit.location = 'USA')
        AND (fruit.size = 'medium')
        AND (fs.fruit_id IS NULL)
  ) as fr
LEFT JOIN
    food_fruits AS ff ON fr.fruit_id = ff.fruit_id AND ff.type='fruit'
LEFT JOIN
    food USING (food_id)  
ORDER BY `food.name` asc
LIMIT 15 OFFSET 0
+1

ORDER BY ... LIMIT , . ORDER BY ... LIMIT , . .

     SELECT fruit.id,
            food.name
       FROM fruit
  LEFT JOIN food_fruits AS ff   ON fruit.fruit_id = ff.fruit_id 
                               AND ff.type='fruit'
  LEFT JOIN food USING (food_id)
  LEFT JOIN fruits_sour AS fs ON fruits.id = fs.fruit_id
      WHERE fruit.date < DATE_SUB(NOW(), INTERVAL 180 DAY)
        AND fruit.`status` = 'Rotten'
        AND fruit.location = 'USA'
        AND fruit.size = 'medium'
        AND fs.fruit_id IS NULL
   ORDER BY food.name ASC
      LIMIT 15 OFFSET 0

.

id fruit_filter, (size, status, location, date, id). .

, , , LEFT JOIN ... IS NULL join-fail, .

, .

, .

SELECT fruit.date,
       fruit.name,
       fruit.reason,
       fruit.id,
       fruit.notes,
       list.name
  FROM fruit
  JOIN (
               SELECT fruit.id,
                      food.name
                 FROM fruit
            LEFT JOIN food_fruits AS ff    ON fruit.fruit_id = ff.fruit_id
                                          AND ff.type='fruit'
            LEFT JOIN food USING (food_id)
            LEFT JOIN fruits_sour AS fs ON fruits.id = fs.fruit_id
                WHERE fruit.date < DATE_SUB(NOW(), INTERVAL 180 DAY)
                  AND fruit.`status` = 'Rotten'
                  AND fruit.location = 'USA'
                  AND fruit.size = 'medium'
                  AND fs.fruit_id IS NULL
             ORDER BY food.name ASC
                LIMIT 15 OFFSET 0
       ) AS list ON fruit.id = list.id
 ORDER BY list.name

, ? , , . , . , , , MySQL , , /. ( EXPLAIN, .)

+1

More articles:


All Articles