Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Select and Indexes
- Thread starter aliiiish
- Start date
I'm a little unclear on what you mean by an optimum number of indexes. Are you referring to the number of indexes on a given table, or indexes specified within the select statement itself?
If what you are asking is what is the optimum number of indexes to put onto a table then the answer is that there is no fixed "best" number - it's going to vary based on your data. You can go the "sledgehammer" route and throw an index on pretty much any field that you're ever going to use as criteria for a selection, but then you're going to have performance issues on statements that update data in the table (meaning updates, inserts, or deletes) because all those indexes have to be updated along with the data. You're also going to introduce a lot of potential locking issues if you do it that way. You really want to analyze the ways you are going to use your data the most (using the "execution plan" tool is an easy method, though there are other things you can/should use as well) and design your indexes based off of those test results.
If you mean within a select statement itself, you can only specify one index using the "with index(blahblahblah)" syntax within the statement. It's best to avoid specifying an index except in the cases where the query optimizer doesn't do a good job of picking one (or more) itself (which unfortunately does happen sometimes). Unlike the the select statement syntax, however, the query optimizer can and does use multiple indexes when selecting records from a table based on the criteria used for the selection.
If what you are asking is what is the optimum number of indexes to put onto a table then the answer is that there is no fixed "best" number - it's going to vary based on your data. You can go the "sledgehammer" route and throw an index on pretty much any field that you're ever going to use as criteria for a selection, but then you're going to have performance issues on statements that update data in the table (meaning updates, inserts, or deletes) because all those indexes have to be updated along with the data. You're also going to introduce a lot of potential locking issues if you do it that way. You really want to analyze the ways you are going to use your data the most (using the "execution plan" tool is an easy method, though there are other things you can/should use as well) and design your indexes based off of those test results.
If you mean within a select statement itself, you can only specify one index using the "with index(blahblahblah)" syntax within the statement. It's best to avoid specifying an index except in the cases where the query optimizer doesn't do a good job of picking one (or more) itself (which unfortunately does happen sometimes). Unlike the the select statement syntax, however, the query optimizer can and does use multiple indexes when selecting records from a table based on the criteria used for the selection.
If I had to give an answer with no more details on wha tit is you are really looking for I would probably say: With any given query the optimum number of indexes, would be the number necessary for the optimizer to produce ZERO table scans in the execution plan. (he-he)
Dalton
Dalton
- Status
Similar threads
- Locked
- Question