Aryeh Gregor schreef: > On Wed, Jan 14, 2009 at 6:10 PM, Roan Kattouw <roan.katt...@home.nl> wrote: > >> So DB2 renames LIMIT $n to something else and doesn't even implement >> offset handling, even though both are in the SQL specification? >> > > LIMIT/OFFSET are completely nonstandard. See, e.g., this Google > result confirms this: > > http://troels.arvin.dk/db/rdbms/#select-limit > > FETCH FIRST n ROWS ONLY is the standard way, not LIMIT. A lot of > stuff that you take for granted in MySQL is completely nonstandard -- > REPLACE INTO... and INSERT IGNORE INTO... are two other major > examples. > I knew about those, yes, but I didn't know LIMIT/OFFSET was non-standard, even though it seems to be the more widely used variant. Of course if offset handling isn't implemented at all, that's something to worry about. > As a general rule, SQL seems to be such a terrible, slowly developed, > impractical standard that a typical app doesn't implement most of what > it says to do and implements a metric ton of things that it doesn't > mention. Which is why we need such a complicated abstraction layer in > the first place. Ideally we could just write in standard SQL with the > occasional minor workaround, the way we write standard HTML and CSS > and so on, but real life doesn't seem to work that way in this case. > > >> What do you mean DB2 "doesn't have them"? FORCE INDEX isn't supported in >> DB2? Then unless its index choosing algorithm is extremely good, it >> won't be able to run certain queries with satisfactory efficiency. >> > > My impression is that MySQL is completely stupid when it comes to > choosing indexes. It's pretty stupid at times, yes. > Most other serious databases are much smarter and > don't need babysitting. Our pgsql wrapper also doesn't implement > this. I don't think anything does other than DatabaseMysql. > > Index behavior is very database-dependent -- even if other DBs did > implement forcing a particular index, they probably wouldn't need to > force the same indexes in the same cases. They might not even *have* > the same indexes. I know our pgsql schema doesn't have the same > indexes as MySQL in most cases: it often can make do with fewer, > because of its ability to do things like take intersections of > multiple indexes efficiently, or even retrieve by one index and order > by another. > > >> You shouldn't rename indices like that, because index names are used in >> FORCE INDEX clauses (oh wait, but they weren't supported, right?) >> > > Right. pgsql also renames indexes (because as noted, it doesn't use > the same ones). I'd imagine the same is true for the other DBs we > support or semi-support. Ah, didn't know pgsql did those things too. I guess it's alright then, as long as performance stays acceptable.
Roan Kattouw (Catrope) _______________________________________________ Wikitech-l mailing list Wikitech-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikitech-l
