Is it necessary to have an index on every combination of queryable fields in a SQL table to optimize performance?_问答_开发者

If my User table has several fields that are queryable (say DepartmentId, GroupId, RoleId) will it make any speed difference if I create an index for each combination of those fields?

By "queryable", I'm referring to a query screen where the end user can select records based on Department, Group or Role by selecting from a drop-down.

At the moment, I have a index on DepartmentId, GroupId and RoleId. That's a single non-unique index per field.

If an end user selects "anyone in Group B", the SQL looks like:

select * from User where GroupId = 2

Having an index on GroupId should speed that up.

But开发者_Python百科 if the end user select "anyone in Group B and in Role C", the SQL would look like this:

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

Having indexes on GroupId and RoleId individually may not make any difference, right?

A better index for that search would be if I had one index spanning both GroupId and RoleId.

But if that's the case, than that would mean that I would need to have an index for every combination of queryable fields. So I would need all these indexes:

DepartmentId
GroupId
RoleId
DepartmentId and GroupId
DepartmentId and RoleId
GroupId and RoleId
Department Id, GroupId and RoleId

Can anyone shed some light on this? I'm using MySQL if that makes a difference.

A multi-column index can be used for any left prefix of that index. So, an index on (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 the columns are all indexed individually, MySQL (5.0 or later) may also use Index Merge Optimization.

Generally speaking, indexes will increase query speed, but decrease insert/update speed, and increase disk space/overhead. So asking if you should index each combination of columns is like asking if you should optimize every function in your code. It may make some things faster, or it may barely help, and it might just hurt more than it helps.

The effectiveness of indexes depends on:

Percentage of SELECTs vs. INSERTs and UPDATEs
The specifics of the SELECT queries, and whether they use JOINs
Size of table being indexed
RAM and processor speed
MySQL settings for how much RAM to use, etc

So, it's hard to give a general answer. The basic sound advice would be: Add indexes if queries are too slow. And remember to use EXPLAIN to see which indexes to add. Note that this is kind of like the database version of the general advice: Profile your app before spending time on optimization.

My experience is with SQL Server rather than mysql and it is possible that this makes a difference. However, in general, the engine can use multiple indexes on a single query. While there are certainly benefits to having a more comprehensive single index(it provides a greater boost, especially if it forms a covering index), you will still have a benefit from using an index on each field of the query.

Furthermore, keep in mind that each index must be maintained separately, so you will suffer a performance reduction on write operations as your number of indexes grow.

Create indexes carefully! I would suggest to collect queries statistics and decide which column is more often used whilst search so you can create Clustered index on this particular column (anyway when you are creating Index on multiple columns - physically data can be ordered only by a single column)

Also please be aware that Clustered index could significantly decrease performance of UPDATE/INSERT/DELETE queries because it causes physical data reordering.

What I have found is that it's best to index anything the user will search on. I have actually found better performance by creating indexes with multiple columns if a search for those columns will be executed.

For instance, if someone can search on both roleid and groupid at the same time, having an index with both of those columns will actually be a little faster than having just one index on each one. However, having an index on each queryable column can still good, since you may miss a combination of columns.

A key consideration is to see how much space the indexes will take up. Since these columns are integer fields, it shouldn't be a big deal. A little time creating indexes could reap significant benefits.

The best thing to do will be to experiment. Do a search on multiple columns and time it, then add a combined index and rerun it.

Remove all indexes and run CRUD statements against the table using a free tool called "SQL sentry plan explorer".

It will show you which indexes are necessary.

Indexes are created based on CRUD and not on the table by itself.