(Thanks for providing the info! You have been reading other threads.)
Turn each of the subqueries into a JOIN. MySQL usually does not optimize subqueries well. For example,
(SELECT `name` FROM `regions_cities` WHERE `regions_cities`.`id`=`addresses`.`city`) as `city`,
-->
`regions_cities`.city,
...
JOIN regions_cities ON `regions_cities`.`id`=`addresses`.`city`
This is likely to be the best performance boost.
This should allow it start with listings (instead of addresses), should it find that to be better:
ALTER TABLE listings
DROP INDEX id_category_visible,
ADD INDEX(`category`,`visible`, id);
I'll take it back. Now that I see that you seem to have 26K listings in city #597, starting with listings may be the best bet.
NO! (I guess you missed my rants against prefix indexes)
KEY `name` (`name`(20))
--> INDEX(name)
This will speed up access into `photos` a little. (Note that the EXPLAIN estimates 10 probes.)
ALTER TABLE photos
DROP INDEX listing_id,
ADD INDEX (listing_id, is_main);
LIMIT 250
Ordered by what??
(Opinion) Because of the nested nature of country > state > city, I would normalize them all at once. That is, have only one id in `addresses`, and have the other table would have `id`, plus country, state, and city, spelled out. (Yeah, the countries and states would be repeated a lot, but the normalization won't buy you anything for this unless some country changes its name from "Upper Volta" turns into "Burkina Faso". Even then, the UPDATE to fix it would not be bad.)
"Sending Data" -- seems to mean "I'm busy, and I don't want to tell you what I am doing."
If those ideas don't work well enough, then we can talk about a subquery to get 250 ids, then go back into `listings` to get the rest of the info.