Solved

Create new index to improve performance

Posted on 2008-10-23
8
363 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to parse the string and split the sub directory names in asp.net 7 56
MySQL Memory Keeps Increasing 4 47
ASP.NET (VB) return a record 2 35
issue with DB import 1 17
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…
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.
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…

839 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