10774: CREATE INDEX: search with 2 fields.

I have a web form where the user can search with 2 fields (PublisherID, ReleaseDate).

I create three indexes?
index 1
CREATE INDEX NONCLUSTERED IX_Book_Publisher
   ON dbo.Book (PublisherID, ReleaseDate);

index 2
CREATE INDEX NONCLUSTERED IX_Book_Publisher
   Dbo.Book ON (PublisherID);

index 3
CREATE INDEX NONCLUSTERED IX_Book_Publisher
   Dbo.Book ON (ReleaseDate);

The user chooses only ReleaseDate sometimes, other times only PublisherID and sometimes a combination of both (PublisherID, ReleaseDate)

My question is whether I should create three indexes?
enrique_aeoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
It depends. Also you forgot

CREATE INDEX NONCLUSTERED IX_Book_Publisher
    ON dbo.Book (ReleaseDate, PublisherID);

Open in new window


Cause order is important due to selectivity and the statistics used by SQL Server. Just create all four. Then check which index is used less often and remove it.

WITH    IndexSize
          AS ( SELECT   OBJECT_NAME(i.object_id) AS TableName ,
                        i.name AS IndexName ,
                        i.index_id AS IndexID ,
                        8 * SUM(a.used_pages) / 1024 AS 'Indexsize(MB)'
               FROM     sys.indexes AS i
                        JOIN sys.partitions AS p ON p.object_id = i.object_id
                                                    AND p.index_id = i.index_id
                        JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
               GROUP BY i.object_id ,
                        i.index_id ,
                        i.name
             ),
        UnusedIndex
          AS ( SELECT   OBJECT_NAME(S.[object_id]) AS [OBJECT NAME] ,
                        I.[name] AS [INDEX NAME] ,
                        user_seeks ,
                        user_scans ,
                        user_lookups ,
                        user_updates
               FROM     sys.dm_db_index_usage_stats AS S
                        INNER JOIN sys.indexes AS I ON I.[object_id] = S.[object_id]
                                                       AND I.index_id = S.index_id
               WHERE    OBJECTPROPERTY(S.[object_id], 'IsUserTable') = 1
                        AND S.database_id = DB_ID()
                        --AND USER_SEEKS + USER_SCANS + USER_LOOKUPS = 0
             )
    SELECT  S.TableName ,
            S.IndexName ,
            S.[Indexsize(MB)] ,
            U.user_seeks ,
            U.user_scans ,
            U.user_lookups ,
            U.user_updates
    FROM    IndexSize S
            INNER JOIN UnusedIndex U ON S.TableName = U.[OBJECT NAME]
                                        AND S.IndexName = U.[INDEX NAME]
    ORDER BY U.user_seeks + U.user_scans DESC;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
What's the primary key in the Book table?
0
Éric MoreauSenior .Net ConsultantCommented:
I would create only 2 indexes:
(PublisherID, ReleaseDate)
(ReleaseDate,PublisherID)

even if your query only search on 1 field, it is ok if your index contains more

and you have less indexes
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

enrique_aeoAuthor Commented:
CREATE TABLE dbo.Book
( ISBN nvarchar(20) PRIMARY KEY,
  Title nvarchar(50) NOT NULL,
  ReleaseDate date NOT NULL,
  PublisherID int NOT NULL
);
GO
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
The PublisherID is a foreign key, right? If so, it should already have an index on it to improve the joins with the Publisher table. Then you'll only need to create an index on ReleaseDate.
0
ste5anSenior DeveloperCommented:
An ISBN is a compound value. Thus this is a violation of 1NF. Also is the PublisherID redundant, cause it's covered by the ISBN (2NF).
0
enrique_aeoAuthor Commented:
The table is only an example. Please concentrate on indexes that should be created according to the parameters of looking
0
Éric MoreauSenior .Net ConsultantCommented:
I already told you, the 2 indexes are enough.

if you search only on the PublisherID, you are covered. If you search on PublisherID and ReleaseDate, you are also covered.

and vice-versa.
0
ste5anSenior DeveloperCommented:
Proper performance can be only achieved by correctly designed tables.. cause the optimizer relies on relational algebra.

So it matters.

Especially as primary keys are ofte the clustered index, thus define the physical row order and an ISBN has a different sort order than publisher and release date.. E.g.Random House publishes under 978-3-453-31672-0 and 978-0-553-80544-4..

So, please post examples which make sense.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I wouldn't go for a solution of 2 composite indexes and specially covering the same columns.
My recommendation is to create 2 individual indexes:

1. PublisherID

Will improve your join with the Publisher table and also improve any search on the column

2. ReleaseDate

Will improve any search based on this date
0
ste5anSenior DeveloperCommented:
@Vitor: It really depends on the concreate queries and the number of involved rows. There is no rule of thumb which index or index combination will be the most used. Thus creating all possible indices and testing which is/are the most used is necessary.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
@ste5an:
I'm referring only for this question in particular. Why the need of creating two composite indexes on same fields? And assuming that there's already a index for the PublisherID FK, the only thing he needs to do is to create a second index for ReleaseDate.
Also, if the user only introduces on of the values (PublisherID or ReleaseDate) the composite index will be worst. Apart that spends more space in disk and would have impact in insert/delete and update operations.
0
ste5anSenior DeveloperCommented:
We don't know anything about the concrete search cases, only the possible scenarios. So any of the four indicies may support the the queries. Thus they all should be created. And then checked which one is used and how often. This what I meant with my first post-

