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.
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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
