How to optimize a MySQL query using INNER JOINs and ORDER BY

Question

Given this request:

EXPLAIN SELECT mi.*, i1.sizes as tile_photo_sizes, i1.filename as tile_photo_filename, v.title_cached as venue_title, v.subtitle_cached as venue_subtitle, v.slug as venue_slug FROM menuitems mi INNER JOIN images i1 ON i1.id = mi.tile_photo_id INNER JOIN menus m ON m.id = mi.menu_id INNER JOIN venues v ON v.id = m.venue_id WHERE NOT m.is_deleted AND v.slug="teplo" AND m.is_published AND v.is_published ORDER BY mi.number ASC 

I get the following result:

I tried adding separate indexes for each column used in join , where and order by , but it still uses a temporary file.

I would not like to optimize it, but sometimes this leads to an error that can only be fixed by the hosting provider: Can't create/write to file '/mysql-temp/#sql_64e0_0.MYD' (Errcode: 17)

The largest table is the menu items (6,867), then there are images (944), menus (85), places (79).

UPD: Removing ORDER BY does not use a temporary file

Answer 1

Try changing the query to put the main JOIN logic in a subquery and see if EXPLAIN will work better:

 EXPLAIN SELECT * FROM ( SELECT mi.*, i1.sizes as tile_photo_sizes, i1.filename as tile_photo_filename, v.title_cached as venue_title, v.subtitle_cached as venue_subtitle, v.slug as venue_slug FROM menuitems mi INNER JOIN images i1 ON i1.id = mi.tile_photo_id INNER JOIN menus m ON m.id = mi.menu_id INNER JOIN venues v ON v.id = m.venue_id WHERE NOT m.is_deleted AND v.slug="teplo" AND m.is_published AND v.is_published ) as t ORDER BY t.number ASC 

This may probably lead to a different temporary situation, but I'm curious that the EXPLAIN results are for you.