On Mon, Feb 3, 2014 at 5:30 PM, Joseph L. Casale <jcas...@activenetwerx.com>wrote:
> I have a query where if I hard code the results of the nested SELECT > DICTINCT to a few > static values, it completes very fast. Leaving the select causes this > query to slow down > badly. Running an explain query plan wasn't obvious with my weak sql > experience. > > Is the nested query not evaluated only once? > No. It appears to be a correlated subquery. It depends on the current row of the "d" table (diffset) because of the "ON r.guid_id=did" term and thus has to be reevalatued for every row of the "d" table. > > SELECT > a.value, > COUNT(*) total, > SUM(CASE WHEN r.status IS NULL THEN 1 ELSE 0 END) > unprocessed, > SUM(CASE WHEN r.status='processed' THEN 1 ELSE 0 END) > processed, > SUM(CASE WHEN r.status='error' THEN 1 ELSE 0 END) error > FROM diffset d > JOIN request r > ON r.guid_id=d.id > JOIN action a > ON a.req_id=r.id > AND a.key='operationType' > AND a.value IN ( > SELECT DISTINCT(a.value) > FROM action a > JOIN request r > ON r.guid_id=d.id > WHERE a.key='operationType' > ) > WHERE d.id=? > GROUP BY value > > Thanks, > jlc > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
