patternsqlMinor
indexing multiple columns in a table in MySQL
Viewed 0 times
columnsindexingmysqlmultipletable
Problem
I am greatly confused about creating an index for a table.
Assuming that a table
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:
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?
Assuming that a table
users have 5 columns, id (primary key), name, email, creation date, and update date.id | name | email | creation_date | update_dateThe 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 = '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?
Solution
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
The index should be
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 ASCThe index should be
(name ,creation_date ,updated_date) which cover the last part of your next question as well.Context
StackExchange Database Administrators Q#322776, answer score: 5
Revisions (0)
No revisions yet.