Currently I am creating indexes as I need them for a particular sql query. But they are starting to overlap each other.
Is there any rule to define them effectively?
For example:
Q: If I have two indexes for column1 and column2, does the composite index by column1, column2 improve select by both columns?
Yes then composite Index is better.
From Mysql
"mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;
If a multiple-column index exists on col1 and col2, the appropriate rows can be fetched directly. If separate single-column indexes exist on col1 and col2, the optimizer attempts to use the Index Merge optimization (see Section 8.3.1.4, “Index Merge Optimization”), or attempts to find the most restrictive index by deciding which index excludes more rows and using that index to fetch the rows."
Q: What is there any difference between an index by column1 and column2 over index by column2 and column1?
Yes it will make difference. It depends on how you form your query.
Example If you have index like below on table :
INDEX name (last_name,first_name)
"The name index is an index over the last_name and first_name columns. The index can be used for lookups in queries that specify values in a known range for combinations of last_name and first_name values. It can also be used for queries that specify just a last_name value because that column is a leftmost prefix of the index."
You will get advantage of index for below query:
SELECT * FROM test WHERE last_name='Widenius';
But index is not used for lookups in the following queries:
SELECT * FROM test WHERE first_name='Michael';
Hope this will help !!
An index over two columns A and B is working as an index for column A also, but not as an index for column B.
I don't know if there is an simple rule for indexes, maybe just look for the columns involved in where clauses and order by statements in your queries and evaluate which to use.
Keep in mind that an index makes sense for a large number of rows where you search for a small subset. Indexes also slow down insertion and updates, so use them wisely. It is often enough to simply index all rows that are used to JOIN and add further ones if you run into performance issues.
If I have two indexes for column1 and column2, does the composite index by column1, column2 improve select by both columns?
Yes, it does. Separate indexes have to get merged first or just one index is used.
What is there any difference between an index by column1 and column2 over index by column2 and column1?
In a compound index the order of the columns matters, yes. If your query has only column2 in the WHERE clause, a compound index over (column1, column2) will not be used.
If you have a compound index over (column1, column2) it can also be used if your query only has column1 in WHERE clause.
For additional information see other answers I gave to questions about indexing:
Q)If I have two indexes for column1 (A) and column2 (B), does the composite index by column1, column2 improve select by both columns?
SELECT something from table where A = 1 and B =2 will be using just one of two indices, but if you have compound index A,B it will use it, which should be fasterQ) What is there any difference between an index by column1 and column2 over index by column2 and column1?
This might be usefull
http://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html http://stackoverflow.com/questions/1823685/when-should-i-use-a-composite-index
