On Tue, Sep 17, 2013 at 9:26 AM, Adam Kopriva <[email protected]>wrote:
> Hi SQLite team, > > I have query with multiple select statements (attached below). When I run > this query on 3.8.0.2 sqlite3 it takes much more time than in took on same > db in 3.7.17. In 3.7.17 it takes 0.093 sec, In 3.8.0.2 it takes couple of > minutes. > > Database has three tables product (3000 rows), category (1000) and > product_category_join (300000), database has 15MB. > Please send: (1) The complete schema for your database (2) The content of the sqlite_stat1 table for you database, if that table exists (3) The output of the sqlite3_analyzer.exe utility (available on the SQLite website) after running it on your database. Thanks. > > > Thanks, > Adam Kopriva > > Query: > > SELECT product.product_id, product.name > FROM product, > ( > SELECT prod_cat.product_id, prod_cat.category_id > FROM product_category_join AS prod_cat, > ( > SELECT node.category_name, node.category_id > FROM category AS node, > category AS parent > WHERE node.lft BETWEEN parent.lft AND > parent.rgt > AND parent.category_id = 14 > ORDER BY node.lft > ) AS sub_categories > WHERE prod_cat.category_id = sub_categories.category_id > ) AS prod_cat1, > ( > SELECT prod_cat.product_id, prod_cat.category_id > FROM product_category_join AS prod_cat, > ( > SELECT node.category_name, node.category_id > FROM category AS node, > category AS parent > WHERE node.lft BETWEEN parent.lft AND > parent.rgt > AND parent.category_id = 2 > ORDER BY node.lft > ) AS sub_categories > WHERE prod_cat.category_id = sub_categories.category_id > ) AS prod_cat2, > ( > SELECT prod_cat.product_id, prod_cat.category_id > FROM product_category_join AS prod_cat, > ( > SELECT node.category_name, node.category_id > FROM category AS node, > category AS parent > WHERE node.lft BETWEEN parent.lft AND > parent.rgt > AND parent.category_id = 13 > ORDER BY node.lft > ) AS sub_categories > WHERE prod_cat.category_id = sub_categories.category_id > ) AS prod_cat3 > WHERE product.product_id = prod_cat1.product_id AND > product.product_id = prod_cat2.product_id AND > product.product_id = prod_cat3.product_id AND > product.product_id > 0 > ORDER BY product.product_id ASC LIMIT 100; > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp [email protected] _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
