SQL indexing is definitely something I need to learn more about.
At the same time, it feels a bit like it's become such a standardised fallback approach that people will sometimes throw indexes at a problem instead of applying some thought to the query.
Admittedly, I'm basing this on a few personal anecdotes. For example, a long time ago an acquaintance posted a question on Facebook about speeding up a query in a database of astronomy data, where they were filtering based on a triangle-inequality style formula a^2 + b^2 < c^2. The first five comments said they would just index the a, b and c columns, which would probably work but the author didn't have the right permissions on the DB and only needed to run the query once anyway. I didn't know much SQL at the time but suggested trying a quick initial filtering of the form a + b > c on the data before calculating the formula with squares. This apparently sped it up sufficiently for their purpose.
Comments
SQL indexing is definitely something I need to learn more about.
At the same time, it feels a bit like it's become such a standardised fallback approach that people will sometimes throw indexes at a problem instead of applying some thought to the query.
Admittedly, I'm basing this on a few personal anecdotes. For example, a long time ago an acquaintance posted a question on Facebook about speeding up a query in a database of astronomy data, where they were filtering based on a triangle-inequality style formula a^2 + b^2 < c^2. The first five comments said they would just index the a, b and c columns, which would probably work but the author didn't have the right permissions on the DB and only needed to run the query once anyway. I didn't know much SQL at the time but suggested trying a quick initial filtering of the form a + b > c on the data before calculating the formula with squares. This apparently sped it up sufficiently for their purpose.