Erik Jensen wrote: > First i had a single flat table, that looked like this: > > CREATE TABLE products ( > id INTEGER NOT NULL PRIMARY KEY, > category TEXT NOT NULL DEFAULT '', > weight INTEGER NOT NULL DEFAULT 0 > ); > CREATE INDEX product_index1 ON products (category); > CREATE INDEX product_index2 ON products (category,weight);
The first index is likely to be superfluous. > 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 Indeed. Do you really need to sort the categories alphabetically? Is anybody going to read this list of 1.4M entries? Try using SQLite 3.8.5, which can do partial sorting with an index: <http://www.sqlite.org/queryplanner.html#partialsort>. If the list of categories does not change, you could assign the category IDs so that they have the same order as the names. Regards, Clemens _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
