mysql - COUNT 比常规选择慢得多

Question

我有以下查询:

SET @poly = (SELECT `polygon` FROM locations WHERE location_id = 65);

SELECT 
`listings`.`listing_id`
FROM `listings`
STRAIGHT_JOIN `listings_coords` ON `listings_coords`.`listing_id` = `listings`.`listing_id`


WHERE
`listings`.`country_id` = 223
AND `listings`.`active` = 1
AND `listings`.`published_flag` = 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)

ORDER BY `created_at` 
LIMIT 10

但是 - 当我对给定查询执行 COUNT(listings.listing_id) 时，它会从 0.03 秒减慢到 4 秒!我知道 InnoDB 在计数上往往比 MyISAM 慢一点，但这太长了。解释一下计数:

+----+-------------+-----------------+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------+---------+--------------------------------+-------+-------------+
| id | select_type | table           | type   | possible_keys                                                                                                                                                    | key                         | key_len | ref                            | rows  | Extra       |
+----+-------------+-----------------+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------+---------+--------------------------------+-------+-------------+
|  1 | SIMPLE      | listings        | ref    | PRIMARY,listings_country_id_foreign,latlng,listings_country_created_at,listings_compound,listings_lat_lng_country_created_at,listings_country_id_price_qualifier | listings_country_id_foreign | 4       | const                          | 10565 | Using where |
|  1 | SIMPLE      | listings_coords | eq_ref | PRIMARY,coords                                                                                                                                                   | PRIMARY                     | 4       | cp_staging.listings.listing_id |     1 | Using where |
+----+-------------+-----------------+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------+---------+--------------------------------+-------+-------------+

有什么方法可以让我在查询执行期间不必好好休息一下就能获得该数字吗？

解释没有计数的查询:

+----+-------------+-----------------+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------+---------+--------------------------------+-------+-------------+
| id | select_type | table           | type   | possible_keys                                                                                                                                                    | key                         | key_len | ref                            | rows  | Extra       |
+----+-------------+-----------------+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------+---------+--------------------------------+-------+-------------+
|  1 | SIMPLE      | listings        | ref    | PRIMARY,listings_country_id_foreign,latlng,listings_country_created_at,listings_compound,listings_lat_lng_country_created_at,listings_country_id_price_qualifier | listings_country_created_at | 4       | const                          | 10565 | Using where |
|  1 | SIMPLE      | listings_coords | eq_ref | PRIMARY,coords                                                                                                                                                   | PRIMARY                     | 4       | cp_staging.listings.listing_id |     1 | Using where |
+----+-------------+-----------------+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------+---------+--------------------------------+-------+-------------+

显示创建表列表:

