Index Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Index Problem

Author

Topic

kiruthika
Yak Posting Veteran

67 Posts

Posted - 2008-03-17 : 03:44:36

Hi all,

Actually my table is having 44496 rows.I want to create index on my table for improving performance.
I want to search the records by using type
example
select * from messages where type = 1 or
select * from messages where type=2

This is the script for creating index on table
----------------------------------------------
create clustered index myindextest on messages(type)

Before creating clustered index:
---------------------------------
duration :1266

After creation:
---------------
duration : 836

I droped the clustered index and create a new non clustered index.The result will be

create index myindextest on messages(type)

Before creating Non-clustered index:
---------------------------------
duration :1326

After creation:
---------------
duration : 733

Which index can i create on type column? I totally confused.Can anyone give me a proper suggestion for using indexes?

This is the script of my table:
-------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[messages]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[messages]
GO

CREATE TABLE [dbo].[messages] (
[id] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[status] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[from_username] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[to_username] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[message] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[pictures] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sent] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[received] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[type] [int] NULL
) ON [PRIMARY]
GO

Thanks in Advance!

kiruthika
http://www.ictned.eu

kiruthika
Yak Posting Veteran

67 Posts

Posted - 2008-03-17 : 07:05:29

Hi,
Please reply me. I'm wating for long time. Thanks in Advance.

kiruthika
http://www.ictned.eu

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-03-17 : 09:41:08

Put your query in Database Tuning advisor and it will suggest you the best indexes and statistics.

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-03-17 : 19:13:33

This looks like a conversion from Excel or something, but even so, there are some rules of thumb
1) Add primary key - you will want an index on your ID I expect
2) Index any foriegn keys
3) Index where you have good selectivity for where clauses. I am surprised that indexing type helps but like I said, it all depends on what your selectivity is.
4) Careful with clustered indexes - best use with timestamps or ever increasing IDs, otherwise stick with non-clustered.
5) Look at query plans not wall-clock timings. Unless you are getting orders of magnitude, there are too many variables to judge performance on elapsed time. Look for low IO costs.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources