什么时候应该使用复合索引？

什么时候应该在数据库中使用复合索引？ 使用综合索引对性能有何影响？ 为什么要使用复合索引？ 例如，我有一张homes桌子：

CREATE TABLE IF NOT EXISTS homes ( home_id int(10) unsigned NOT NULL auto_increment, sqft smallint(5) unsigned NOT NULL, year_built smallint(5) unsigned NOT NULL, geolat decimal(10,6) default NULL, geolng decimal(10,6) default NULL, PRIMARY KEY (home_id), KEY geolat (geolat), KEY geolng (geolng), ) ENGINE=InnoDB ; 对geolat和都使用复合索引对我来说是否有意义geolng：

我取代：

KEY geolat (geolat), KEY geolng (geolng), 与：

KEY geolat_geolng (geolat, geolng) 如果是这样的话：

为什么？ 使用复合索引对性能有何影响？ 更新：

由于许多人说它完全取决于我执行的查询，因此以下是执行的最常见的查询：

SELECT * FROM homes WHERE geolat BETWEEN ??? AND ??? AND geolng BETWEEN ??? AND ??? 更新2：

使用以下数据库架构：

CREATE TABLE IF NOT EXISTS homes ( home_id int(10) unsigned NOT NULL auto_increment, primary_photo_group_id int(10) unsigned NOT NULL default '0', customer_id bigint(20) unsigned NOT NULL, account_type_id int(11) NOT NULL, address varchar(128) collate utf8_unicode_ci NOT NULL, city varchar(64) collate utf8_unicode_ci NOT NULL, state varchar(2) collate utf8_unicode_ci NOT NULL, zip mediumint(8) unsigned NOT NULL, price mediumint(8) unsigned NOT NULL, sqft smallint(5) unsigned NOT NULL, year_built smallint(5) unsigned NOT NULL, num_of_beds tinyint(3) unsigned NOT NULL, num_of_baths decimal(3,1) unsigned NOT NULL, num_of_floors tinyint(3) unsigned NOT NULL, description text collate utf8_unicode_ci, geolat decimal(10,6) default NULL, geolng decimal(10,6) default NULL, display_status tinyint(1) NOT NULL, date_listed timestamp NOT NULL default CURRENT_TIMESTAMP, contact_email varchar(100) collate utf8_unicode_ci NOT NULL, contact_phone_number varchar(15) collate utf8_unicode_ci NOT NULL, PRIMARY KEY (home_id), KEY customer_id (customer_id), KEY city (city), KEY num_of_beds (num_of_beds), KEY num_of_baths (num_of_baths), KEY geolat (geolat), KEY geolng (geolng), KEY account_type_id (account_type_id), KEY display_status (display_status), KEY sqft (sqft), KEY price (price), KEY primary_photo_group_id (primary_photo_group_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=8 ; 使用以下SQL：

EXPLAIN SELECT homes.home_id, address, city, state, zip, price, sqft, year_built, account_type_id, num_of_beds, num_of_baths, geolat, geolng, photo_id, photo_url_dir FROM homes LEFT OUTER JOIN home_photos ON homes.home_id = home_photos.home_id AND homes.primary_photo_group_id = home_photos.home_photo_group_id AND home_photos.home_photo_type_id = 2 WHERE homes.display_status = true AND homes.geolat BETWEEN -100 AND 100 AND homes.geolng BETWEEN -100 AND 100 EXPLAIN返回：

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE homes ref geolat,geolng,display_status display_status 1 const 2 Using where 1 SIMPLE home_photos ref home_id,home_photo_type_id,home_photo_group_id home_photo_group_id 4 homes.primary_photo_group_id 4
我不太了解如何阅读EXPLAIN命令。这看起来好还是坏。现在，我没有为geolat和geolng使用复合索引。我可以做？