And the EXPLAIN for the original query?
I'll guess that it starts with description_test, then moves on to listings.
With 319K rows with 'swimming pool'...
1. Look up the fields needed from description_test (title and description, id) - 319K lookups
2. JOIN to listings -- another 319K lookups
3. filter down to 549 (or fewer) rows
4. JOIN to the other tables, etc.
What we need to do is avoid the 3*319K lookups.
But, we have a potential problem -- the numbers could be reversed. That is, if the FULLTEXT lookup found only a few hundred rows, the query would be efficient, and if the filtering on `listings` was weaker (eg, not down to the city), starting with `listings` may not be the best.
There is no simple way out of this dilemma.
This will help, and be moderately robust:
Please time this:
SELECT id
FROM (
( SELECT listing_id AS id
FROM description_test
WHERE MATCH (`descriptions_test`.`description`)
AGAINST ('swimming pool' IN BOOLEAN MODE)
)
UNION
( SELECT id
FROM listings
WHERE `listings`.`category` = 1
AND `listings`.`city` = 35862
AND `listings`.`visible` = 1; ) )
GROUP BY id
HAVING COUNT(*) = 2;
If that runs fast enough, then make that a subquery in
SELECT ...
FROM ( that subquery ) x
JOIN listings ON listings.id = x.id
JOIN ...the rest of the tables...