[AtNightWeCode]

Not sure how to use these recommendations in practice though even if the info is somewhat correct. SQL is a beast of tech and it is used because of battle history and since there is simply no other viable tech replacing it when it comes to transactions and aggregated queries.

Indexes are a nightmare to get right. Often performance optimizations of SQL databases include removing indexes as much as adding indexes.

[larrik]

Indexes aren't a "make my DB faster" magic wand. They have benefits and costs.

If you are seeing performance gains from removing indexes, then I'm assuming your workload is very heavy on writes/updates compared to reads.

[dspillett]

Too many indexes can cause significant performance problems if RAM is short. If the indexes are actually used (rather than sitting idle on disk because other indexes are better choices for all your applications' typical queries) then they will “compete” for memory potentially causing a cache thrashing situation.

But yes, the issue with too many indexes is more often that they harm write performance.

A related issue is indexes that are too wide, either covering many columns or “including” them. As well as eating disk space they also eat extra memory (and potentially cause extra IO load) when used (less rows per page, so more pages loaded into RAM for the same query).

Both problems together, too many indexes many of which are too wide, usually comes from blindly accepting recommendations from automated tools (particularly when they are right that there is a problem, and it is a problem that a given index may solve, but fixing the queries so existing indexes are useful could have a much greater effect than adding the indexes).

[AtNightWeCode]

Mostly because of overlapping indexes. Then if there are include columns it may get out of hand. Not too difficult to achieve. Just blindly follow recommendations from a tool or a cloud service.

[roflyear]

Or you're using MySQL ;)

[vorpalhex]

It's not that SQL is all that beastly, it's that most tutorials fail to explain the internals and basics and so you just see all these features and interfaces of the system and can't build a mental model of how the system works.

[AtNightWeCode]

Well, SQL does come with liberties. I worked with expensive commercial software that destroys the performance of databases by doing everything from complicated ad hoc queries to massive amounts of point reads.

[vorpalhex]

And a hammer will let you smash your own fingers.

All useful tools can be used incorrectly - and I agree SQL is one of the more frequently misused ones. I think a lot of that is that it's one of the more powerful tools.