Solved

10774: CREATE INDEX:  search with 2 fields.

Posted on 2014-11-28
25
161 Views
Last Modified: 2014-11-28
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?
0
Comment
Question by:enrique_aeo
  • 9
  • 6
  • 5
  • +2
25 Comments
 
LVL 32

Accepted Solution

by:
ste5an earned 250 total points
ID: 40470224
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40470227
What's the primary key in the Book table?
0
 
LVL 69

Assisted Solution

by:Éric Moreau
Éric Moreau earned 84 total points
ID: 40470239
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
 

Author Comment

by:enrique_aeo
ID: 40470322
CREATE TABLE dbo.Book
( ISBN nvarchar(20) PRIMARY KEY,
  Title nvarchar(50) NOT NULL,
  ReleaseDate date NOT NULL,
  PublisherID int NOT NULL
);
GO
0
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 166 total points
ID: 40470327
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
 
LVL 32

Assisted Solution

by:ste5an
ste5an earned 250 total points
ID: 40470354
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
 

Author Comment

by:enrique_aeo
ID: 40470409
The table is only an example. Please concentrate on indexes that should be created according to the parameters of looking
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 40470426
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
 
LVL 32

Expert Comment

by:ste5an
ID: 40470431
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
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 166 total points
ID: 40470433
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
 
LVL 32

Expert Comment

by:ste5an
ID: 40470451
@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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40470469
@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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 32

Expert Comment

by:ste5an
ID: 40470499
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40470505
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40470532
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40470540
>>  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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40470552
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40470554
>> 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
 
LVL 32

Assisted Solution

by:ste5an
ste5an earned 250 total points
ID: 40470562
@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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40470565
@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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40470573
@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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40470574
@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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40470590
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40470612
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
 

Author Closing Comment

by:enrique_aeo
ID: 40470802
Thanks experts:
I learned a lot of different viewpoints. Thank you
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now