I definitely have databases like this.

It's not carelessness, it's performance.

Quite simply, I have a table with 4 columns -- A, B, C, D. Each column is just an 8-byte integer. It has hundreds of millions of rows. It has an index on B+C+D, an index on C+D, and one on D.

All of these are required because the user needs to be able to retrieve aggregate data based on range conditions around lots of combinations of the columns. Without all the indices, certain queries take a couple minutes. With them, each query takes milliseconds to a couple seconds.

I thought of every possible way to avoid having all three indices, but it just wasn't possible. It's just how performant data lookup works.

You shouldn't assume people are being careless with indices. Far too often I see the opposite.

Sometimes using mmap for SQL queries can speed them up (if you want to avoid adding too many indexes or cannot modify the db).

Set this on the connection:

PRAGMA mmap_size=…

https://www.sqlite.org/mmap.html

I think this works as it removes data copying between the os and your process.

Your process reads data from the os to execute the query.

With mmap the data reads have less overhead, so a full table scan may be fast enough.

Hah they need to try harder then, I have seen more than 20x the data volume in systems where people are both paranoid and ignorant, a dangerous combo!