1. This is the subquery:
SELECT `listings`.`id`, `listings`.`feed_id`,
`listings`.`feed_listing_id`, `listings`.`category`,
`listings`.`property_type`, `listings`.`price`,
`listings`.`listing_date`, `listings`.`detailed_view_url`,
`listings`.`virtual_tour_url`, `listings`.`square_feet`,
`listings`.`year_built`, `listings`.`bedrooms`,
`listings`.`bathrooms`, `listings`.`feature`,
`listings`.`mls_number`, `listings`.`large`,
`listings`.`neighborhood`, `listings`.`city`,
`listings`.`state`, `listings`.`country`,
`listings`.`postal_code`, `listings`.`latitude`,
`listings`.`longitude`, `listings`.`address_full`,
`listings`.`agent_img`, `listings`.`bump_up_date`,
`listings`.`num_photos`, `listings`.`urgent`,
`listings`.`price_from_to`, `listings`.`rental_period`,
`regions_cities`.`id` as `city_id`,
`regions_cities`.`name` as `city_name`,
`users`.`name`, `users`.`image`, `users`.`vanity_url`,
`users`.`about_me`, `users`.`agency_img`,
`users`.`broker`, `open_houses`.`date` as `open_house_date`,
`open_houses`.`start_time` as `open_house_start_time`,
`open_houses`.`end_time` as `open_house_end_time`,
`open_houses`.`description` as `open_house_description`
FROM `listings`
JOIN `regions_cities` ON `listings`.`city` = `regions_cities`.`id`
LEFT JOIN `users` ON `listings`.`agent_id` = `users`.`id`
LEFT JOIN `open_houses` ON `listings`.`id` = `open_houses`.`listing_id`
WHERE `listings`.`category` = 1
AND `listings`.`city` = 35862
AND `listings`.`visible` = 1
Run it to make sure it is right.
Note that it has no references to descriptions_test. They will come next...
Paste it in place of "subquery" in
SELECT x.*,
dt.`title`,
dt.`description`,
FROM
( subquery ) x
JOIN descriptions_test dt ON x.id = dt.id
WHERE MATCH (dt.`description`)
AGAINST ('swimming pool' IN BOOLEAN MODE)
(Please use short aliases for the tables.)
Most of the settings look fine, except
query_cache_size=256M --> 50M (as mentioned in my link)
table_cache=1750M NO NO You don't need to open a 1.75 _billion_ _tables_! change it to 500 (no M).
See the link for what is cached where. "table_cache" is a confusing name.