The gist is:
1. Trace your application, so you know which query is running slowly in production.
2. Using this exact query, run `EXPLAIN ANALYZE` or whatever your RDBMS equivalent is.
3. Read the output, see which step is taking the slowest. Use google to help.
4. Google-fu until you find out which index might help you.
Over time, (3) and (4) requires less and less Google, because there's really only a few common cases that give you 100% of the speedup in 80% of cases.
Once you know this path to improvement exists, it's trivial to progress down it habitually. The `EXPLAIN ANALYZE` output looks like Greek at first, but quickly becomes as familiar to parse as a compiler error, etc.
1. Trace your application, so you know which query is running slowly in production.
2. Using this exact query, run `EXPLAIN ANALYZE` or whatever your RDBMS equivalent is.
3. Read the output, see which step is taking the slowest. Use google to help.
4. Google-fu until you find out which index might help you.
Over time, (3) and (4) requires less and less Google, because there's really only a few common cases that give you 100% of the speedup in 80% of cases.
Once you know this path to improvement exists, it's trivial to progress down it habitually. The `EXPLAIN ANALYZE` output looks like Greek at first, but quickly becomes as familiar to parse as a compiler error, etc.