is_empty() should not ORDER BY

[Jeroen T. Vermeulen]

Caling is_empty on a query wraps the entire query text in a select that's limited to one row. Then it can check whether one row was returned or not.

But the original query may contain an ordering clause, in which case finding the first row may still involve executing the whole query and sorting it. So it'd be nice to skip the ORDER BY.

Why not wrap the query in a "SELECT NOT EXISTS(...)"? That way there's a better chance of the database knowing to forget about the ordering. And it simply returns the boolean you want, so it also obviates the transfer of the row's data.

[James Henstridge (jamesh)]

Looks like the same issue as bug 246200 (although that one was about the SQLObject compatibility layer). We should do something about it.

The "SELECT NOT EXISTS (subquery)" looks like it works for MySQL and SQLite too, so is probably worth looking into. I wonder how it compares with the current code minus orderings?