I'd like to be sure about how exactly an SQL index will work in this particular situation, so please share your thoughts.
I have a table of items with an index on column 'views' which is incremented every time the details for a particular item is displayed. The index is on the column so that items can be quickly listed by most/least views. How detrimental will it be to update this index every time the column changes? If there are 10s or 100s of thousands of items in the db with at least many hundred being listed every minute, my belief is it would be extremely inefficient and have to update the index on an essentially never-ending basis.
Does that sound right? If so, how could this possibly be accomplished in an efficient manner? When a single value in an indexed column changes, how much work does the db do to keep the index up-to-date?
In a related vein: Do you have any idea how efficient, if at all, it is to have an index on a column of type SET? I ask because I know they are stored as bitfields with each bit corresponding to one of the set values, but it doesn't seem logical that the db could do a binary search, for example, to locate records with a particular bit set.
Thanks.
