ALTER TABLE ADD INDEX, in some cases, can create speed increases in multiple orders of magnitude.
I was using a SAST program that used MS SQL Server and was generating reports, and often finding the reports took HOURS to generate, even when the report was only ~50 pages. A report on one specific project took over a DAY to generate a report. I thought it was ludicrous, so I logged onto the SQL server to investigate and found that one query was taking 99% of the time. This query was searching through a table with tens of millions of rows, but not indexed on the specific rows it was checking against, and many variations of the query were being used to generate the report. I added the index (Only took about an hour, IIRC), and what took hours now took a couple minutes.
I was always surprised the software didn't create that index to begin with.
In my use case, impact on inserts was not noticed. I did notice higher disk space usage, but it absolutely was worth it. Spending $200 on a larger disk was absolutely worth saving literally days on report generation.
I was using a SAST program that used MS SQL Server and was generating reports, and often finding the reports took HOURS to generate, even when the report was only ~50 pages. A report on one specific project took over a DAY to generate a report. I thought it was ludicrous, so I logged onto the SQL server to investigate and found that one query was taking 99% of the time. This query was searching through a table with tens of millions of rows, but not indexed on the specific rows it was checking against, and many variations of the query were being used to generate the report. I added the index (Only took about an hour, IIRC), and what took hours now took a couple minutes.
I was always surprised the software didn't create that index to begin with.