SQL and Multiple Indexes

indexselect

Consider the following table:

i | a | b | c
--------------
0 | 1 | 2 | 3
1 | 4 | 5 | 6
2 | 7 | 8 | 9
3 | x | y | z

The table, in the end, will have several thousand rows, and even more columns then described here (20+). I plan on adding indexes to the table, but cannot figure out which indexes to make. In the end, I'll be doing SELECT statements with different combinations of these, and sometimes only on 1 column. Is the most efficient way to make the indexes:

(A) Index each column separately, eg. aIndex(a), bIndex(b), cIndex(c), etc..

(B) An Index for each type of select statement, eg. aIndex(a), abIndex(a,b), acIndex(a,c), etc..

Indexes are one of those things I'm still getting a grasp on, so I apologize if this seems like a silly or simplistic question. What I'm trying to avoid is unnecessary indexes as they will increase disk-space and INSERT times dramatically.

Best Answer

It is best to use any combination of the techniques, but match them to your actual queries. Some queries may only need a single column index, while other queries may need a mutli-column index. That is a great thing about indexes, is you can add them as you need them, and remove ones that no longer serve a purpose, so you have exactly what you need for your queries.

Related Solutions

Mysql – Simple Slow Query Not Using Indexes

Your query actually is using the idx_ksours index.

It seems like a pretty sensible assumption that Using index in the Extra field means the query is using the index, and then when that isn't shown in Extra then the index isn't being used... but that's actually not what that means.

Indexes, as you likely know, contain copies of the indexed column(s) data, in sorted order, along with a copy of the primary key of the row so the row referenced by the index can be retrieved.

When all of the data the query needs to examine in a table is available inside one index, the optimizer realizes that reading the actual row data is unnecessary -- everything needed is actually contained in the index, and will be read from the index -- eliminating the extra lookup to retrieve the associated row from the full table. This is what Using index means.

On the other hand, if the optimizer will using an index to optimize the query, the name of that index is shown in key column of EXPLAIN SELECT.

+------------+
| key        |
+------------+
| idx_ksours |
| postid     |
+------------+

So, you're actually using idx_ksours.

That's the good news. The bad news, of course, is that this index doesn't offer enough of an optimization for your entire query.

Note that the second column in idx_ksours is visible, which is not evaluated by anything in the join condition or where clause, which means none of the other columns in that index are not used for this query -- it's only helping you find rows WHERE post.threadid = 562942.

The comment from @dezso addresses this -- if you had an index with all-relevant columns, in a helpful order, that index would provide more benefit for the query.

Sql-server – Consolidating indexes

If the "cube" needs to process all rows, indexes won't speed up the process as the querying engine would just do a table scan.

I think the best advice I could give is to familiarize yourself with query plan output so that you can see how the database is satisfying the query requests. This will allow you to see what access methods are used to retrieve data. It would also allow you to answer you're own question: if you create the proposed index, does sql server use it to fulfill your query?

I think the answer to your question is going to be "No". If your "cube" is being materialized by executing "SELECT DISTINCT columnB FROM table", and you currently have an indexes on "columnA" and "columnB". "Consolidating" those separate indexes into a single index on "(columnA, columnB)" is unlikely to help your "cube" queries on "columnB". Your mileage may vary, not having access to the schema nor query source makes these kinds of questions difficult to answer definitively.

Having said that, I think the correct response is to point you towards the SQL Server data warehousing functionality instead of rolling your own solution.

Best Answer

Related Solutions

Mysql – Simple Slow Query Not Using Indexes

Sql-server – Consolidating indexes

Related Question