Database

Index Column Order Advisor

Reads your WHERE and ORDER BY clauses and puts the index columns in the order that works.

Loading the tool…

Processing happens locally in your browser. What you paste or load is processed by this page and is not uploaded to a server. Nothing is stored unless you use a control that says it stores something, and you can clear anything this site has kept from the privacy page.

How to use this tool

  1. Paste your queries, one per line. A bare WHERE clause works just as well as a whole statement.
  2. Select Suggest the indexes.
  3. Read the reason column, not just the suggestion — it names the columns that were left out and says why.
  4. Look for the redundant rows at the bottom: those indexes are already served by a longer one and should not both be created.
  5. Confirm anything you plan to create against a real EXPLAIN, since this page cannot see your data.

What index column order advisor does

A composite index on (a, b) and one on (b, a) are different indexes with different uses, and the wrong one of the pair is close to useless. The rule that decides it is not complicated but it is easy to get backwards: columns compared with equals or IN go first, then at most one column compared with a range, then the columns you sort by. Once the scan opens on a range, everything after it in the index is no longer in a useful order, which is why a second range column earns nothing and why a sort column placed before a range column costs you the sort you were trying to avoid.

Paste one query, or just one WHERE clause, per line. Each gets back the column order it wants and the reason for that order, including which columns were left out and why. At the end the tool checks the suggestions against each other and flags any index that is a leading prefix of a longer one — those are already served by the longer index and creating both wastes write throughput and disk for no read benefit. It reads text and does not connect to anything, so it knows nothing about your cardinality; treat the output as the starting shortlist, not the last word.

Frequently asked questions

A b-tree is sorted by the whole key, left to right. While each column is pinned to a single value the scan stays on one narrow path. The moment a column is compared with a range, the scan opens out, and everything after that column in the index is no longer in any useful order. So every column you can pin goes first, and the range column goes last of the filtering ones.

For the same reason. Once the first range has opened the scan, a second range column can only be checked row by row as the rows come back — the index cannot narrow on it. The tool names the range columns it left out for exactly this reason. If the second range is the more selective one, it may belong first instead, which is a judgement call your data has to settle.

An index on (a) is a leading prefix of one on (a, b), so any query the short one serves the long one serves too. Keeping both costs disk and slows every insert, update and delete on the table for no read benefit. The tool flags these at the bottom of the result. The reverse is not true — (b) is not served by (a, b) at all.

No. This page reads text and knows nothing about your table sizes, cardinality or how often each query actually runs. An index on a column with three distinct values is rarely worth having whatever the shape suggests. Use the output as a shortlist, then confirm each one against EXPLAIN on real data before creating it.