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)
INDEX(comments_flag, visibility, flag)
Of course the WHERE clause would be updated to suite the index order.