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?