How to select the sequence of columns in a composite index when three fields need to be included?

When creating a composite index with three columns, it is important to choose the order of the fields correctly, as this affects query efficiency and the ability to use the index.

Main recommendations:

• Place the column with the highest selectivity (i.e., the one that filters data best) at the beginning of the index.
• Next come columns with lower selectivity.
• If there are fields frequently used in WHERE or JOIN conditions, they should be placed closer to the beginning.

For example, if you have a table with columns country, city, and street, and queries most often filter by country and city, then the index will be:

CREATE INDEX idx_location ON table_name (country, city, street);

This order will allow the index to be used efficiently for queries filtering by country and city, as well as all three fields.

If filtering is more often done by city, and country is less important, the order should be changed.