I somewhat know the answer to this question already, but I always feel as though there is more I need to pick up on the topic.
My basic understanding is that generally speaking, a single index that just includes all the fields you might be querying/sorting on at any given time isn't likely to be useful, yet I have seen this type of thing. As in, someone thought, "Well, if we just put all this stuff in an index, the database can use it to find what it needs", without having ever seen an execution plan for some of the actual queries being run.
Imagine a table like so:
id int pk/uid
customerId int (foreign key)
I might see a single index including the
But my understanding is that such an index would not be used in a query like, for example:
SELECT [id], [name], [customerId], [dateCreated]
FROM Representatives WHERE customerId=1
ORDER BY dateCreated
For such a query, it seems to me that a better idea would be an index including the
dateCreated fields, with the
customerId field being 'first'. This would create an index that would have the data organized in such a way that this query could quickly find what it needs - in the order that it needs.
Another thing I see, perhaps as frequently as the first, is individual indexes on each field; so, one each on
Unlike the first example, this type of arrangement seems to me sometimes to at least be partially useful; the query's execution plan may show that at least it's using the index on the
customerId to select the records, but it's not using the index with the
dateCreated field to sort them.
I know this is a broad question, because the specific answer to any particular query on any particular set of tables is usually to see what the execution plan says it's going to do, and otherwise take the specifics of the table(s) and queries into account. Also, I know that it depends on how often a query might be run as opposed to the overhead of maintaining a particular index for it.
But I suppose what I'm asking is as a general 'starting point' for indexes, does the idea of having specific indexes for specific, frequently-pulled queries and the fields in the WHERE or ORDER BY clauses make sense?