Solved

Create new index to improve performance

Posted on 2008-10-23
8
368 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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: 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
Independent Software Vendors: 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

Technology Partners: 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!

Question has a verified solution.

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

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

691 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