SQL and Table Indexes
AsSiMiLaTeD
Posts: 11,725
I have a quick question about table indexes. I'm specifically working in SQL Server 2005, but I suppose my question is generic enough to apply to any environment.
I know in SQL Server you can have multiple indexes on a single table, I'm wondering how many is too much, or does such a limit exist. If I have 30 fields and I have 3 indexes, how does that work technically? Do all 3 indexes exist simultaneously, or only as I access a field that's part of an index?
I'm just trying to wrap my brain around how SQL Server really handles indexes. I mean at it's core, all an index really does is sort so that a given value is easier to find, right (since the data is sorted it can cut the data in half, then in half again, then again and again until my value is found, which is quicker than searching sequentially through the entire dataset).
So given how that works, wouldn't too many inexes on a table slow it down? What's the trick in determining how many is too much?
I know in SQL Server you can have multiple indexes on a single table, I'm wondering how many is too much, or does such a limit exist. If I have 30 fields and I have 3 indexes, how does that work technically? Do all 3 indexes exist simultaneously, or only as I access a field that's part of an index?
I'm just trying to wrap my brain around how SQL Server really handles indexes. I mean at it's core, all an index really does is sort so that a given value is easier to find, right (since the data is sorted it can cut the data in half, then in half again, then again and again until my value is found, which is quicker than searching sequentially through the entire dataset).
So given how that works, wouldn't too many inexes on a table slow it down? What's the trick in determining how many is too much?
Post edited by AsSiMiLaTeD on
Comments
-
its not entirely accurate to say the table access will slow down.
inserting and updating would take longer, as each row you attempt to write would need to update all the indexes.
However data retrieval would be faster.
you don't want too many indexes, you want just enough. how much is enough?
well, you want to index fields that appear wafter the "WHERE" and the "AND" parts of your SQL query. nothing more.
in some cased you might want to join two fields into one index. that would result in faster performance, depending on the case of course.
Finally, the type of indexing you've mentioned uses binary tree sorting, its not the only option. MsSql server, and in fact most others would allow you to select different types of sorting algorithms for your indexes.
though most default to bTree."If the global crisis continues, by the end of the year Only two Banks will be operational, the Blood Bank and the Sperm Bank. Then these 2 banks will merge and it will be called 'The Bloody **** Bank'" -
carpenter is correct. You determine what should have an index by what you're doing in your stored procedures / functions / sql commands. If you access UserTable with UserID for joining to another tables and you also access UserTable by UserName for logins, then you'd want two indexes: one for UserID and one for UserName.
Though, UserID should typically be a PrimaryKey and thus is already an index. Then you'd just create a unique index for UserName.
If you posted the fields in the table we may be able to give you a better idea but it still depends on how something accesses that table. -
