Should search fields be indexed by date in the database table?

Question

If I have a field in a table of some type of date, and I know that I will always look for it using comparisons such as between , > or < and never = can there be no good reason not to add an index for it?

Answer 1

The only reason not to add the index to the field you are looking for is that the cost of maintaining the index outweighs its benefits.

This can happen if:

• You have DML on your hard drive
• The existence of an index makes it unbearably slow, and
• It is more important to have fast DML than fast queries.

If it is not, then just create an index. The optimizer simply will not use it if it considers that it is not needed.

Answer 2

There are much worse reasons.

However, the index in the search column may not be sufficient if the index is nonclustered and non-cover . Queries like these are often good candidates for clustered indexes, but the coverage index is also good.

Answer 3

This is a great example of why this is as much art as science. Some considerations:

• How often are data added to this table? If there is far more reading / searching than adding / changing (the whole point of some tables for outputting data to reports), you want to go crazy on indexes. You may need a clustered index for the ID field, but you may have many indexes with multiple columns (where the date fields appear later, and the columns listed earlier in the index do a good job of reducing the result set) and span the indexes (where all the return values ​​are are in the index, so they are very fast, for example, you start searching by the clustered index).

• If the table is often edited / added or you have limited storage space, and therefore it cannot have tons of indexes, then you need to be more careful with your indexes. If your date criteria usually provide a wide range of data, and you don't often search in other fields, you can give a clustered index to that date field, but think a few times before you do it. You clustered index, located in a simple auto-dial field, is a bonus for all your indexes. Undisclosed indexes use a clustered index to write to records for a set of results. Do not move the clustered index in the date field if the main search area is extensive in this date field. This is a nuclear option.

• If you cannot have many covered indexes (data varies greatly in the table, limited space, your result sets are large and varied) and / or you really need a clustered index for other columns, and typical date criteria give a wide range of entries, and you have to look a lot, you have a problem. If you can dump data into the report table, do it. If you do not, you will need to balance all these competing factors. Perhaps for 2-3 searches, you minimize the columns of the results the way you can tune the covered indexes, and you let the rest do it thanks to a simple non-clustered index

You can understand why good db people should pay well. I know many factors, but I envy people so that they can balance all these things quickly and correctly, without having to do a lot of profiling.

Answer 4

Do not index it if you want to crawl the entire table every time. I would like the database to try to perform a range scan, so I would add an index, but I use SQL Server and it will use the index in most cases. However, different databases do not use an index.

Answer 5

Depending on the data, I would go beyond this and suggest that it could be a clustered index if you are going to make BETWEEN queries to avoid table scans.

Answer 6

Although the index helps in querying the table, it also slows down insertion, updating, and deletion. If the table has more changes than queries, the index can damage overall performance.

Answer 7

If the table is small, it may never use indexes, so adding them can simply waste resources.

There are data types (such as an image in SQL Server) and data distributions where indexes are unlikely to be used or cannot be used. For example, in SQL Server, it makes no sense to index a bit field, because the data does not have enough variability for the index to do any good.

If you usually query with a similar sentence and a wildcard as the first character, the index will not be used, so creating one is another waste of resources.