Hello Clemens, thanks for your response.
>> But in 1.4m entries, the "category" field only had around 7000 >> different entries, this was a big waste of space. >> >> 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 CL> Indeed. CL> Do you really need to sort the categories alphabetically? Unfortunately, yes. CL> Try using SQLite 3.8.5, which can do partial sorting with an index: CL> <http://www.sqlite.org/queryplanner.html#partialsort>. I tried with 3.8.5 (was using 3.7.15 before), but it even seemed a little slower (around 1 sec). Yes, i recreated the database from scratch. CL> If the list of categories does not change, you could assign the CL> category IDs so that they have the same order as the names. Yes, that was my idea too if there is no other easy solution. Is there an easy/elegant way to re-order the "category" table and update the "products" table with the new category_ids? Thanks, Eric _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
