On 4 Jul 2014, at 8:06am, Erik Jensen <double...@web.de> wrote: > 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);
This is excellent and there's nothing wrong with it. I will just note that you have no need for the index 'product_index1'. If SQLite needs to sort products by category it will notice that the second index allows it to do this, and use that index. You can save some time and disk space. You might also run the ANALYZE command (just once, the results are saved with the database) which allows SQLite to inspect how 'chunky' each of your columns is, and each of your indexes is, and optimize its searching and sorting strategies accordingly. > 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? The new form of your query requires that SQL do lots of extra searching. Try this SELECT products.id, category.name, products.weight FROM products JOIN categories ON categories.id = products.category_id ORDER BY category.name, products.weight I haven't tried it but I think it'll help. If not, please post again. Lastly, as tips from a very old database admin, irrelevant to your query, (1) Define name fields (product name too, if there is one) as COLLATE NOCASE UNIQUE. This means you don't have to guard against double-entry or keep defining sort orders as NOCASE in the rest of your definitions. (2) Do not allow a category name to be empty. Invent a 'Miscellaneous' or 'Other' category and use that as default. If it's important that this category be sorted first, start the name with a space character like ' Divers'. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
