indexing multiple columns in a table in MySQL

Question

I am greatly confused about creating an index for a table.

Assuming that a table users have 5 columns, id (primary key), name, email, creation date, and update date.

id | name | email | creation_date | update_date

The initial question is about creating an index for all columns (a multi-column index). Is it generally a good idea? (obviously for a table with a small number of columns)

I know that you probably ask it depends on the query but let's assume the queries below:

1. SELECT * FROM users where creation_date < 'A DATE'
2. SELECT id, email from users where name = 'SOME NAMES';
3. SELECT id, email from users where name = 'SOME NAMES' ORDER BY creation_date DESC;
4. SELECT id, email from users where name = 'SOME NAMES' AND creation_date >= 'SOME DATES' ORDER BY updated_date ASC;

Something that I am intending to avoid is creating several indexes for every query above. So can we create a multicolumn index consisting of all columns to cover all queries above?

The next question with scattered information in the net is about the order of columns in an index. Is this the same as the parameters coming after WHERE clause? How about ORDER BY? Do we need to consider that in a multicolumn index?

Answer 1

The following answer is for MySQL.

The initial question is about creating an index for all columns (a multi-column index). Is it generally a good idea? (obviously for a table with a small number of columns)

No, it isn't.

If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to look up rows.

For example, if you have a three-column index on (name,email,creation_date), you have indexed search capabilities on (name), (name, email), and (name,email,creation_date).

Multiple-Column Indexes

Something that I am intending to avoid is creating several indexes for every query above. So can we create a multicolumn index consisting of all columns to cover all queries above?

No single multicolumn index can cover all the above queries .

The next question with scattered information in the net is about the order of columns in an index. Is this the same as the parameters coming after WHERE clause?

Yes. For example if you have where name = 'SOME NAMES' AND creation_date >= 'SOME DATES' ORDER BY updated_date ASC

The index should be (name ,creation_date ,updated_date) which cover the last part of your next question as well.

Answer 2

1. SELECT * FROM users where creation_date < 'A DATE'

    INDEX(creation_date)
   

2. SELECT id, email from users where name = 'SOME NAMES';

    INDEX(name) -- or, to be slightly faster:
    INDEX(name, email, id)  -- This is "covering"
   

3. SELECT id, email from users where name = 'SOME NAMES' ORDER BY creation_date DESC;

    INDEX(name, creation_date)
   

4. SELECT id, email from users where name = 'SOME NAMES' AND creation_date >= 'SOME DATES' ORDER BY updated_date ASC;

    INDEX(name, creation_date)
   

   Because a range is being used in the WHERE, there is no advantage to adding on updated_date.

Summary: If you have all 4 queries, I recommend having these two:

INDEX(creation_date)
INDEX(name, creation_date)

Notes:

• MySQL rarely uses two indexes in a single query.
• Hence, a "composite" index is often useful.
• When you have INDEX(a,b), that index will handle other cases needing INDEX(a) (the left column(s))
• No single composite index will be good for all of your queries because of the following 2 points:
• Only the left part(s) of an index are used. That is INDEX(name, created_date) is [virtually] useless for Q1 since name` is not being filtered on.
• In general, a composite index needs to start with column(s) tested by = (name) and optionally end with a single "range" tested column (created_date). The other order fails to get past the range test. Hence, INDEX(created_date, name) is only partially used for Q3, Q4.
• If email is TEXT (or a too-big VARCHAR) it cannot be included in an INDEX.
• The order of columns in INDEX does matter; the order of tests in WHERE does not matter.
• If two columns are tested with =, it does not matter which order they are in for the INDEX. That is "selectivity" is irrelevant.

More: Index Cookbook