Confusion about multi compose index within one table

Question

I have a book online sale website, in the system, there is a table called Order, and there are some columns like below:

OrderID int (Primary key),
Library_ID int,
Mode int,
BookId int,
Ext_User_ID varchar(2000),
Activated varchar(1),
RequiredDate datetime,
AmountBuy int,
APPROVAL_DATE datetime,
...

So the table has over 7 millions rows, currently I am working on add more query conditions to a webpage, the sql script is like below:

SELECT * FROM Book b, Library l, [Order] o 
WHERE o.bookid = b.bookID 
AND o.mode = 'A' 
AND o.library_ID = l.library_ID 
AND l.library_ID > 19 
AND b.publisher_id  > 1000 
AND b.print_id > 800 
AND NOT EXISTS (SELECT * FROM ExtBOOK WHERE b.bookid = extbookid AND library_ID = l.library_ID) 
AND o.activated = 'Y' 
AND b.eisbn13 LIKE '978%' 
AND len(o.ext_user_id) > 3 
AND b.bookid > 200000 
AND b.bookid in (SELECT bookid FROM category WHERE categoryid  > 2) 
ORDER BY o.orderid DESC

This script probably contains all the possible query conditions, after I running it on Management Studio with "Included Actual Execution Plan" turn on, the recommendation said I need to create a index on Order table

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[Order] ([MODE],[ACTIVATED],[LIBRARY_ID],[BOOKID])
INCLUDE ([OrderID],[EXT_USER_ID],[APPROVAL_DATE])

However, when I go to create the index, I found out other three indexs already there, they are:

create nonclustered index IDX_Order_1 { MODE asc}
create nonclustered index IDX_Order_2 { Library_ID asc, EXT_USER_ID asc, BookID asc}
create nonclustered index IDX_Order_3 { BOOKID asc, Library_id asc, MODE asc }

So the above 3 index have been created, the question, if I want to create my index, I mean I felt some duplicate, can I merge them? Or each index is for specific query, cause the tables is growing everyday, and I can not ensure which app or what sql script using except my query. Any idea?

Answer 1

I would recommend you do some reading about how indexes work.

This link would give you the basics.

http://www.simple-talk.com/sql/learn-sql-server/sql-server-index-basics/

But in answer to your question, you should create indexes on the columns which are used frequently. This means that If you create an index on BookID, LibraryID and Mode this index uses all 3 columns and is usefull when your where clause looks at these 3 columns. Having that index and then only searching on LibraryID would not be useful as far as I understand things.

Basically look at the queries you are doing on your tables, identify which columns are used then index the most frequently used ones. Beware of overindexing though as this will slow your database down.