What does EXPLAIN QUERY PLAN say about your query? Run ANALYZE What does EXPLAIN QUERY PLAN say about your query now?
product_index1 is just a waste of space, by the way. >So i split it up into two tables: > >CREATE TABLE categories ( > id INTEGER NOT NULL PRIMARY KEY, > name TEXT NOT NULL DEFAULT '' >); >CREATE INDEX category_index1 ON categories (name); > >CREATE TABLE products ( > id INTEGER NOT NULL PRIMARY KEY, > category_id INT NOT NULL DEFAULT 0, > weight INTEGER NOT NULL DEFAULT 0 >); >CREATE INDEX product_index1 ON products (category_id); >CREATE INDEX product_index2 ON products (category_id,weight); > >And the new query: > >SELECT products.id, category.name, products.weight > FROM products, categories > WHERE products.category_id = categories.id > ORDER BY category.name, products.weight > >This now takes 12 seconds. The problem is probably that i don't have a >compound index for the ORDER BY clause now (category.name, >products.weight). But as they're in different tables, i can't have one >(or can I?). > >Is there a solution for this? > >Thanks in advance. >Eric > >_______________________________________________ >sqlite-users mailing list >sqlite-users@sqlite.org >http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
