Re: [sqlite] Performace problem with SELECT after normalization

2014-07-04 Thread Erik Jensen
Hello Clemens, Friday, July 4, 2014, 1:43:47 PM, you wrote: CL> Erik Jensen wrote: >> > If the list of categories does not change, you could assign the >> > 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 the

Re: [sqlite] Performace problem with SELECT after normalization

2014-07-04 Thread Clemens Ladisch
Erik Jensen wrote: > > If the list of categories does not change, you could assign the > > 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

Re: [sqlite] Performace problem with SELECT after normalization

2014-07-04 Thread Simon Slavin
On 4 Jul 2014, at 8:06am, Erik Jensen 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 PRIMA

Re: [sqlite] Performace problem with SELECT after normalization

2014-07-04 Thread Erik Jensen
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 DEF

Re: [sqlite] Performace problem with SELECT after normalization

2014-07-04 Thread Keith Medcalf
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 DEF

Re: [sqlite] Performace problem with SELECT after normalization

2014-07-04 Thread Clemens Ladisch
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 produc

[sqlite] Performace problem with SELECT after normalization

2014-07-04 Thread Erik Jensen
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