When Should You Create a Composite Index for Optimal Database Performance?

When Should You Use a Composite Index?

A composite index is a type of index that includes more than one column. This is useful when queries frequently involve multiple columns from the same table. By leveraging a composite index, the database can retrieve data more efficiently.

Performance Ramifications of Using a Composite Index

Utilizing a composite index typically enhances query performance. However, using an excessive number of composite indices can impact performance negatively due to increased index size and maintenance overhead.

Reasons to Use a Composite Index

Consider creating a composite index to:

• Handle queries involving multiple columns from the same table
• Speed up queries that use left-most subsets of the composite index columns

Example: Homes Database

In your example, a composite index on geolat and geolng would benefit queries filtering based on these coordinates, such as:

SELECT * FROM homes
WHERE geolat BETWEEN ??? AND ???
AND geolng BETWEEN ??? AND ???

Updated Query Analysis

Based on your provided schema, the following query:

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

is using the display_status index and not the geolat or geolng index. A composite index on geolat and geolng could potentially improve query performance in this scenario.

The above is the detailed content of When Should You Create a Composite Index for Optimal Database Performance?. For more information, please follow other related articles on the PHP Chinese website!