qouestion about Index
-
Hi,
For reach best performance, its better to put all columns in one index or create one index per column that must have index ?
-
Indexing per column seems to be good for the smaller databases, but it will slow down the performance when the size of the databse will raise.
SEE Boyce Code Normal Form - 2 and BCNF - 3 for more clarity
kshitij kumar
kshitij@krayknot.com
www.krayknot.com -
I really do not think you can make a blanket call about this question with out knowing how the application will touch the entity. If you know your going to have the first column in your index in every access to the table then a multiple column index would probably work. IF you have no idea how the table is accessed then maybe individual indexes would make sense.
Also it is very important how much of the cluster will be used in every call to that table.
-
It depends.
http://sqlinthewild.co.za/index.php/2009/01/19/index-columns-selectivity-and-equality-predicates/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
sorry, may be i explain my question badly.I have a table with 10 million records, and i created Separate index for each index-needed columns . my question is :
its better put all columns together in one index or this situation is the best.
-
Gail's "it depends" answer is spot on...it depends on how your data gets accessed; indexes help find the data faster.
ok 10 million records, but which columns are actually used for searching? if the data gets searched by the PK, firstname or lastname , then i'd make a single index with those three columns for example. there's no sense putting an index on a table's " phone number extension" column if noone ever searches the table for that.
after that index is in place, then i'd test it, look at it's performance with typical queries, and see if it can be imporved with seperate indexes or not.
this is one area where you make your experience as a dba...testing your performance with indexes, tweaking them, finding optimal balances. You don't want to just create a bunch of indexes and hope for the best, you want to verify that they help.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
farax_x (11/3/2009)
my question is :its better put all columns together in one index or this situation is the best.
As I said, it depends. It depends purely on the queries that you're running against that table. Did you read the blog post I referred you to?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
GilaMonster (11/3/2009)
farax_x (11/3/2009)
my question is :its better put all columns together in one index or this situation is the best.
As I said, it depends. It depends purely on the queries that you're running against that table. Did you read the blog post I referred you to?
yes it helps but does not answer my question.
-
Lowell (11/3/2009)
Gail's "it depends" answer is spot on...it depends on how your data gets accessed; indexes help find the data faster.ok 10 million records, but which columns are actually used for searching? if the data gets searched by the PK, firstname or lastname , then i'd make a single index with those three columns for example. there's no sense putting an index on a table's " phone number extension" column if noone ever searches the table for that.
after that index is in place, then i'd test it, look at it's performance with typical queries, and see if it can be imporved with seperate indexes or not.
this is one area where you make your experience as a dba...testing your performance with indexes, tweaking them, finding optimal balances. You don't want to just create a bunch of indexes and hope for the best, you want to verify that they help.
tnx alot , it was helpful
-
farax_x (11/3/2009)
yes it helps but does not answer my question.There is no one, single, always right answer to your question. Without knowing the queries that run against that table, there is absolutely no way to advise on which indexes will be better. Understand how SQL uses indexes, and you'll be able to answer the question yourself, once you know what queries run against the table.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
GilaMonster (11/4/2009)
farax_x (11/3/2009)
yes it helps but does not answer my question.There is no one, single, always right answer to your question. Without knowing the queries that run against that table, there is absolutely no way to advise on which indexes will be better. Understand how SQL uses indexes, and you'll be able to answer the question yourself, once you know what queries run against the table.
ok tnx for your kind answer. I thought I should say these :
my table :
CREATE TABLE dbo.tblDocument(
docId uniqueidentifier ROWGUIDCOL NOT NULL,
docOrgId int NOT NULL,
docOrgSrvId smallint NOT NULL,
docOwnerDepartmentId uniqueidentifier NOT NULL,
docOwnerPersonnelId uniqueidentifier NOT NULL,
docOwnerName nvarchar(150) NOT NULL,
docDescription nvarchar(max) NOT NULL,
docType smallint NOT NULL,
docNo nvarchar(40) NOT NULL,
docDate char(8) NOT NULL,
docSubject nvarchar(500) NULL,
docSecurityId tinyint NOT NULL,
docUrgencyId tinyint NOT NULL,
docArchiveTotal smallint NOT NULL,
docAttachTotal smallint NOT NULL,
docRegisterTotal smallint NOT NULL,
docRelationTotal smallint NOT NULL,
docSendTotal smallint NOT NULL,
docSignatureTotal smallint NOT NULL,
docTitleTotal smallint NOT NULL,
docDeleteOk bit NOT NULL,
docRegisterDate datetime NOT NULL,
docProperties nvarchar(max) NOT NULL,
docTemplate nvarchar(max) NOT NULL,
docKeyWord nvarchar(300) NOT NULL,
DocAttachType nvarchar(300) NULL,
docOrganizationId uniqueidentifier NOT NULL,
docRegNo varchar(50) NOT NULL,
docRegDate char(8) NOT NULL,
CONSTRAINT PK_tblDocument PRIMARY KEY CLUSTERED
(
docId ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY
)
sample queries :
SELECT * FROM (
SELECT TOP 30 ROW_NUMBER() OVER (ORDER BY docRegisterDate ) as ROW_NUM, DocId,typName, DocSubject,docRegNo,docNo,DocDate,docOwnerName,doc RegisterDate,docType
FROM dbo.tblDocument WITH (NOLOCK)
INNER JOIN (SELECT accDocId, accDepartmentId FROM tblAccess WITH (NOLOCK) GROUP BY accDocId, accDepartmentId) AS tblAccess
ON dbo.tbldocument.docId = tblAccess.accDocId
INNER JOIN IstgRef.dbo.tbrType with (nolock) on tbldocument.docType = tbrType.typId
WHERE docType <> 1002 AND tblAccess.accDepartmentId ='6FCFF50F-012D-436D-B7A1-D9991ADA417F' and docNo LIKE '%123%' and docDate between '13880713' and '13880813' and docSubject LIKE '%test%'
ORDER BY ROW_NUM
)
INNERSELECT
WHERE ROW_NUM > 10
and indexes are create on these columns: docDate,docSubject,docNo,docRegisterDate
execution plan :
-
For that particular query, quick guess would be a composite index on
DocID, DocDate, docSubject, docNo (in that order)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
GilaMonster (11/10/2009)
For that particular query, quick guess would be a composite index onDocID, DocDate, docSubject, docNo (in that order)
Bet that isn't the only query to hit the table(s) though.
OP, you need to evaluate all the queries and their frequency and typical parameters in order to do a good job indexing your tables.
You might also look into using full text indexing since you have two LIKE '%something%' filters.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
I tend to come at this issue from a different angle.
I agree with the others on this post that "it depends".
But there are ways you can determine some of the "it depends".
I have posted this very simple query before but here it is again.
select index_fieldname, count(*)
from table_name
group by index_fieldname
Using this query you can see the cardinality and distribution of data on your indexes.
If either one is bad then I would consider making them part of a compost index.
If you have 10 Million rows and you insert your primary key field in my query.
You will see a cardinality of 10 million and a perfect distribution each will have 1 record.
If you have an index on a field like gender you should have a cardinality of 2 and depending on your data a reasonable distribution of something like 5 million men and 5 million women.
The cardinality on this index is bad and could be dropped or considered as a second part of a compound index.
If you have 9 million men and 1 million women you have a bad distribution as well.
I would suggest on a table of 10 million records that any index with cardinality below 10 would be a candidate to be part of a compound index.
As well any index that has more than 90% of the records in one or 2 items could also be considered the same way.
When I am compounding indexes I always try to use the field with the highest cardinality first.
I generally do not worry about tables with 10,000 records or less.
I also try to get indexes with no higher than 10,000 on average distribution as well.
I hope this helps.
[/url]Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply