Do I need to have an index for each combination of requested fields in an SQL table to optimize performance?

Question

If there are several fields in the User table that can be requested (say, DepartmentId, GroupId, RoleId), will there be a difference in speed if I create an index for each combination of these fields?

By “queryable,” I mean a query screen where the end user can select entries based on a section, group, or role, choosing from a drop-down list.

At the moment I have an index on DepartmentId, GroupId and RoleId. This is the only non-unique index for each field.

If the end user selects “anyone in group B,” SQL looks like this:

 select * from User where GroupId = 2 

Having an index on GroupId should speed it up.

But if the end user chooses “anyone in group B and role C,” SQL will look like this:

 select * from User where GroupId = 2 and RoleId = 3 

Having indices for GroupId and RoleId individually can not make any difference, can it?

The best index for this search would be if I had one index covering both GroupId and RoleId.

But if this happens, it will mean that I will need to have an index for each combination of requested fields. Therefore, I need all of these indexes:

• DepartmentID
• Groupid
• Role id
• DepartmentId and GroupId
• DepartmentId and RoleId
• GroupId and RoleId
• Department ID, GroupId and RoleId

Can anyone shed some light on this? I use MySQL if that matters.

Answer 1

A multi-column index can be used for any left prefix of this index. Thus, the index for (A, B, C) can be used for queries on (A), (A, B) and (A, B, C), but it cannot, for example, be used for queries on (B) or (B, C).

If indexes are indexed separately, MySQL (5.0 or later) can also use Index Merge Optimization .

Answer 2

In general, indexes will have a speed increase , but a decrease in insert / update speed and increase disk space / overhead. Therefore, when asking whether to index each combination of columns, you need to ask if each function in your code should be optimized. It can do some things faster, or it can hardly help, and it can hurt more than it helps.

Index performance depends on:

• Percentage of SELECTs versus INSERT and UPDATE
• SELECT Query Specifics and JOIN Usage
• Indexed Table Size
• RAM and processor speed
• MySQL settings for using RAM, etc.

So it’s hard to give a general answer. Basic Sound Tip: Add indexes if queries are too slow. And don't forget to use EXPLAIN to see which indexes to add. Note that this is similar to the general tips database version: Profile your application before spending time optimizing.

Answer 3

My experience is with SQL Server, not mysql, and maybe that matters. However, in general, an engine can use multiple indexes for a single query. Despite the fact that there is some benefit from having a more complete single index (it provides a larger increase, especially if it forms a coverage index), it will still be useful for you to use an index for each query field.

In addition, keep in mind that each index must be supported separately, so when working with numbers, you will decrease performance when increasing the number of indexes.

Answer 4

Create indexes carefully! I would suggest collecting query statistics and deciding which column is used most often during the search so that you can create a clustered index in that particular column (in any case, when creating an index in several columns, physically the data can be ordered with only one column)

Also note that the Clustered index can significantly reduce the performance of UPDATE/INSERT/DELETE queries as it causes physical data to be reordered.

Answer 5

I found that it is best to index everything that the user will search. I really found better performance by creating indexes with multiple columns if those columns were searched.

For example, if someone can search both in roles and in groupid at the same time, an index with both of these columns will actually be a little faster than having only one index for each of them. However, having an index in each column of a query can be good, since you can skip the combination of columns.

A key consideration is to see how much space indexes occupy. Since these columns are whole fields, this should not be a big problem. Short time index creation could bring significant benefits.

It will be best to experiment. Do a search across multiple columns and times, then add a combined index and run it.

Answer 6

Delete all indexes and run the CRUD commands in the table using a free tool called "SQL sentry plan explorer".

It will show you which indexes are needed.

Indexes are created based on CRUD, not a table.