4 ms·
> It's really not very hard, basically add indexes It's really not but you'd be surprised how many people don't know the basics of how indexes work. I've had p
by alex7734 3y ago
> It's really not very hard, basically add indexes
It's really not but you'd be surprised how many people don't know the basics of how indexes work. I've had people tell me that their queries couldn't be optimized further because "they had already added an index for every column of the table".
It doesn't help that SQL, by design, hides the actual algorithm doing the data access from the users while simultaneously relying on them to add indexes to achieve performance, which is in my humble opinion the worst mistake of SQL.
- corytheboyd 3y agoI love to ask this very simple question in interviews: if a database index makes queries faster, why not add an index for every column on a table? I want them to say “the heck are you talking about, because that’s not how anything works?”… it does trip some people up though that just legitimately don’t understand what’s happening.
- ako 3y agoThat is not a SQL mistake, but an implementation choice. Most modern databases can determine where indexes should be added, and add these automatically. E.g.: https://www.oracle.com/news/connect/oracle-database-automatic-indexing.html https://www.oracle.com/news/connect/oracle-database-automati...
- akoboldfrying 3y agoThis is news to me. Can any other DBs do this? (I only skimmed -- far too much "Joan explains that" to signal ratio.)
- ako 3y agoSQL server: https://learn.microsoft.com/en-us/sql/relational-databases/automatic-tuning/automatic-tuning?view=sql-server-ver16 https://learn.microsoft.com/en-us/sql/relational-databases/a... Interesting read on a project implementing this for Postgres: https://pganalyze.com/blog/automatic-indexing-system-postgres-pganalyze-indexing-engine https://pganalyze.com/blog/automatic-indexing-system-postgre...
- sharadov 3y agoNot for postgres or mysql.