CODEWITHSUNDEEP
sql-serverindexing
Ali Sheikhpour
Ali Sheikhpour

Reputation: 11055

Multiple indexes on a same column to improve the search speed

I am working on a web crawler. To prevent crawling duplicate URLs I have stored MD5 of any url in a column. To do a fast search through the MD5 values, I have created an INDEX on md5 column.

As I have no idea if there is any difference between ASC and DESC on md5, I have set the sort order of that index to be ASC. My question is that if defining another index on the same column with DESC order will improve the search speed? Does database engine (in my case I am using SQL server 2016) starts two parallel searches through a same column when there are multiple indexes on the same column?

Upvotes: 0

Views: 38

Answers (1)

Mark Taylor
Mark Taylor

Reputation: 1188

ASC vs DESC won't make a difference - RDBMS optimisers are very well designed and difficult to 'beat' with cheap tricks. As the index is sorted, and the direction know a search can be started at the top or bottom and read in either direction.

The page files required will be quickly located regardless of the direction of the sort.

There may be a difference between a clustered and non-clustered index however depending on your joins and data sets.

Indexs aren't free either, remember, the cost often comes from interacting with (insert/delete/alter) functions along with off-line functions such as backups. There are also space concerns.

My advice is strongly to build the correct indexes based on the most likely painful queries. Often the best way to do that is trial and error along with real testing samples!

Upvotes: 1

Related Questions