CREATE TABLE `listings` (
  `listing_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `auth_agent_id` int(10) unsigned NOT NULL,
  `country_id` int(10) unsigned NOT NULL,
  `branch_id` int(10) unsigned DEFAULT NULL,
  `property_type` tinyint(4) NOT NULL,
  `listings_status_id` int(10) unsigned NOT NULL,
  `listings_price_qualifier_id` int(10) unsigned NOT NULL,
  `listings_tenure_type_id` int(10) unsigned DEFAULT NULL,
  `listings_class_id` int(10) unsigned DEFAULT NULL,
  `listings_location_type_id` int(10) unsigned DEFAULT NULL,
  `transaction_type` enum('1','2','3') COLLATE utf8_bin NOT NULL,
  `auth_agents_staff_id` int(10) unsigned DEFAULT NULL,
  `published_flag` tinyint(1) NOT NULL,
  `address_1` char(255) COLLATE utf8_bin DEFAULT NULL,
  `address_2` char(255) COLLATE utf8_bin DEFAULT NULL,
  `area` char(150) COLLATE utf8_bin DEFAULT NULL,
  `city` char(255) COLLATE utf8_bin DEFAULT NULL,
  `region` char(255) COLLATE utf8_bin DEFAULT NULL,
  `postcode` char(255) COLLATE utf8_bin DEFAULT NULL,
  `hide_address` tinyint(1) NOT NULL DEFAULT '0',
  `street_view` tinyint(1) NOT NULL DEFAULT '1',
  `lat` decimal(10,6) NOT NULL,
  `lng` decimal(10,6) NOT NULL,
  `listings_coords_id` int(11) NOT NULL,
  `price_sales` decimal(14,2) DEFAULT NULL,
  `price_sales_min` decimal(10,2) DEFAULT NULL,
  `price_lettings` decimal(10,2) DEFAULT NULL,
  `price_lettings_min` decimal(10,2) DEFAULT NULL,
  `price_per_unit_sales` decimal(9,2) DEFAULT NULL,
  `price_per_unit_lettings` decimal(9,2) DEFAULT NULL,
  `min_size` decimal(10,2) DEFAULT NULL,
  `max_size` decimal(10,2) DEFAULT NULL,
  `listings_unit_id` int(10) unsigned NOT NULL,
  `summary` text COLLATE utf8_bin,
  `description` text COLLATE utf8_bin,
  `short_title` varchar(150) COLLATE utf8_bin DEFAULT NULL,
  `title` varchar(200) COLLATE utf8_bin DEFAULT NULL,
  `slug` varchar(200) COLLATE utf8_bin DEFAULT NULL,
  `agent_ref` char(255) COLLATE utf8_bin DEFAULT NULL,
  `active` tinyint(1) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  `former_id` int(11) DEFAULT NULL,
  `coords` point DEFAULT NULL,
  PRIMARY KEY (`listing_id`),
  UNIQUE KEY `listings_former_id_unique` (`former_id`),
  KEY `listings_price_sales_index` (`price_sales`),
  KEY `listings_price_sales_min_index` (`price_sales_min`),
  KEY `listings_price_lettings_index` (`price_lettings`),
  KEY `listings_price_lettings_min_index` (`price_lettings_min`),
  KEY `listings_country_id_foreign` (`country_id`),
  KEY `listings_agent_id_foreign` (`auth_agent_id`),
  KEY `listings_listings_status_id_foreign` (`listings_status_id`),
  KEY `listings_listings_price_qualifier_id_foreign` (`listings_price_qualifier_id`),
  KEY `listings_listings_unit_id_foreign` (`listings_unit_id`),
  KEY `listings_listings_tenure_type_id_foreign` (`listings_tenure_type_id`),
  KEY `agents_staff_id` (`auth_agents_staff_id`),
  KEY `listings_listings_class_id_foreign_idx` (`listings_class_id`),
  KEY `listings_listings_location_type_id_foreign_idx` (`listings_location_type_id`),
  KEY `listings_listings_branch_id_foreign_idx` (`branch_id`),
  KEY `latlng` (`lat`,`lng`),
  KEY `listings_city` (`city`,`created_at`),
  KEY `listings_country_created_at` (`country_id`,`created_at`),
  KEY `listings_transaction_type` (`transaction_type`),
  KEY `listings_compound` (`country_id`,`active`,`published_flag`,`created_at`),
  KEY `listings_lat_lng_country_created_at` (`lat`,`lng`,`country_id`,`created_at`),
  KEY `listings_country_id_price_qualifier` (`country_id`,`listings_price_qualifier_id`),
  KEY `country_active_published_property_type_lat_lng` (`country_id`,`active`,`published_flag`,`property_type`,`lat`,`lng`),
  KEY `country_active_published_flag_property_type_status_lat_lng` (`country_id`,`active`,`published_flag`,`property_type`,`listings_status_id`,`lat`,`lng`),
  CONSTRAINT `listings_agent_id_foreign` FOREIGN KEY (`auth_agent_id`) REFERENCES `auth_agents` (`auth_agent_id`),
  CONSTRAINT `listings_country_id_foreign` FOREIGN KEY (`country_id`) REFERENCES `countries` (`country_id`),
  CONSTRAINT `listings_ibfk_1` FOREIGN KEY (`auth_agents_staff_id`) REFERENCES `agents_staff` (`agents_staff_id`) ON DELETE SET NULL ON UPDATE SET NULL,
  CONSTRAINT `listings_listings_branch_id_foreign` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`branch_id`),
  CONSTRAINT `listings_listings_class_id_foreign` FOREIGN KEY (`listings_class_id`) REFERENCES `listings_classes` (`listings_class_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `listings_listings_location_type_id_foreign` FOREIGN KEY (`listings_location_type_id`) REFERENCES `listings_location_types` (`listings_location_type_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `listings_listings_price_qualifier_id_foreign` FOREIGN KEY (`listings_price_qualifier_id`) REFERENCES `listings_price_qualifiers` (`listings_price_qualifier_id`),
  CONSTRAINT `listings_listings_status_id_foreign` FOREIGN KEY (`listings_status_id`) REFERENCES `listings_statuses` (`listings_status_id`),
  CONSTRAINT `listings_listings_tenure_type_id_foreign` FOREIGN KEY (`listings_tenure_type_id`) REFERENCES `listings_tenure_types` (`listings_tenure_type_id`),
  CONSTRAINT `listings_listings_unit_id_foreign` FOREIGN KEY (`listings_unit_id`) REFERENCES `listings_units` (`listings_unit_id`)
) ENGINE=InnoDB AUTO_INCREMENT=34769 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

Answer 1

这行是罪魁祸首:

LIMIT 10

这就是为什么您的选择如此之快。

请删除此行并重试。我认为 count 版本现在更快。