Question

Clustered and Non-clustered Index

Query optimization, Clustered Index, Non-Clustered Index

Hi,

I have one table having more than 3 millions rows with following columns:

[SITE_ID]

[USER_NAME]


[PAGE_TYPE]


[DOC_ID]

[FROM_PAGE]

[TO_PAGE]

[ORG_NAME]

[LOG_DATE_TIME]

[SESSION_ID]

[IP]

[MACHINE_ID]



I am using 8 different queries, all thru stored procedure, to fetch data.

SITE_ID, USER_NAME, ORG_NAME and ACTION are in all queries WHERE clause.

I have created SITE_ID as non-unique clustered index and USER_NAME, ORG_NAME and ACTION as non-unique non-clustered index.

Problem is that data fetching without indexs is faster than when creating idex.

Is there any problem in index columns.

Can you please suggest me a better index plan.

I have attached the queries / column table image file.

Images file described columns called details


(http://xs434.xs.to/xs434/08513/queries-columns997.jpg or
http://xs.to/xs.php?h=xs434&d=08513&f=queries-columns997.jpg)


Please help..


Thanks

Sharma

You need to create a single index which contains all the columns which you are in your WHERE clause as well as your ORDER BY clause. The columns in your SELECT statement should be put into the index as included columns (if using SQL 2005 or higher).

You will probably end up with a few different indexes to handle all your queries. Each query with a different ORDER BY Clause will need it's own index, with the Order by columns included in the index in the order you are sorting by them. The queries which don't have ORDER BY statements can all be handled by one index just make sure that you have all the columns that all the queries return listed in the INCLUDE section.

Look at the execution plan to see what index is currently being used.