Indexes you can explain
A useful index is one you can describe in a sentence before you add it.
Most slow queries are not mysterious. They are reading more rows than the question needs. An index is a promise about the order of those rows.
Before adding one, write the lookup in plain language. "Find royalty lines for this society, in this period, that are still unpaid." If the sentence has a clear starting column and a clear range, the index almost writes itself: society, period, status.
A few habits keep that promise honest:
- Lead with the column you always filter on. A trailing column does not help a query that never mentions the ones in front.
- Match the sort. If the page is ordered by
created_at desc, the index should be too, or the database will sort after it finds the rows. - Watch selectivity. An index on a boolean is rarely the win people hope for. An index on
(society_id, paid)often is, because the first column already cuts the table down.
Then look at the plan. If it still says a sequential scan on a large table, the sentence and the index do not match yet. Change one of them. Do not add a second index until the first one is explained.