index-question

Question

index-question

Airolg Icsadjag

SSC Eights!

Points: 961
More actions
January 22, 2006 at 9:09 pm

#90514

hi!
i have a table (TBL) which has two data colums (COL1, COL2) in which i want to search for @a, and an ID column which i want to get. my query is the next:
select ID from TBL where COL1= @a or COL2= @a
i created a unique nonclustered index for COL1, and i create a nonclustered (and not unique) index for COL2 and ID together.
i need some advice if it was a good choise or i have to change something.
thanks for your answers.

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Paul Mu SSCertifiable Points: 5547 More actions · Answer 1

Depending on whether the table will be used in a join with other tables, and assuming that ID is the join key, then possibly you could make it the clustered index. Since you are searching on both attributes COL1 and COL2, then both can be nonclustered indexes.

Note that the selectivity of the data will determine if you should consider an index. The best thing for you is to experiment to see which would give you the best performance.

HemaSunder Bugatha Right there with Babe Points: 737 More actions · Answer 2

Hi

It would be better if u had created a composite index on those columns as it would increase your performance.

sunder..

Airolg Icsadjag SSC Eights! Points: 961 More actions · Answer 3

using composite indexes does the performance depend on the columns order in the definition?

if the answer is yes, what wolud be the right order to my query? COL1, COL2, ID?

another note. on COL1 there is a uniqe constraint/ index. so if i put into this index COL2 and ID, COL1 uniqueness is over, isnt it?

HemaSunder Bugatha Right there with Babe Points: 737 More actions · Answer 4

Specify the column with unique index first so that the search will

be easier..give : select ID from TBL with col1=@a..

let me know if it works..

..sunder

Airolg Icsadjag SSC Eights! Points: 961 More actions · Answer 5

i never heard about using with in a select query.

what do you mean? can u explain?

thx G

HemaSunder Bugatha Right there with Babe Points: 737 More actions · Answer 6

HemaSunder Bugatha

Right there with Babe

Points: 737

January 23, 2006 at 2:11 am

#616331

Sorry, it was 'where' not 'with'....

HemaSunder Bugatha Right there with Babe Points: 737 More actions · Answer 7

HemaSunder Bugatha

Right there with Babe

Points: 737

January 23, 2006 at 2:13 am

#616332

Sorry, it was 'where' not 'with'....

Airolg Icsadjag SSC Eights! Points: 961 More actions · Answer 8

ahha

as i wrote in my 2nd post, my query is the next:

select ID from TBL where COL1= @a or COL2= @a

i think, its almost the same, what u advice me, isnt it?

my query's long run time couses by OR, i think.

HemaSunder Bugatha Right there with Babe Points: 737 More actions · Answer 9

I want to know what is happening when you make a composite index on the three columns.

..sunder

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

I want to know what is happening when you make a composite index on the three columns.

What exactly do you want to know?

select ID from TBL where COL1= @a or COL2= @a

If you have an OR, SQL will only use indexes if there is an index on each of the columns involved in the OR. If you have an index on Col1 and another on Col2 then SQL will be able to use the indexes. Otherwise not.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Airolg Icsadjag SSC Eights! Points: 961 More actions · Answer 11

thanks

and what do u think? i have to create an index on ID, or not?

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 12

To be able to return results from the query, no you do not need to create an index. If you want the query to perform optimally you should however create two indexes, one on (Col1, ID) and the other on (Col2, ID). Do not just create indexes because this query would be faster with them though, you need to consider the system as a whole.

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

Do you ever search on ID? Is it the PK of the table (and hence uniquely indexed)

How many rows are in the table? How often is it queried? How often is it inserted, deleted or updated? Where is the clustered index?

Don't create indexes just because you can. You should analyse the access patterns of a table to see if an index is necessary before adding one. Testing in a dev environment is also recomended.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Airolg Icsadjag SSC Eights! Points: 961 More actions · Answer 14

the ID is the PK! ... jesuschrist! i forgot it!

so. this is that becouse i use ID to connect to another tables and for UPDATEs too. so ID is unchangeable.

but COL1 must be uniqe too. so this is a nonclustered unique constraint. and COL1 is changeable. its almost like names.

COL2 contains just a secondary information, like birth names. but i need for searching.

the table has cca 20 000 rows.

in the first time i have to query COL1 and COL2, but later i will use only the ID. in the first time, i make a big amount of INSERT in a very short time and some UPDATEs if its needed. later i wont. i dont make any delete or just a very few later.