Solved

Create new index to improve performance

Posted on 2008-10-23
8
361 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
 
LVL 8

Author Comment

by:rpkhare
ID: 22793882
I am using MySQL.
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

867 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

23 Experts available now in Live!

Get 1:1 Help Now