Menu
Let's say I have the Products table.
On my UI, I allow the user to search by name, description, code The user can only search on criteria.
Should I create an index for each criteria : name, description, code or create ONE single index for all 3?
What will make you choose one versus the other?
453
Basically it helps your data insert at the end of the index and not cause lots of disk IO and Page splits. Secondly, if you are creating other indexes on your data and they are constructed cleverly they will be reused.
Yes you can have too many indexes as they do take extra time to insert and update and delete records, but no more than one is not dangerous, it is a requirement to have a system that performs well.
Yes, but not with the indexes you've chosen. And that's without having to use multiple index hints.
Like a single index, a composite index is also a data structure of records sorted on something. But unlike a single index, that something is not a field, but a concatenation of multiple fields. position = 'Top'; will have improved retrieval time, because the composite index is sorted by class-position .
Whenever you build an index on multiple columns, say create index .. on T(A, B, C), the index can only be used if the leftmost columns are specified. If you search on column A, the index can be used. If you search on columns A and B then the index can be used. If you search on columns A and B and C then the index can be used. But the index will not be used if you search on column B only, or on column C only or on columns B and C only.
So if you want to search on Product or on Description or on Code you need separate indexes on each. If you want to search for a term in any of the three columns then most likely you need a Full Text search. Same goes for Description, is very unlikely you want a normal index on it, most likely you need a Full Text index on it.
162
You need an index for each. One index for all three will only benefit searches involving the first criterion in the index, or that in combination with the second, etc.
32
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
