5 ms·

A "query optimizer", let's call it. That'd be so cool. I wish RDBMSs came with query optimizers. Maybe something that could ANALYZE (or, better yet, EXPLAIN) t

A "query optimizer", let's call it. That'd be so cool. I wish RDBMSs came with query optimizers. Maybe something that could ANALYZE (or, better yet, EXPLAIN) their performance, so people could tune it. And yeah, sarcasm aside, it's easier to have a DBA tune things than some AI - of course: "Why can't we just have a compiler that gets fed a description of the problem, then solves it, calculating the optimal solutions for all of the engineering trade-offs, and then builds whatever kind of interfaces are optimal for your users choosing Design Patterns as appropriate. Is this something anyone is working on?"

derefr 16y ago
Yes, RDBMSes already have the capability to tell you how to best optimize them. The problem—and it's a sociological problem, not a technological problem—is that people don't want to tune their databases. Instead, they want to install something that is already tuned 80% correctly (i.e. a NoSQL store), and then ignore the other 20% of possible optimization. There's a big, unaddressed opportunity here: if you can just make your DB smart enough to tune itself 81% correctly (and that's not very hard—certainly not AI hard) then you can conquer the market for people who don't know how to EXPLAIN their way out of a paper bag. It's not like it would be inventing its schema on the fly; you'd still be feeding it a formal, completely-specified but unoptimized set of tables. It would just do things like: * Notice when it gets a query with the same parameters 10000x in a row, and grind out an index for it; * Notice when a large (>100000) set of rows in a table is identical save for their primary key, and only queried by COUNT(foo), and turn those rows into a single row with a foo_count field (and a log table in case a query ever does come in that cares about the IDs); * Notice when two tables are only ever queried joined together, and morph the primary store for them into a denormalized table, turning the previous, separate tables into views on the denormalized table. And so on. If you look at, say, Python's hashtable library, it has all sorts of these little "rules of thumb" for when to alter its internal representation, because of what it guesses the user is up to (storing unique pairs, creating mathematical sets, nesting hashes to mimic a complex data structure, etc.) Databases, on the other hand, much bigger systems purely for flexibly storing and querying data, just stubbornly sit there until the DBA (because there must always be a DBA, even if the DB is just being fed migrations written by Rails CRUD coders) comes along to tell them to do the things they already know would make them faster. It's just silly.
- jasonkester 16y ago
  Are you really saying that people are too lazy to draw lines between tables in their database? Too lazy to right click a column name and click "Index"? That you wouldn't notice on your own that a particular query took four minutes to run and maybe open it up and poke around? That this should be something the database does for you??? That is not the hard part of my job. That is the easy part. No thank you.
  - derefr 16y ago
    But, as you say, it is part of your job. The difficulty, or ease, of the problem, has nothing to do with it; at some shops, it's nobody's job, and so it just doesn't get done. In 90% of shops, the last time anyone so much as glances at the database is to run the CREATE TABLE query. No one profiles, no one optimizes—you're lucky if anyone even declares any indexes (ahead of time, as a guess, of course.) And, consider: if it's the easy part, why are [some] people switching to NoSQL just to avoid doing it? ;)
    - jasonkester 16y ago
      I think you misunderstood me. Part of writing the CREATE TABLE query you mention is hooking up primary and foreign keys and setting up indexes. That's just how you build databases. Even if you never look at it again, you still need to do it.
    - kunley 16y ago
      I agree with your points and ideas of auto-optimization. I'd just replace "at some shops" with "at most shops" to reflect reality a bit more.
    - wvenable 16y ago
      The most frightening part of this debate is that people who are too lazy to properly define indexes are considering NoSQL solutions (and most of those require indexes too)! Properly utilizing a NoSQL requires more work than an RDMS, that's trade off for using a more low-level technology.
mistermann 16y ago
Haha, classic.

derefr 16y ago

Yes, RDBMSes already have the capability to tell you how to best optimize them. The problem—and it's a sociological problem, not a technological problem—is that people don't want to tune their databases. Instead, they want to install something that is already tuned 80% correctly (i.e. a NoSQL store), and then ignore the other 20% of possible optimization. There's a big, unaddressed opportunity here: if you can just make your DB smart enough to tune itself 81% correctly (and that's not very hard—certainly not AI hard) then you can conquer the market for people who don't know how to EXPLAIN their way out of a paper bag. It's not like it would be inventing its schema on the fly; you'd still be feeding it a formal, completely-specified but unoptimized set of tables. It would just do things like: * Notice when it gets a query with the same parameters 10000x in a row, and grind out an index for it; * Notice when a large (>100000) set of rows in a table is identical save for their primary key, and only queried by COUNT(foo), and turn those rows into a single row with a foo_count field (and a log table in case a query ever does come in that cares about the IDs); * Notice when two tables are only ever queried joined together, and morph the primary store for them into a denormalized table, turning the previous, separate tables into views on the denormalized table. And so on. If you look at, say, Python's hashtable library, it has all sorts of these little "rules of thumb" for when to alter its internal representation, because of what it guesses the user is up to (storing unique pairs, creating mathematical sets, nesting hashes to mimic a complex data structure, etc.) Databases, on the other hand, much bigger systems purely for flexibly storing and querying data, just stubbornly sit there until the DBA (because there must always be a DBA, even if the DB is just being fed migrations written by Rails CRUD coders) comes along to tell them to do the things they already know would make them faster. It's just silly.

jasonkester 16y ago
Are you really saying that people are too lazy to draw lines between tables in their database? Too lazy to right click a column name and click "Index"? That you wouldn't notice on your own that a particular query took four minutes to run and maybe open it up and poke around? That this should be something the database does for you??? That is not the hard part of my job. That is the easy part. No thank you.
- derefr 16y ago
  But, as you say, it is part of your job. The difficulty, or ease, of the problem, has nothing to do with it; at some shops, it's nobody's job, and so it just doesn't get done. In 90% of shops, the last time anyone so much as glances at the database is to run the CREATE TABLE query. No one profiles, no one optimizes—you're lucky if anyone even declares any indexes (ahead of time, as a guess, of course.) And, consider: if it's the easy part, why are [some] people switching to NoSQL just to avoid doing it? ;)
  - jasonkester 16y ago
    I think you misunderstood me. Part of writing the CREATE TABLE query you mention is hooking up primary and foreign keys and setting up indexes. That's just how you build databases. Even if you never look at it again, you still need to do it.
  - kunley 16y ago
    I agree with your points and ideas of auto-optimization. I'd just replace "at some shops" with "at most shops" to reflect reality a bit more.
  - wvenable 16y ago
    The most frightening part of this debate is that people who are too lazy to properly define indexes are considering NoSQL solutions (and most of those require indexes too)! Properly utilizing a NoSQL requires more work than an RDMS, that's trade off for using a more low-level technology.

mistermann 16y ago

Haha, classic.