Original query... It seems that there is only 1 listing in city 35862 ? Were you getting 30 seconds for that city?
The EXPLAIN says that it picked
`regions_cities` first
apparently because of ON `listings`.`city` = `regions_cities`.`id`
AND `listings`.`city` = 35862
(The optimizer is smart enough to realize that that means that regions_cities = 35862.)
Apparently there is only one regions_cities with city=35862.
But that is mostly bogus -- the only purpose of regions_cities is to get the city name.
The main part of the query starts with the MATCH. The EXPLAIN estimated that only one row would be returned. Given that, it is an excellent choice.
Suggest trying this:
ANALYZE TABLE descriptions_test;
If the EXPLAIN still says "1" rows, then the problem is deeper.
How big is key_buffer_size? See
http://mysql.rjweb.org/doc.php/memory
for recommendation.
Would you time each of these separately:
SELECT `listing_id` AS `id`
FROM `descriptions_test`
WHERE MATCH (`descriptions_test`.`description`)
AGAINST ('swimming pool' IN BOOLEAN MODE)
SELECT `id`
FROM `listings`
WHERE `listings`.`category` = 1
AND `listings`.`city` = 35862
AND `listings`.`visible` = 1
If the MATCH still takes much too long, we need to rearrange the query a different way. Now I would suggest trying
SELECT ...
FROM ( SELECT ... most of query ... FROM ... most of tables ... ) x
JOIN descriptions_test dt ON x.id = dt.id
WHERE MATCH (`descriptions_test`.`description`)
AGAINST ('swimming pool' IN BOOLEAN MODE)
The idea here is to get it to do all the non-FULLTEXT work first. (I'm not positive this will achieve it.)