How to optimize a query with 2 JOINs and zero values ​​on the second JOIN?

Question

MySQL

for example , the requests table
:
- id
- user_id the

requests_cities table:
- id
- request_id
- city_id

of the cities table:
- id
- title

is valid in the requests table 100 000 records, and in the requests_cities table there are only 5000 records related to requests

example query:

SELECT 
 requests.id
 requests.user_id
 cities.title
LEFT JOIN requests_cities ON requests_cities.request_id = requests.id
LEFT JOIN cities ON cities.id = requests_cities.city_id

at the same time, the query starts to slow down a lot
if you leave the joins, but remove cities.title in the select, then it works out quickly
in the tables there are associated keys on the join fields, there are no problems with indexes

Answer 1

First option. You need to first connect the requests_cities and cities tables, put the result in a temporary table.
The second option is to use an inner join instead of a left join.