3 ms·
In other words, superoptimization for SQL queries? That's a neat idea. I don't know that anyone has done that before. It's dependent on the RDBMS, the schema
by ken 8y ago
In other words, superoptimization for SQL queries? That's a neat idea. I don't know that anyone has done that before.
It's dependent on the RDBMS, the schema, and the data (or at least the engine's current stats about the data). The good news, though, is that if you could extract just the stats that your RDBMS engine knows about your data, that could be a pretty small bit of metadata to send over the wire to an army of CPUs. You wouldn't need to send actual database tables over the wire (which would be big and slow, and probably a security red flag).
I once worked on a similar problem which also amounted to "enumerate every possibility in N-dimensional space to find the good ones". It was surprising to me how well this worked in practice. Starting with literally every possible solution and then chopping off obviously bad branches by hand will get you to a 90% solution pretty quick.
This seems like an entirely tractable problem.
- Tarean 8y agoRDBMS do query optimization by trying out different plans and picking the best via cost estimations. With prepared statements you can reuse that optimization so the cost is (kinda) amortized. If you want something deeper you'd need an optimizing sql compiler. The closest I know is DSH (database supported haskell) which translates haskell comprehensions into reaonably optimized sql. The name is a nod to data parallel haskell which used the same flattening transformation for automatic parallelization of comprehensions. https://db.inf.uni-tuebingen.de/staticfiles/publications/the-flatter-the-better.pdf https://db.inf.uni-tuebingen.de/staticfiles/publications/the... Though this system can't come up with answers like 'add an index', 'split this table' or 'add sharding' so for the complex cases it doesn't really help.
- magicalhippo 8y ago> Though this system can't come up with answers like 'add an index', 'split this table' or 'add sharding' so for the complex cases it doesn't really help. And that's the answer we ended up with. Adding a couple of materialized views with indexes, and rewriting the original query to utilize the materialized views.