3 ms·
> In all other CS domains I know exactly what will happen under the hood for any given action, but for SQL I have zero clue. With experience, you'll learn to a
by nine_k 6y ago
> In all other CS domains I know exactly what will happen under the hood for any given action, but for SQL I have zero clue.
With experience, you'll learn to appreciate the lack of need to say how to do something, and the ability to just specify what needs to be done. Read more about declarative vs imperative.
Knowing exactly what happens under the hood costs a lot of mental resources (this is why assembly is considered low-level), and is often an illusion. A proven tool which knows a particular area instead of you is immensely valuable (think about compilers, for instance).
> it would automatically add an index
Adding an index has performance costs, storage costs, etc. Just indexing every permutation of columns is prohibitively expensive. Then, columns with low variation need different indexing approaches, or sometimes even refactoring of the schema.
Software that suggests which indexes would benefit a particular query does exist. Consideration is still needed.
- DaiPlusPlus 6y ago> Adding an index has performance costs, storage costs, etc. Just indexing every permutation of columns is prohibitively expensive. Then, columns with low variation need different indexing approaches, or sometimes even refactoring of the schema. To be clear, I’m not suggesting a DBMS should always add a new index every time it encounters a query that’s slow due to a missing index. I’m saying that DB servers should look at trends of queries - and see where real wall-clock time is being wasted, and act accordingly. The performance cost of an extra index for insert/update/delete queries is only a concern when you get to the point where you do need to manually manage indexes - but for most database tables with less a few million rows there’s no reason for the server not to automatically add indexes on columns used in predicates. It won’t be perfect, but it’s better than allowing slow queries to always be slow.
- nine_k 6y agoUnfortunately every index takes time to access; it's like dereferencing a pointer. You also usually want the hottest indexes to reside in RAM. Making more indexes compete for RAM pages may and will have implications for other queries, possibly more important. I agree that often machine resources are plentiful, and human resources are scarce, so managing indexes automatically makes sense. It's even implemented, e.g. in Oracle as a complete management tool [1], or in Postgres as a discovery and recommendation tool [2]. [1]: https://oracle-base.com/articles/19c/automatic-indexing-19c https://oracle-base.com/articles/19c/automatic-indexing-19c [2]: https://www.percona.com/blog/2019/07/22/automatic-index-recommendations-in-postgresql-using-pg_qualstats-and-hypopg/ https://www.percona.com/blog/2019/07/22/automatic-index-reco...
- no-s 6y ago>> The performance cost of an extra index for insert/update/delete queries is only a concern when you get to the point where you do need to manually manage indexes - but for most database tables with less a few million rows there’s no reason for the server not to automatically add indexes on columns used in predicates. It won’t be perfect, but it’s better than allowing slow queries to always be slow. You'd be surprised. The thing that really drives index explosion is the number of distinct queries. IME, inspecting and reducing distinct queries is one of the easiest ways to optimize and it can typically be automated. I've migrated systems from millions of distinct queries down to hundreds or dozens. However the objective of the optimization is typically driven by requirements external to the database and may vary over time. Thus difficult to align with an optimizer.
- Akronymus 6y agoDistinct as in doing different things or distinct as in prefer batching? My English seems to be failing me here.