Re: [Bug 12321] New: Add compound keys to IndexedDB

On Fri, Mar 18, 2011 at 12:27 PM, Aryeh Gregor <Simetrical+w3c@gmail.com>wrote:

> On Thu, Mar 17, 2011 at 10:03 PM, Jeremy Orlow <jorlow@chromium.org>
> wrote:
> > Here's one ugliness with A: There's no way to specify ascending
> > or descending for the individual components of the key.  So there's no
> way
> > for me to open a cursor that looks at one field ascending and the other
> > field descending.  In addition, I can't think of any easy/good ways to
> hack
> > around this.
> > Any thoughts on how we could address this use case?
>
> For what it's worth, the way MySQL does it is it doesn't.  If you have
> an index on (a, b), then it can be used for ORDER BY a, b or ORDER BY
> a DESC, b DESC, but not ORDER BY a DESC, b or ORDER BY a, b DESC.  In
> practice this usually works fine -- it's pretty rare that you really
> want to sort different columns in a different order.
>

Most SQL engines (Postgresql, SQLite) support "CREATE INDEX idx ON tbl (date
DESC, name ASC)" .  This allows "ORDER BY date DESC, name ASC" (eg. "newest
events first, events per date sorted by name") and its reverse, "name DESC,
date ASC".  MySQL is an outlier in not supporting this.

-- 
Glenn Maynard