MySQL Indexes

This is a discussion on MySQL Indexes within the Tech Board forums, part of the Community Boards category; I'd like to be sure about how exactly an SQL index will work in this particular situation, so please share ...

  1. #1
    Magically delicious LuckY's Avatar
    Join Date
    Oct 2001
    Posts
    856

    MySQL Indexes

    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.

  2. #2
    Crazy Fool Perspective's Avatar
    Join Date
    Jan 2003
    Location
    Canada
    Posts
    2,640
    >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
    Code:
    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:
    Code:
    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.

Popular pages Recent additions subscribe to a feed

Similar Threads

  1. Change detection in MySQL.
    By amitbora27 in forum A Brief History of Cprogramming.com
    Replies: 2
    Last Post: 05-13-2009, 11:34 AM
  2. MySQL libraries
    By csonx_p in forum C++ Programming
    Replies: 6
    Last Post: 10-02-2008, 03:23 AM
  3. About C++ and MySQL or oether free database
    By xxxrugby in forum C++ Programming
    Replies: 18
    Last Post: 12-30-2005, 06:28 AM
  4. Get data from mysql and use in a system.
    By smaakage in forum Tech Board
    Replies: 3
    Last Post: 10-04-2005, 01:03 PM
  5. Get data from mysql and use in a system.
    By smaakage in forum C++ Programming
    Replies: 5
    Last Post: 10-02-2005, 02:25 PM

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21