Thanks James.
I tried the suggested query but I ended up having to kill it as it had surpassed 300 seconds.
SELECT x.*,
dt.`title`,
dt.`description`
FROM (
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
) x
JOIN descriptions_test dt ON x.id = dt.id
WHERE MATCH (dt.`description`)
AGAINST ('swimming pool' IN BOOLEAN MODE)
To take this issue back to the basics, does it even make sense for `descriptions` to be in its own table? I originally put it in its own table to help reduce the overall size of the `listings` table since the majority of the listing descriptions can be quite large.
Would it run faster/help the cause if the descriptions were in the `listings` table?