When should I use a composite index?

composite-index, indexing, mysql

Solution

You should use a composite index when you are using queries that benefit from it. A composite index that looks like this:

index( column_A, column_B, column_C )

will benefit a query that uses those fields for joining, filtering, and sometimes selecting. It will also benefit queries that use left-most subsets of columns in that composite. So the above index will also satisfy queries that need

index( column_A, column_B, column_C )
index( column_A, column_B )
index( column_A )

But it will not (at least not directly, maybe it can help partially if there are no better indices) help for queries that need

index( column_A, column_C )

Notice how column_B is missing.

In your original example, a composite index for two dimensions will mostly benefit queries that query on both dimensions or the leftmost dimension by itself, but not the rightmost dimension by itself. If you're always querying two dimensions, a composite index is the way to go, doesn't really matter which is first (most probably).

Problem

- When should I use a composite index in a database? - What are the performance ramification by using a composite index)? - Why should I use use a composite index? For example, I have a `homes` table: ``` 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 ; ``` Does it make sense for me to use a composite index for both `geolat` and `geolng`, such that: I replace: ``` KEY `geolat` (`geolat`), KEY `geolng` (`geolng`), ``` with: ``` KEY `geolat_geolng` (`geolat`, `geolng`) ``` If so: - Why? - What is the performance ramification by using a composite index)? UPDATE: Since many people have stated it entirely dependent upon the queries I perform, below is the most common query performed: ``` SELECT * FROM homes WHERE geolat BETWEEN ??? AND ??? AND geolng BETWEEN ??? AND ??? ``` UPDATE 2: With the following database schema: ``` 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 ; ``` Using the following 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 returns: ``` 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 ``` I don't quite understand how to read the EXPLAIN command. Does this look good or bad. Right now, I am NOT using a composite index for geolat and geolng. Should I be?

Original source