Not the answer you need?
Register and ask your own question!
Many Forum changes were implemented on Tue 22 Sep. Read about new Ranks, Scoring, and Reactions.
Email [email protected] for any comments or concerns.

Order of columns in indexes

SpeepleSpeeple ContributorInactive User Role Beginner
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.

Comments

  • PeterPeter Percona CEO Percona Moderator Role
    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.
  • SpeepleSpeeple Contributor Inactive User Role Beginner
    Ok, but what is the general consensus.

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

    or the inverse of this?
  • PeterPeter Percona CEO Percona Moderator Role
    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.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.