Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 375
  • Last Modified:

Create new index to improve performance

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
rpkhare
Asked:
rpkhare
2 Solutions
 
jorge_torizResearch & Development ManagerCommented:
CREATE NONCLUSTERED INDEX ixYourTable_ItemName
ON YourTable (ItemName)

and create another nonclustered index in the other table that you are using for join
0
 
ASPSQLServerCOMCommented:
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
rpkhareAuthor Commented:
I am using MySQL.
0
 
mickdocCommented:
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
 
ASPSQLServerCOMCommented:
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
 
rpkhareAuthor Commented:
Thanks all.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now