MySQL Forums
Forum List  »  Performance

Re: Adding keyword search (fulltext) to query
Posted by: Rick James
Date: November 01, 2012 12:32AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Adding keyword search (fulltext) to query
812
November 01, 2012 12:32AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.