When should one index a column?
For example I have a column with numeric (double) values that is being heavily utilized for searches. However all the values are unique. So how does indexing work in such a case? Make an ordered list?
Markus
indexes can make ordered lists but they also store statistics that can be used to figure out IF the index is useful, how selective it is etc
what kind of queries are you using these values in ?
that tends to drive whether or not an index is useful
and you CAN have the database engine tell you IF the index is useful with the use of the EXPLAIN command
if you have a query, say “select * from table”, and run it as “explain select * from table” then the db engine will usually give you some very useful information about what indexes it will use etc
so you can
- use explain with your query as it is now
- add the index
- use explain with your query with the new index & see if that index will be used
BUT be ware that there can be a downside to indexing everything - you can spend as much time updating indexes as you do just inserting data
And there are tricks that different db engines use to make it so indexes can be even more useful (covering queries comes to mind)
Because the engine has to read the index to then eventually go get the data IF you make a query where ALL the data required to form the result is in the indexes then the db engine may not read the data since it already has it in the indexes - this can make a big difference
DB optimization & index construction is a VERY in depth topic
Doing it right can make a database really fly
Doing it wrong can make it grind
Unique values make the best indexes. Your searches will become VERY fast.
Note a “downside” to indexes (in some cases)
If your app does a lot of INSERTS into an indexed table (especially a large table), those inserts will be slower, as it has to adjust the index for each INSERT (grouping in transaction sets does help somewhat)
I have a large production application using an ORACLE database, where we actually found it “cheaper” to drop the indexes, insert the records (thousands), and rebuild the indexes.
This is most likely NOT something you will need to do, but I point it out just to show a situation where an index actually can slow things down, and I am not advocating to not use indexes
[quote=133450:@Dave S]Note a “downside” to indexes (in some cases)
If your app does a lot of INSERTS into an indexed table (especially a large table), those inserts will be slower, as it has to adjust the index for each INSERT (grouping in transaction sets does help somewhat)
I have a large production application using an ORACLE database, where we actually found it “cheaper” to drop the indexes, insert the records (thousands), and rebuild the indexes.
This is most likely NOT something you will need to do, but I point it out just to show a situation where an index actually can slow things down, and I am not advocating to not use indexes[/quote]
Having worked on a decently large Oracle system where we inserted about 70,000 rows a minute I can say that not being careful enough about indexes CAN lead to hot spots in the index and data pages that can result in slow inserts.
If you have data that has a strictly increasing timestamp in the index (ours was SCADA data so time is always increasing) & you use the date as the first item in the index then EVERY insert is always going to be right near the data that was just inserted because the index & data pages cluster based on that index.
If you visualize data being organized from left to right as oldest to newest all inserts that have the date at the right edge and page splits etc happen in one small spot at the right end. Rebalancing all that takes time & leads to hot spots.
You CAN construct a useful index that does NOT behave like this & spreads things around in a way that you can avoid hotspots.
Hence why I said
DB optimization & index construction is a VERY in depth topic
Doing it right can make a database really fly
Doing it wrong can make it grind