Hello. I'm pretty much an SQL newbie, so please bear with me.
I have a performance problem after normalizing a table. 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 query to display the data: SELECT id, category, weight FROM products ORDER BY category, weight The table has around 1.4m entries and the SELECT took around 1.5 seconds. This was good. 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 (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
