MySQL Indexes

[LuckY]

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.

[Perspective]

>When a single value in an indexed column changes, how much work does the db do to keep the index up-to-date?


That depends on the index structure, it may have to rebalance a tree, or in the case of a B+ tree (which is always balanced) it may have to split/create nodes.

It all comes down the the workload, if your updating the data just as often as retrieving it, I think the index will cause more overhead than it's worth. You should do some offline profiling to check though.

The other point here is that you don't necessarily know how the DB will evaluate your query. If it's

SELECT {...}
FROM Table
ORDER BY views

than the index is likely used (since the data can be retrieved in order, avoiding the sort), however, if it's more like:

SELECT {...}
FROM Table
WHERE <Predicate>
ORDER BY views

the DB may chose to use another index based on the predicate, and your views index is maintained for nothing. Similarly, if your joining tables the evaluation plans may vary.