MongoDB has an Equality, Sort, Range (ESR) guideline that has helped me tune my MongoDB indices in the past.
Now I'm using a SQLite database on a side-project of mine and I'm curious if ESR guidelines make sense in relational databases and/or SQLite as well or not. After all, most (if not all) of them use B-tree indices.
Yes it applies to all B-tree indexes that you should arrange the columns in a compound index starting with equality followed by sort then range. Additionally if there is multiple equality columns arrange them in the order of high cardinality to low cardinality. However the first column is the one that matters most as queries wouldn't be able make use of the index if first column is not referenced by any predicate.
