What columns generally make good indexes?

Question

As a follow up to "What are indexes and how can I use them to optimise queries in my database?" where I am attempting to learn about indexes, what columns are good index candidates? Specifically for an MS SQL database?

After some googling, everything I have read suggests that columns that are generally increasing and unique make a good index (things like MySQL's auto_increment), I understand this, but I am using MS SQL and I am using GUIDs for primary keys, so it seems that indexes would not benefit GUID columns...

Solution 1

Indexes can play an important role in query optimization and searching the results speedily from tables. The most important step is to select which columns are to be indexed. There are two major places where we can consider indexing: columns referenced in the WHERE clause and columns used in JOIN clauses. In short, such columns should be indexed against which you are required to search particular records. Suppose, we have a table named buyers where the SELECT query uses indexes like below:

SELECT
 buyer_id /* no need to index */
FROM buyers
WHERE first_name='Tariq' /* consider indexing */
AND last_name='Iqbal'   /* consider indexing */

Since "buyer_id" is referenced in the SELECT portion, MySQL will not use it to limit the chosen rows. Hence, there is no great need to index it. The below is another example little different from the above one:

SELECT
 buyers.buyer_id, /* no need to index */
 country.name    /* no need to index */
FROM buyers LEFT JOIN country
ON buyers.country_id=country.country_id /* consider indexing */
WHERE
 first_name='Tariq' /* consider indexing */
AND
 last_name='Iqbal' /* consider indexing */

According to the above queries first_name, last_name columns can be indexed as they are located in the WHERE clause. Also an additional field, country_id from country table, can be considered for indexing because it is in a JOIN clause. So indexing can be considered on every field in the WHERE clause or a JOIN clause.

The following list also offers a few tips that you should always keep in mind when intend to create indexes into your tables:

• Only index those columns that are required in WHERE and ORDER BY clauses. Indexing columns in abundance will result in some disadvantages.
• Try to take benefit of "index prefix" or "multi-columns index" feature of MySQL. If you create an index such as INDEX(first_name, last_name), don’t create INDEX(first_name). However, "index prefix" or "multi-columns index" is not recommended in all search cases.
• Use the NOT NULL attribute for those columns in which you consider the indexing, so that NULL values will never be stored.
• Use the --log-long-format option to log queries that aren’t using indexes. In this way, you can examine this log file and adjust your queries accordingly.
• The EXPLAIN statement helps you to reveal that how MySQL will execute a query. It shows how and in what order tables are joined. This can be much useful for determining how to write optimized queries, and whether the columns are needed to be indexed.

Update (23 Feb'15):

Any index (good/bad) increases insert and update time.

Depending on your indexes (number of indexes and type), result is searched. If your search time is gonna increase because of index then that's bad index.

Likely in any book, "Index Page" could have chapter start page, topic page number starts, also sub topic page starts. Some clarification in Index page helps but more detailed index might confuse you or scare you. Indexes are also having memory.

Index selection should be wise. Keep in mind not all columns would require index.

Solution 2

Some folks answered a similar question here: How do you know what a good index is?

Basically, it really depends on how you will be querying your data. You want an index that quickly identifies a small subset of your dataset that is relevant to a query. If you never query by datestamp, you don't need an index on it, even if it's mostly unique. If all you do is get events that happened in a certain date range, you definitely want one. In most cases, an index on gender is pointless -- but if all you do is get stats about all males, and separately, about all females, it might be worth your while to create one. Figure out what your query patterns will be, and access to which parameter narrows the search space the most, and that's your best index.

Also consider the kind of index you make -- B-trees are good for most things and allow range queries, but hash indexes get you straight to the point (but don't allow ranges). Other types of indexes have other pros and cons.

Good luck!

Solution 3

It all depends on what queries you expect to ask about the tables. If you ask for all rows with a certain value for column X, you will have to do a full table scan if an index can't be used.

Indexes will be useful if:

• The column or columns have a high degree of uniqueness
• You frequently need to look for a certain value or range of values for the column.

They will not be useful if:

• You are selecting a large % (>10-20%) of the rows in the table
• The additional space usage is an issue
• You want to maximize insert performance. Every index on a table reduces insert and update performance because they must be updated each time the data changes.

Primary key columns are typically great for indexing because they are unique and are often used to lookup rows.

Solution 4

Any column that is going to be regularly used to extract data from the table should be indexed.

This includes: foreign keys -

select * from tblOrder where status_id=:v_outstanding

descriptive fields -

select * from tblCust where Surname like "O'Brian%"

The columns do not need to be unique. In fact you can get really good performance from a binary index when searching for exceptions.

select * from tblOrder where paidYN='N'

Solution 5

In general (I don't use mssql so can't comment specifically), primary keys make good indexes. They are unique and must have a value specified. (Also, primary keys make such good indexes that they normally have an index created automatically.)

An index is effectively a copy of the column which has been sorted to allow binary search (which is much faster than linear search). Database systems may use various tricks to speed up search even more, particularly if the data is more complex than a simple number.

My suggestion would be to not use any indexes initially and profile your queries. If a particular query (such as searching for people by surname, for example) is run very often, try creating an index over the relevate attributes and profile again. If there is a noticeable speed-up on queries and a negligible slow-down on insertions and updates, keep the index.

(Apologies if I'm repeating stuff mentioned in your other question, I hadn't come across it previously.)

View more solutions

Comments

[Rick James]

How about a "cookbook": mysql.rjweb.org/doc.php/index_cookbook_mysql

[Arthur Thomas]

string searches where the value can be anywhere inside the string might make it not use those index in that case.

[marc_s]

Well, that's not totally accurate that way. You can easily create a regular, non-clustered index on a GUID column - why not? The GUID has a big drawback if you use it as the clustering key (e.g. for the CLUSTERED INDEX) - then it's a desaster to use.

[Muhammad Babar]

Thanks Somnath, So does it implies indexes should only be created for columns where we are planning to use WHERE, JOINS or HAVING?

[Somnath Muluk]

Yes, use indexes for columns where you are planning to use WHERE, JOINS or HAVING. But also keep in mind, all condition columns not require indexes. Sometimes where condition column is used only once so it might not need index whereas other condition column is used in many queries so prefer more for indexing to that column.

[jpmc26]

Answer would benefit from putting, "columns referenced in the WHERE clause and columns used in JOIN clauses" in a TL;DR section.

[Ozymandias]

So you're saying that if in my WHERE clause I am checking the value of a field where its column can only take on two values, then I should index that binary column? This seems wrong.

[Somnath Muluk]

@AjaxLeung: Remember Knuth's maxim "Premature Optimization is the root of all Evil.". You can make index on binary columns, but it should be dependent on at what cost (like insert, update times). If your business logic often depends on that binary switch then binary column might be required to have index.

[pfabri]

Your explicit mention of foreign keys really cleared things up for me considering joins.

[Domin]

I have one more question about columns used in JOINS - as in the example given above, we have LEFT JOIN country ON buyers.country_id=country.country_id. If those columns (country_id) in both tables were foreign-keys, do they still need additional indexing?

[Domin]

Plus the last question - I've tested indexing over the column which is the type of VARCHAR(1) and contains maybe 3 different ENUMS with 1 mln records in the table - indexing over this field decreases (gets worse) query time so it seems like putting an index over column that is supposed to handle only ENUMS, or booleans is not a good idea, is it?

[Chris Schaller]

You Can create an index on a Guid but the index will be sorted to optimise the Seek operations. When the data being inserted is sequential (like IDENTITY columns) the index can simply be appended with relatively low risk of fragmentation, however Guid values are generally a random value, meaning that each new insert will cause fragmentation of the index and will require the index to be re-sorted. For large databases this can cause serious performance or maintenance issues that will require active management. Using sequential keys instead of Guid simplifies long term management.