i luv forums...
-
really gives us novices a great place to post our beginner questions: SELECT COUNT(BookTitle) FROM Book WHERE IsFiction = 1; I am Indexing the IsFiction field, but not sure if I should be Indexing the BookTitle field too? Any expert thoughts? Thanks again!
-
really gives us novices a great place to post our beginner questions: SELECT COUNT(BookTitle) FROM Book WHERE IsFiction = 1; I am Indexing the IsFiction field, but not sure if I should be Indexing the BookTitle field too? Any expert thoughts? Thanks again!
Not based on that query. If the booktitle field is in a join or a where clause then it may need an index. Take a look at the execution plan and see where the main cost is, if it says table scan then an index will help.
Never underestimate the power of human stupidity RAH
-
Not based on that query. If the booktitle field is in a join or a where clause then it may need an index. Take a look at the execution plan and see where the main cost is, if it says table scan then an index will help.
Never underestimate the power of human stupidity RAH
I am not joining on the BookTitle field...I just wasn't sure if Count needed BookTitle to be Indexed when it aggregates the count command. I will leave BookTitle un-indexed, thanks!
-
really gives us novices a great place to post our beginner questions: SELECT COUNT(BookTitle) FROM Book WHERE IsFiction = 1; I am Indexing the IsFiction field, but not sure if I should be Indexing the BookTitle field too? Any expert thoughts? Thanks again!
You could in theory get a faster query if you make a composite index on (IsFiction, BookTitle) and booktitle are having many duplicates. But this is improbable in this case as booktitles are fairly unique, and you will have extra overhead on the inserts and updates.
"When did ignorance become a point of view" - Dilbert
-
really gives us novices a great place to post our beginner questions: SELECT COUNT(BookTitle) FROM Book WHERE IsFiction = 1; I am Indexing the IsFiction field, but not sure if I should be Indexing the BookTitle field too? Any expert thoughts? Thanks again!
It is normally good practice to have indexes on fields used in where clauses. However, if IsFiction is a bit field, it will have low selectivity (a measurement of how unique each value is) which will mean that it is highly unlikely that the query optimizer would consider using the index. You are much more likely to find that the optimizer is using a clustered index scan or a table scan.
-
It is normally good practice to have indexes on fields used in where clauses. However, if IsFiction is a bit field, it will have low selectivity (a measurement of how unique each value is) which will mean that it is highly unlikely that the query optimizer would consider using the index. You are much more likely to find that the optimizer is using a clustered index scan or a table scan.
maybe in need 2 go back to the basics...can anyone give me a link or two of a basic tutorial that talks about optimization...i always put indexes on Where or Order By fields if I am doing joins on the Where, and if I think the tables are going to have a huge number of records. also, a simple tutorial link about the Query Optimizer and how to use it would be great too. Thanks!
-
maybe in need 2 go back to the basics...can anyone give me a link or two of a basic tutorial that talks about optimization...i always put indexes on Where or Order By fields if I am doing joins on the Where, and if I think the tables are going to have a huge number of records. also, a simple tutorial link about the Query Optimizer and how to use it would be great too. Thanks!
What you are doing is generally a sensible approach but you need to view the Actual Execution Plan that Sql Server generates to see if they are being used by the Query Optimizer. There is a free pdf download from Red-Gate[^] that is very good. http://www.simple-talk.com/books/sql-books/sql-server-execution-plans/[^] If you are willing to spend some money, then buy this book. SQL Server 2008 Query Performance Tuning Distilled[^] I can't recommend this book highly enough. Although it is targeted at Sql2008, it's still very applicable to Sql2005 as well.