Solved

Create new index to improve performance

Posted on 2008-10-23
8
365 Views
Last Modified: 2012-05-05
I have a table with following structure:

ItemID    BigInt          Primary Key
ItemCode varchar(10)
ItemName varchar(60)

In most queries, I am using ItemID for joins. But in some queries, need has arisen to fetch records on the basis of ItemName. I am noticing slight performance degradation as items list is growing.

I want to know that if I create an index on ItemName, will it improve the performance of the query? What else I can do to improve the performance of the query where I am fetching with ItemName and not ItemID?
0
Comment
Question by:rpkhare
8 Comments
 
LVL 15

Assisted Solution

by:jorge_toriz
jorge_toriz earned 55 total points
ID: 22791711
CREATE NONCLUSTERED INDEX ixYourTable_ItemName
ON YourTable (ItemName)

and create another nonclustered index in the other table that you are using for join
0
 
LVL 7

Expert Comment

by:ASPSQLServerCOM
ID: 22793448
0
 
LVL 7

Expert Comment

by:ASPSQLServerCOM
ID: 22793458
SQL Server Index Tuning
http://databases.about.com/od/sqlserver/a/indextuning.htm

An Introduction to Clustered and Non-Clustered Index Data Structures

http://www.sql-server-performance.com/articles/per/index_data_structures_p1.aspx
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 8

Author Comment

by:rpkhare
ID: 22793882
I am using MySQL.
0
 
LVL 7

Accepted Solution

by:
ASPSQLServerCOM earned 70 total points
ID: 22796598
0
 
LVL 3

Expert Comment

by:mickdoc
ID: 22797105
Dunno about MYSQL but a rule of thumb is to index things after the WHERE clause in your sql code i.e.

Select * FROM tbl_x WHERE Blah = @var

I would then ensure you at least have an index on column Blah

Same with joins... your join columns should be indexed too.
0
 
LVL 7

Expert Comment

by:ASPSQLServerCOM
ID: 22797612
check for the number of Insert and number of time that same column used in select, because if the number of insert are more then it will take more time to update the index and it will imact the performance of the system, so you have to check for both the scenario and then apply index to it.
0
 
LVL 8

Author Closing Comment

by:rpkhare
ID: 31509461
Thanks all.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Foreword This article was written many years ago, in the days when PHP supported the MySQL extension (http://php.net/manual/en/function.mysql-connect.php).  Today (http://php.net/manual/en/migration70.removed-exts-sapis.php) you would not use MySQL…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

679 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