Dominique Devienne wrote: > In the two queries below, there's a 5x performance difference.
> select max(id) from t100m limit 1; > select id from t100m order by id desc limit 1; > But logically, it seems to me that the limit 1 on the order by is logically > equivalent to a min or max depending on ascending or descending ordering, > and is "optimize-able" by using an appropriate transformation. SQLite's query optimizer does not implement this transformation, so the MAX can throw away any smaller records, while the ORDER BY does not know about the LIMIT and keeps all sorted records in a temporary table. This query is efficient only if there is an index that allows the database to look up the largest value without touching any other records. > This use case is not as bogus as it seems. We have tree UI components, > where the child nodes of a tree node is determined by a query, with some of > these "child queries" having order by clauses. The UI needs to know if the > parent node is expendable or not, and for that we currently add the limit 1 > clause to the "child query", but the limit 1 is applied *after* the > ordering (or after the union all, see below), which forces getting all the > children rows and columns, a major performance issue. This sounds as if you're working on the wrong abstraction level. I would redesign the code so that tree nodes can return another query. Alternatively, if you can afford to confuse your users, allow all nodes to be expandable, and remove the [+] only when an actual attempt to expand returns no records. > Also note that quite a few of our queries are "union" or "union all" > queries, joining rows from several different tables, so in this specific > use case, any query returning a row would be enough to stop executing the > other queries and without the need to union all the inner result sets. Why "inner"? Are you using a subqueries? In that case, read <http://www.sqlite.org/optoverview.html#flattening>. Regards, Clemens _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
