I have the following query that, despite its complexity, performs well in the following shape:
SELECT
`listings`.*,
`auth_agents`.*,
`symbols`.`code` AS `symbol_code`,
`symbols`.`text` AS `symbol_text`,
`symbols`.`position` AS `symbol_position`,
GROUP_CONCAT(DISTINCT IFNULL(`categories_countries`.`translation`, `c`.`name`) ORDER BY CASE WHEN `c`.`category_id` = 106 THEN 1 ELSE `c`.`category_id` END) as `category_names`,
`pv_1`.`value` AS `transaction_type_name`,
`pv_2`.`value` AS `property_type_name`,
`listings_statuses`.`name` AS `status`,
CONCAT(`pv_3`.`value`, 'property/', `listings`.`slug`, '/', `listings`.`listing_id`) AS `url`,
price_range(`listings`.`price_sales_min`, `listings`.`price_sales`, `symbols`.`code`, `symbols`.`position`, `listings`.`listings_price_qualifier_id`) AS `price_sales_range`,
price_range(`listings`.`price_lettings_min`, `listings`.`price_lettings`, `symbols`.`code`, `symbols`.`position`, `listings`.`listings_price_qualifier_id`) AS `price_lettings_range`,
summary(`listings`.`description`, `listings`.`summary`) AS `summary`,
short_title(`categories`.`name`, `categories`.`title`, `pv_1`.`value`, `listings`.`city`, `listings`.`region`, `listings`.`area`) AS `short_title`,
IF(`listings`.`listings_price_qualifier_id` = 2, TRUE, FALSE) AS `POA`,
IF((SELECT `listings_categories`.`listing_id` FROM `listings_categories` WHERE `listings_categories`.`listing_id` = `listings`.`listing_id` AND `listings_categories`.`category_id` = 124 LIMIT 1) IS NOT NULL, TRUE, FALSE) AS `serviced_office`,
GROUP_CONCAT(DISTINCT CONCAT_WS('|', `auth_agents_staff`.`auth_agents_staff_id`, `auth_staff`.`f_name`, `auth_staff`.`l_name`, `auth_staff`.`photo`)) AS `agents_staff`,
(SELECT CONCAT('http://ift.tt/1GIT2RV', `listings`.`auth_agent_id`, '/', `listings_media`.`filename`) FROM `listings_media` WHERE `listings_media`.`listing_id` = `listings`.`listing_id` ORDER BY `default` DESC LIMIT 1) AS `image`,
(SELECT COUNT(`listings_media`.`listings_media_id`) FROM `listings_media` WHERE `listings_media`.`listing_id` = `listings`.`listing_id` AND `listings_media`.`listings_media_type_id` = 1) AS `image_count`,
(SELECT COUNT(`listings_media`.`listings_media_id`) FROM `listings_media` WHERE `listings_media`.`listing_id` = `listings`.`listing_id` AND `listings_media`.`listings_media_type_id` = 3) AS `pdf_count`,
(SELECT COUNT(`listings_media`.`listings_media_id`) FROM `listings_media` WHERE `listings_media`.`listing_id` = `listings`.`listing_id` AND `listings_media`.`listings_media_type_id` = 4) AS `floor_plan_count`,
distance(`listings`.`lat`, `listings`.`lng`, 51.5073500, -0.1277600) AS `distance`
FROM `listings`
JOIN `listings_coords` ON `listings_coords`.`listing_id` = `listings`.`listing_id`
JOIN `auth_agents` ON `listings`.`auth_agent_id` = `auth_agents`.`auth_agent_id`
JOIN `branches` USING (`branch_id`)
LEFT JOIN `listings_auth_agents_staff` ON `listings_auth_agents_staff`.`listing_id` = `listings`.`listing_id`
LEFT JOIN `auth_agents_staff` ON `listings_auth_agents_staff`.`listing_id` IS NOT NULL AND `listings_auth_agents_staff`.`auth_agents_staff_id` = `auth_agents_staff`.`auth_agents_staff_id`
LEFT JOIN `auth` AS `auth_staff` ON `listings_auth_agents_staff`.`listing_id` IS NOT NULL AND `auth_agents_staff`.`auth_agents_staff_id` IS NOT NULL AND
`auth_staff`.`auth_id` = `auth_agents_staff`.`auth_id`
JOIN `parameters_values` AS `pv_1`
ON `pv_1`.`parameter_id` = 2 AND `pv_1`.`country_id` = 223 AND `pv_1`.`key` = `listings`.`transaction_type`
JOIN `parameters_values` AS `pv_2`
ON `pv_2`.`parameter_id` = 1 AND `pv_2`.`country_id` = 223 AND `pv_2`.`key` = `listings`.`property_type`
JOIN `parameters_values` AS `pv_3`
ON `pv_3`.`parameter_id` = 5 AND `pv_2`.`country_id` = 223
JOIN `countries` ON `listings`.`country_id` = `countries`.`country_id`
JOIN `countries_symbols` ON `countries`.`country_id` = `countries_symbols`.`country_id`
JOIN `symbols` ON `countries_symbols`.`symbol_id` = `symbols`.`symbol_id`
JOIN `listings_units` ON `listings`.`listings_unit_id` = `listings_units`.`listings_unit_id`
JOIN `listings_units` AS `user_units` ON `user_units`.`listings_unit_id` = 2
JOIN `listings_statuses` ON `listings`.`listings_status_id` = `listings_statuses`.`listings_status_id`
LEFT JOIN `listings_statuses_countries` ON `listings_statuses`.`listings_status_id` = `listings_statuses_countries`.`listings_status_id`
AND `listings_statuses_countries`.`country_id` = '223'
JOIN `listings_categories` ON `listings_categories`.`listing_id` = `listings`.`listing_id`
JOIN `categories` ON `categories`.`category_id` = `listings_categories`.`category_id`
JOIN `listings_categories` AS `lc` ON `lc`.`listing_id` = `listings`.`listing_id`
JOIN `categories` AS `c` ON `c`.`category_id` = `lc`.`category_id`
LEFT JOIN `categories_countries` ON `c`.`category_id` = `categories_countries`.`category_id` AND `categories_countries`.`country_id` = '223'
LEFT JOIN `listings_lettings`
ON (`listings`.`transaction_type` = 2 OR `listings`.`transaction_type` = 3) AND `listings_lettings`.`listing_id` = `listings`.`listing_id`
LEFT JOIN `listings_lettings_rent_frequencies`
ON (`listings`.`transaction_type` = 2 OR `listings`.`transaction_type` = 3) AND `listings_lettings_rent_frequencies`.`listings_lettings_rent_frequency_id` = `listings_lettings`.`listings_lettings_rent_frequency_id`
LEFT JOIN `listings_lettings_rent_frequencies` AS `llrf` ON `llrf`.`listings_lettings_rent_frequency_id` = 2
WHERE `listings`.`active` = 1 AND `listings`.`published_flag` = 1 AND
`listings`.`listings_status_id` = 1 AND
`listings`.`lat` BETWEEN 51.333436956522 AND 51.681263043478 AND
`listings`.`lng` BETWEEN -0.40717687879372 AND 0.15165687879372 AND
st_within(`listings_coords`.`coords`, @poly) AND
IF (`listings`.`transaction_type` = 2 || `listings`.`transaction_type` = 3,
`listings`.`price_lettings` * `listings_lettings_rent_frequencies`.`months` >= `llrf`.`months` * 500,
TRUE
) AND
IF(`listings_units`.`meters` IS NULL, TRUE, `listings`.`max_size` * `listings_units`.`meters` >= 100 * `user_units`.`meters`) AND
`listings`.`country_id` = 223
GROUP BY `listings`.`listing_id`
ORDER BY `listings`.`created_at`
LIMIT 10;
As you can see, the first query grabs the polygon which then is used in polygon search through spatial function. The query executes in aprox 0.06sec.
However adding any ORDER BY clause results in a drastic performance drop and ends up executing in 17 seconds.
Now - I've done some research and I know this might be to do with either clustered indexes or just adding the right indexing in general, but after hours spent trying to either return just the ID's and then run another query to get the data or simply getting rid of the GROUP BY and removing the joins (which seemed to have worked at first but then another performance issues appeared when I joined a few tables to do a price search) - I gave up.
I tried to run a query without grouping and then group the results, like:
SELECT * FROM ( *the query* ) AS results GROUP BY listing_id
But I got pretty much the same results.
Is there a way to optimise the query so I could do both grouping and sorting?
Here is the EXPLAIN:
| 1 | PRIMARY | countries | const | PRIMARY | PRIMARY | 4 | const | 1 | Using index; Using temporary; Using filesort |
| 1 | PRIMARY | user_units | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL |
| 1 | PRIMARY | listings_statuses | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL |
| 1 | PRIMARY | listings_statuses_countries | const | PRIMARY,country_id | PRIMARY | 8 | const,const | 1 | Using index |
| 1 | PRIMARY | countries_symbols | ref | PRIMARY,symbol_id | PRIMARY | 4 | const | 1 | Using index |
| 1 | PRIMARY | symbols | eq_ref | PRIMARY | PRIMARY | 4 | cp_staging.countries_symbols.symbol_id | 1 | NULL |
| 1 | PRIMARY | pv_2 | ref | parameter_id,country_id | parameter_id | 8 | const,const | 2 | NULL |
| 1 | PRIMARY | pv_1 | ref | parameter_id,country_id | parameter_id | 8 | const,const | 3 | NULL |
| 1 | PRIMARY | pv_3 | ref | parameter_id | parameter_id | 4 | const | 4 | NULL |
| 1 | PRIMARY | listings | range | SEE BELOW | compound | 20 | NULL | 2594 | Using index condition; Using where; Using join buffer (Block Nested Loop) |
| 1 | PRIMARY | listings_units | eq_ref | PRIMARY | PRIMARY | 4 | cp_staging.listings.listings_unit_id | 1 | Using where |
| 1 | PRIMARY | listings_coords | eq_ref | PRIMARY,coords | PRIMARY | 4 | cp_staging.listings.listing_id | 1 | Using where |
| 1 | PRIMARY | auth_agents | eq_ref | PRIMARY | PRIMARY | 4 | cp_staging.listings.auth_agent_id | 1 | NULL |
| 1 | PRIMARY | branches | eq_ref | PRIMARY | PRIMARY | 4 | cp_staging.listings.branch_id | 1 | Using index |
| 1 | PRIMARY | listings_categories | ref | PRIMARY,listings_categories_category_id_foreign | PRIMARY | 4 | cp_staging.listings.listing_id | 1 | Using index |
| 1 | PRIMARY | lc | ref | PRIMARY,listings_categories_category_id_foreign | PRIMARY | 4 | cp_staging.listings.listing_id | 1 | Using index |
| 1 | PRIMARY | listings_auth_agents_staff | ref | PRIMARY | PRIMARY | 4 | cp_staging.listings.listing_id | 1 | Using index |
| 1 | PRIMARY | categories | eq_ref | PRIMARY | PRIMARY | 4 | cp_staging.listings_categories.category_id | 1 | NULL |
| 1 | PRIMARY | c | eq_ref | PRIMARY | PRIMARY | 4 | cp_staging.lc.category_id | 1 | NULL |
| 1 | PRIMARY | auth_agents_staff | eq_ref | PRIMARY | PRIMARY | 4 | cp_staging.listings_auth_agents_staff.auth_agents_staff_id | 1 | Using where |
| 1 | PRIMARY | auth_staff | eq_ref | PRIMARY | PRIMARY | 4 | cp_staging.auth_agents_staff.auth_id | 1 | Using where |
| 1 | PRIMARY | categories_countries | eq_ref | PRIMARY,categories_countries_country_id_foreign | PRIMARY | 8 | cp_staging.lc.category_id,const | 1 | NULL |
| 1 | PRIMARY | listings_lettings | ref | listings_lettings_listing_id_foreign | listings_lettings_listing_id_foreign | 4 | cp_staging.listings.listing_id | 1 | Using where |
| 1 | PRIMARY | listings_lettings_rent_frequencies | eq_ref | PRIMARY,listings_lettings_rent_frequencies_id_months | PRIMARY | 4 | cp_staging.listings_lettings.listings_lettings_rent_frequency_id | 1 | Using where |
| 1 | PRIMARY | llrf | const | PRIMARY,listings_lettings_rent_frequencies_id_months | PRIMARY | 4 | const | 1 | Using where |
| 5 | DEPENDENT SUBQUERY | listings_media | ref | listings_media_listing_id_foreign,listings_media_listings_media_type_id_foreign | listings_media_listing_id_foreign | 4 | func | 1 | Using where |
| 4 | DEPENDENT SUBQUERY | listings_media | ref | listings_media_listing_id_foreign,listings_media_listings_media_type_id_foreign | listings_media_listing_id_foreign | 4 | func | 1 | Using where |
| 3 | DEPENDENT SUBQUERY | listings_media | ref | listings_media_listing_id_foreign,listings_media_listings_media_type_id_foreign | listings_media_listing_id_foreign | 4 | func | 1 | Using where |
| 2 | DEPENDENT SUBQUERY | listings_categories | eq_ref | PRIMARY,listings_categories_category_id_foreign | PRIMARY | 8 | func,const | 1 | Using index |
5th column value for listings table (pulled out so that format of above table could be preserved) PRIMARY,listings_former_id_unique,listings_price_sales_index,listings_price_sales_min_index,listings_price_lettings_index,listings_price_lettings_min_index,listings_country_id_foreign,listings_agent_id_foreign,listings_listings_status_id_foreign,listings_listings_price_qualifier_id_foreign,listings_listings_unit_id_foreign,listings_listings_tenure_type_id_foreign,agents_staff_id,listings_listings_class_id_foreign_idx,listings_listings_location_type_id_foreign_idx,listings_listings_branch_id_foreign_idx,latlng,listings_city,listings_created_at,listings_country_created_at,listings_transaction_type,listings_listing_id_transaction_type,listings_listings_coords_id,listings_created_at_listing_id,compound
Aucun commentaire:
Enregistrer un commentaire