SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Guru
    Join Date
    Jul 2005
    Location
    Orlando
    Posts
    634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Should I add an index to a TINYINT field with a small set of possible values?

    I commonly use a field I call "active" on my database tables. It's a TINYINT(1) field that I store boolean values in. This field basically holds whether a row is ready to be used in production. When a row has outlived its usefulness, rather than deleting it it will be set to inactive, and active becomes 0.

    Consequentially, this field is used in almost every SELECT statement against the table. They will almost always check for active = 1 in the WHERE clause.

    Should this field be indexed?

    Since it's used in the WHERE clause so often, it would make sense to index it, imo.

    But this active field is almost always set to 1 -- maybe 95 - 99% of the time. My understanding is having an index on a field that is almost always the same thing can actually slow the speed of the query down.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    your understanding is correct

    the only time an index on "active" might be used would be if you were searching for all active rows

    the optimizer would clearly just use a table scan instead

    so indexing this field separately would not help

    when you use it in the WHERE clause, i'll bet that you have other search criteria, and those other criteria would probably benefit from indexing

    you might consider adding "active" as a secondary column in those indexes

    e.g. ALTER TABLE foo ADD INDEX category_ix ( category_name, active )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru
    Join Date
    Jul 2005
    Location
    Orlando
    Posts
    634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks r! I was kind of thinking that way. I had just noticed I had missed some fairly obvious indexes, and wanted tos top myself from going overboard.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    do you know what a covering index is?

    not adding "active" as a secondary column in an index (when you are searching for active rows) will prevent indexes from functioning as covering indexes

    although you have to use discretion, and not index everything in sight hoping to make them all covering indexes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru
    Join Date
    Jul 2005
    Location
    Orlando
    Posts
    634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I had never heard the term before, but after reading about it it makes perfect sense. Thanks again!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •