Indexing a news table. - 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

Database Design and Application Architecture

Indexing a news table.

Author

Topic

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2011-12-13 : 20:09:20

Hi.I have a standard news table that has id(PK),headertext(nvarchar),innertext(nvarchar),photo(nvarchar) and url(nvarchar).Do i need to provide any extra indexing as this is a stand alone table without any relationships.
Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-12-13 : 20:17:53

It depends. What queries will you have against this table? How many rows do you expect in this table? What is the ratio between reads and writes for this table?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2011-12-13 : 21:37:37

It's a small news web page.The queries is for receiving data pages according to date(i forgot to put the data column in the first post),rows as this is a news forum will be top 20 rows per day lower 1 row per day.I am not sure how to give a ration between reads and writes.
Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-12-13 : 22:41:39

Show us the queries. We can't help with indexes unless we see the queries.

20 rows per day tops though is quite small. It'll take a while before you need other indexes. I like to start out with the right indexes though.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2011-12-13 : 23:35:05

Sorry for the delay.Here is the query and basically all of these are like this one.Thanks i'll look at any answers tomorrow.

select rownum,id,headertext,innertext,url,photo,datec
From
(select id,headertext,innertext,url,photo,datec,
 ROW_NUMBER() OVER(ORDER BY datec desc) as rownum
From news n) as newsinfo
WHERE RowNum BETWEEN @startRowIndex AND (@startRowIndex + @maximumRows) - 1

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-12-14 : 01:15:06

You should add an index to the datec column. I would likely make the clustered index composite with datec and id. I'd have the PK non-clustered.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2011-12-14 : 01:30:08

Thanks.One thing i forgot to mention is that i will be using full text search for searching the news table.I've read that it must be fed with a unique column such as the primary key.Will this interfere if i use your scenario?
Thanks again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources