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.
| Author |
Topic |
|
dbwilson4
Yak Posting Veteran
50 Posts |
 Posted - 2007-06-14 : 17:08:39
|
| Ok. Assume there are two queries. This table contains 10 million rows and is queried heavily against (the main queries than run are the queries below).Query 1:SELECT * FROM [users] WHERE id = 1 AND NOT status = 5;Query 2:SELECT * FROM [users] WHERE id = 1 AND NOT status = 5 AND vouchers > 5 AND tickets < 10;Query 3:SELECT * FROM [users] WHERE token < 78 AND NOT status = 5 AND boughtitems > 10;Does this mean that for the [users] table, I should create 4 indexes?Index 1: Columns = id, statusIndex 2: Columns = id, status, vouchers, ticketsIndex 3: Columns = token, status, boughtitemsPK_Index: id (this is automaticaly done by sql studio management)Is that how indexes should be done? |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-06-14 : 20:44:42
|
| Don't need composite index for every query, you can use database tuning advisor to figure out indexes needed based on queries. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-06-15 : 04:19:35
|
| Index 1: Columns = id, statusIndex 2: Columns = id, status, vouchers, ticketsIn this example Index2will do the job of Index1 as well. You don't need both. However as the PK is [id] these indexes don't achieve anything. For "id = 1" SQL server will use the PK to go directly to the (single) matching record.In Query 3 then Index 3 will help. it will be important that the order of the columns "token, status, boughtitems" has the most "selective" first - but also balanced with which of these three columns are commonly used in query criteria. So if Status is both selective and frequently used that should perhaps be put first - seems unlikely that a column called Status would be Selective, but ...!The other thing to consider is whether you have ORDER BY on your Query, and whether you are really using SELECT *, or actually a small subset of queries, and whether the query is likely to JOIN to other tables. If that is the case the index can be constructed with additional tables to help Cover the query.But that said, SQL Server can also Merge data from multiple indexes ....... so its more of an Art than a Science!I look at the query plans and IO Statistics to get a feel for how much work SQL Server is doing for each query, and then optimise them where necessary. AI add an Index if that makes a big enough difference.Kristen |
|
|
|
rudesyle
Posting Yak Master
110 Posts |
Posted - 2007-06-15 : 14:26:43
|
| In short :All tables should have a clustered index. For your example, id would do the trick.Be careful adding indexes. Just don't willy-nilly throw them on. If your query is taking too long to run, then consider adding an index. Be aware that too many indexes on a table can slow down insert/updates/deletes because the indexes have to be altered as well. |
|
|
|
|
|
|
|
|
Can someone explain indexing to me?