CODEWITHSUNDEEP
sql
Richie
Richie

Reputation: 1

What is the proper index to make SELECT queries more efficient?

I have a table similar you can see below

Table Keywords
 Column ID
 Column Keyword
 Column Keyword2

the first query is 

select keyword from keywords with (nolock) where keyword = keyword

another query for the same tabel is

select keyword2 from with (nolock) keywords where keyword2 Like 'keyword%'
  1. My question is what index type to set on which columns int this table to make a select process more sufficient? Is it should be clustered index or non-clustered? and on which columns I need to set it?

This table contains about 600k rows and it constantly growing.

  1. Another question I'm getting a dead-lock error when I trying to insert a new record to Keywords table. What can be the problem? I'm selecting records with nolock.

Thank you

Upvotes: 0

Views: 289

Answers (2)

Andrew Lazarus
Andrew Lazarus

Reputation: 19330

If we are really seeing the only use cases, you want a clustered key on keyword2 and then hope your DBMS is smart enough to optimize index use with LIKE operator. Clustering helps when the returned rows from a typical query are adjacent in the DB, so keeping the table in alphabetical order on keyword2 will mean fewer pages have to be scanned on the SELECT. Clustering a table where access is pretty much random (e.g., user names) won't give you any more than a standard index.

Upvotes: 0

marc_s
marc_s

Reputation: 754508

Since your two queries are on totally separate columns, you will need two separate non-clustered indices:

  • one index on keyword to speed up the first query
  • a second index on keyword2 to speed up the second query

And assuming you're using SQL Server: neither of them really makes a good clustered index, I would say - but a good clustered index would be really beneficial!

A good clustered index should be:

  • unique
  • small
  • stable (never changing)
  • ever-increasing

Your best bet would be on an INT IDENTITY field. See Kimberly Tripp's outstanding blog post Ever-increasing clustering key - the Clustered Index Debate..........again! for more detailed background on the requirements for a good clustering key.

Upvotes: 6

Related Questions