The other direction, however, is not a given: most DBs will let you create a FK on fields not covered by an index, so deleting or modifying a parent can benefit if you create such index explicitly, because it can check for the existence of children much faster (and avoid potentially locking the entire table). Again, the access pattern governs what indexes are needed: if you never delete/modify parent, you may not need an index on FK (unless you also have some queries which can use it, of course).
> /dev/null will suffice.
And if you ever need to read anything even once database without indexes is slow
"database = fast" is way worse heuristic to believe in for the people that need heuristics to move on with what they are doing.
This may be true in most cases, but not all. It just depends on your access pattern. If all you do are full table scans (possibly feeding to hash-joins), you won't benefit from indexes at all!
The whole point is that indexes do not auto-magically improve performance with no downsides. If they did, we could just index all columns and call it a day!
In most cases it means that you shouldn't be doing what youa re trying to do. Or best case, that maybe database is not the right tool for your job.
No it’s not… if all you do is write to it. In fact, it’s the fastest possible database for such case.
Indexes are pure redundancy - they contain the data already in the base table which must be maintained during writes.
But they can make reads so much faster, if the data access pattern can utilize them. The key is to identify access patterns which justify the price of the index.