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. 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. 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. _______________________________________________ Wikitech-l mailing list Wikitech-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikitech-l