Optimizing is always a trial and error process. Thus looking at the acutal execution plans and the index usage is necessary.

The only thing nowadys is: Is enough memory available? Then even such a compound index as covering index with the necessary columns included may be the optimum for all these search queries.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Optimizing is always a trial and error process.
I can't agree with you in this one. If development team do their work properly, then they should know in advance which indexes they need in advance. Can be some particular cases that need to be tuned but in general it shouldn't.
And if you have enough experience with databases you should agree that having two indexes with same columns but only in different order it's an odd solution. The order should be only one, from the most selective column to the less one.
0
Scott PletcherSenior DBACommented:
It depends.  Would need more info to do a complete index analysis.

If, for example, the vast majority of searches are on ReleaseDate, you could probably get away with just ( ReleaseDate, PublisherID ).

If the searches are more balanced, and you need optimum response across both, you may have to create both ( ReleaseDate, PublisherID ) and ( PublisherID, ReleaseDate ).

Separate indexes on ReleaseDate and PublisherID are not worth it in this situation based on what we know so far.
0
Scott PletcherSenior DBACommented:
>>  If development team do their work properly, then they should know in advance which indexes they need in advance.  <<

No chance.  *Development* personnel should *never* decide *anything* on indexing.  They should simply present their expected accesses to the DBA to decide on indexing.  Developers never understand indexing and how it's actually used in an RDBMS as well as they think they do.


>> And if you have enough experience with databases you should agree that having two indexes with same columns but only in different order it's an odd solution. <<

I've been a DBA for almost 30 years, and it makes perfect sense to me, if you can't afford to have queries scanning a nonclus index vs. seeking on it.  


>> The order should be only one, from the most selective column to the less one. <<

Again, it depends on the specific usage.


We still need to know what INCLUDEd column(s) are required, if any, if a key lookup is required, cardinality, number of distinct values, etc., to make proper final decisions in this case.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
No chance.  *Development* personnel should *never* decide *anything* on indexing.  They should simply present their expected accesses to the DBA to decide on indexing.  Developers never understand indexing and how it's actually used in an RDBMS as well as they think they do.
Maybe in companies you are worked in is like that but I can tell you that my experience is different. Usually there's a database expert in development teams and is the person that should design the database. In internal teams the DBA's can give an help but when we are talking about external companies developing a product there's almost nothing to do but wait to have the application installed and running.

I've been a DBA for almost 30 years, and it makes perfect sense to me, if you can't afford to have queries scanning a nonclus index vs. seeking on it.  
Like I said, for me it's very odd. Can only accept this solution in a very specific situation.
0
Scott PletcherSenior DBACommented:
>> Like I said, for me it's very odd. Can only accept this solution in a very specific situation. <<


@Vitor:

It's actually quite common.

Do you have any professional DBA experience?
0
ste5anSenior DeveloperCommented:
@Scott is right. As far as I can tell after 20 years ;) and it took my almost 5 years to learn that optimizing a query requires a completely different mind set (DBA) then a developer creating the model and the code behind.

@Vitor, DevOps is a common job describtion when it comes down to databases, where operating means they developed it, the can operate (administer) it. But this does not mean that the average developer is an average DBA.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
@ScottPletcher: I know that you like to show off but I don't need that. My experience is in my profile for who want to see.
0
Scott PletcherSenior DBACommented:
@Vitor:

You are the one that stated:

>> And if you have enough experience with databases you should agree that having two indexes with same columns but only in different order it's an odd solution. <<

You were clearing saying that anyone that disagreed with you was inexperienced at best and possibly even unqualified at worst.  Since you made that broad statement, I wanted to see what you based it on.

And, yes, I am somewhat annoyed by the huge % of developers who believe they somehow automatically know how to design tables and indexes with absolutely no experience as a DBA.  That is what is odd, bizarre even.  

Please just don't make such extremely broad statements with no basis in experience or fact.  You could mislead people into believing that having an index on cols (a,b) and one on (b,a) is some type of error or very rare, when it's not.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
@ste5an: You don't need to be a DBA to know about indexes. Just try to search (Google, Bing, ...) for Database Developer jobs. It's a profile for experts in databases and very good for people that like to design database models. Here's a description of the role.
For me a DBA should avoid to touch in code and focus more in tuning the available resources. I'm working in banks for long and we have applications for many suppliers and of course we can't change the code for their applications but we usually make recommendations and wait for they release new patches based in our inputs. But the applications aren't ours so we can't touch the code.
With more than 4000 SQL Server instances to care on it will be mad to verify all databases and their objects.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
And, yes, I am somewhat annoyed by the huge % of developers who believe they somehow automatically know how to design tables and indexes with absolutely no experience as a DBA.  That is what is odd, bizarre even.
Hummm...
I met some developers that hate DBA's. I think it's because they met you before :)
*** kidding ofc ***
This one was only to break the ice. We are here for long and hope that will continue here. We have different experiences and that's why different opinions. Have a nice weekend.
0
Scott PletcherSenior DBACommented:
Yes, some developers insist on short-cuts in design so they can get to coding, which is, after all, what their schedule requires them to do.  

But being in a hurry doesn't automatically add qualifications to do something.  Just because I want a bridge in a hurry doesn't make me an engineer qualified to design and build one.

If anyone "hates" someone else for doing his/her job, that is their problem, bordering on the unprofessional.
0
enrique_aeoAuthor Commented:
Thanks experts:
I learned a lot of different viewpoints. Thank you
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.