Link to home
Start Free TrialLog in
Avatar of enrique_aeo
enrique_aeo

asked on

create index to c1, c2 and c3

hi have this dinamic query

depending on the parameters selected the query could be
--query 1
select .. from .. where c1=1

--query 2
select .. from .. where c1=1 and c2='lop'

--query 3
select .. from .. where c1=1 and c3=10

--query 4
select .. from .. where c2='pol' and c3=90

as I create the index? to column c1, c2 and c3
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

is that your question asking whether or not should we create the indexes for column c1, c2 and c3?

if this is the question, then the answer probably is yes. you can do that.

or if this is not the question you asking for, pls elaborate further for your question.
ASKER CERTIFIED SOLUTION
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
In general terms:

What percentage of the time do you query on c1?  If you query on c1 most of the time, then cluster the table first on c1, assuming it is at all reasonably selective.

If you do that, then you can create a nonclustered index on:
(c2, c3) or
(c3, c2)
Index first on whichever one you search on most often.

Of course to be sure about clustering you'd need to look at index stats, including index missing, index usage and index operational stats.
Avatar of enrique_aeo
enrique_aeo

ASKER

c1, c2 and c3 are nonclustered index

the user enters the search parameters from webform.
The parameter are: firstname, lastname, nationalidnumber are varchar
what then is c1, c2 and c3?
c1= firstname
c2= lastname
c3= nationalidnumber
ok