# Order of columns in indexes

**URL:** <https://forums.percona.com/t/order-of-columns-in-indexes/108>\
**Category:** Other MySQL® Questions\
**Created:** [October 27, 2006, 7:18am UTC](https://forums.percona.com/t/order-of-columns-in-indexes/108 "2006-10-27T07:18:19Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Speeple](https://avatars.discourse-cdn.com/v4/letter/s/73ab20/32.png) [@Speeple](https://forums.percona.com/u/Speeple)\
**Post date:** [October 27, 2006, 7:18am UTC](https://forums.percona.com/t/order-of-columns-in-indexes/108/1 "2006-10-27T07:18:19Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Peter](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/peter/32/2_2.png) [@Peter](https://forums.percona.com/u/Peter)\
**Post date:** [October 27, 2006, 7:34am UTC](https://forums.percona.com/t/order-of-columns-in-indexes/108/2 "2006-10-27T07:34:43Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Speeple](https://avatars.discourse-cdn.com/v4/letter/s/73ab20/32.png) [@Speeple](https://forums.percona.com/u/Speeple)\
**Post date:** [October 27, 2006, 7:45am UTC](https://forums.percona.com/t/order-of-columns-in-indexes/108/3 "2006-10-27T07:45:13Z")

</div>

Ok, but what is the general consensus.

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

or the inverse of this?

---

<div class="post-metadata">

**Author:** ![Peter](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/peter/32/2_2.png) [@Peter](https://forums.percona.com/u/Peter)\
**Post date:** [October 27, 2006, 1:27pm UTC](https://forums.percona.com/t/order-of-columns-in-indexes/108/4 "2006-10-27T13:27:57Z")

</div>

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.