Index Advice 2

[HestonJames]

Hello Guys,

I thought I'd ask your advice on this one, quite commonly within my applications I'll have a query which has optional WHERE clauses in it, so sometimes the query is executed looking like this:

Select	Username
From	Users
Where	FirstName = 'Dave'

This is not the real query, I'm using it simply for demonstration purposes to give us a nice generic scenario. Other times the query will be run like this:

Select	Username
From	Users
Where	LastName = 'Smith'

And thirdly it might be run with both clauses, like so:

Select	Username
From 	Users
Where	FirstName = 'Dave'
And	LastName = 'Smith'

Now, suppose I want to place an index on this table to increase the performance of these queries. Will a single index on both the FirstName and LastName Columns suffice? or should I create 3 separate indexes, one for FirstName, one for LastName and one for Both?

My initial gut feeling is that it'll need all three indexes, just wanted to come on an clarify it.

Thanks for any advice on how to tackle this kind of scenario.

Heston

 

[bborissov]

OK, I did a test (not sure if it is valid though):
I created a table with just two fields:
FirstName and LastName both varchar(20).
I indexed the table by FirstName, LastName fields (NOT CLUSTERED and NOT a primary index)
Then I run these queries and get these execution planes for them:

SELECT *
       FROM IndexTest
WHERE FirstName = 'AAA'  AND LastName = 'BBB'
/* Index SEEK */


SELECT * 
       FROM IndexTest
WHERE FirstName = 'AAA'  AND LastName LIKE '%'
/*Index SEEK */

SELECT *
      FROM IndexTest
WHERE FirstName LIKE '%' AND LastName = 'BBB'
/* Index SCAN */

So I think one index is enough but you should think about what query is more often used LastName first and then FirstName or vice versa.


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[gmmastros]

That 3rd query is scanning because of the like '%'. If you add another index to the table that has LastName as the first column in the index, I bet that third query would seek too.

I would recommend 2 indexes. Each index would have 2 columns, first name and last name, but the indexes would have the columns in different orders.

For Boris's fine example:

Create Index idx_IndexTest_FirstName_LastName On IndexTest(FirstName, LastName)
Create Index idx_IndexTest_LastName_FirstName On IndexTest(LastName, FirstName)

For the actual table...

Create Index idx_Users_FirstName_LastName On Users(FirstName, LastName)
Create Index idx_Users_LastName_FirstName On Users(LastName, FirstName)

The column order is extremely important in an index.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[HestonJames]

Thank you both for your help on this, I appreciate it!

I'll go and look at the actual queries that we're running and work on this premise, keeping in mind the column order as being an important factor. We just have a few report queries which are all very similar, some of which run every nicely, others which are very slow, I think the way our indexing is structured is to blame for that ;-)

I may post back with some more explicit examples of the actual queries to get your advice.

I've used the database tuning advisor in the past to analyse the queries and recommend the best indexes, is it possible for me to give it a set of queries and have it give me the best suggested performance tweaks for all of them?

Cheers guy, really appreciate the help, you're super stars!

Heston