The easiest way to understand how to apply indexing concepts is with an illustration, so we’ve prepared a case study in indexing.

Suppose we need to design an online dating site with user profiles that have many different columns, such as the user’s country, state/region, city, sex, age, eye color, and so on. The site must support searching the profiles by various combinations of these properties. It must also let the user sort and limit results by the last time the profile’s owner was online, ratings from other members, etc. How do we design indexes for such complex requirements?

Oddly enough, the first thing to decide is whether we have to use index-based sorting, or whether post-retrieval sorting is acceptable. Index-based sorting restricts how the indexes and queries need to be built. For example, we can’t use an index for a WHERE clause such as WHERE age BETWEEN 18 AND 25 if the same query uses an index to sort users by the ratings other users have given them. If MySQL uses an index for a range criterion in a query, it cannot also use another index (or a suffix of the same index) for ordering. Assuming this will be one of the most common WHERE clauses, we’ll take for granted that many queries will need a sort (i.e., a filesort).