Announcement

Announcement Module
Collapse
No announcement yet.

Order of columns in indexes

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Order of columns in indexes

    What is the best way to add a combined index on columns?

    For example, in a theoretical blog post table I have:

    title, comments_flag, visibility, flag

    title doesn't need to be indexed, comments_flag is an TINYINT between 0 and 5 and so is visibility. flag just represents whether the post is deleted or not. flag=0 is not deleted, flag=1 deleted post, so there can only be 2 values.

    Most blog posts will be flag=0.

    So what is the best order of index for performance (InnoDB):

    INDEX(flag, comments_flag, visibility)

    or

    INDEX(comments_flag, visibility, flag)

    Of course the WHERE clause would be updated to suite the index order.

  • #2
    Speeple,

    Index is usually build to facilitate certain selects while you do not clarify what queries are you going to run.

    Depending on them different indexes and different order of columns may be optimal.

    Also note - order of clauses in WHERE clause is not important.

    Comment


    • #3
      Ok, but what is the general consensus.

      index like:
      small range + bigger range + even bigger range

      or the inverse of this?

      Comment


      • #4
        If you have index on (A,B) and have where clause
        A between C1 and C2 and B between C3 and C2

        You will only have key prefix A used.

        Second key prefix is only used for = and IN type of lookups.

        Comment

        Working...
        X