 1. When should I use a composite index in a database?
 2. What are the performance ramification by using a
    composite index)?
 3. 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 &#39;0&#39;,
      `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&#39;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?

All Composite Index Solutions on Gang of Coders

Total of 1 Composite Index Solutions

When should I use a composite index?