On 3 May 2011, at 5:56pm, Paul Shaffer wrote: > This is a mapping table or descriptor table or whatever you want to call > it, between 2 tables (about 120,000 rows). I'm finding that operations are > very slow (delete, select, etc.) even within transactions. [snip] > > CREATE TABLE [Item_attribute] ( > [ItemID] integer NOT NULL, > [AttributeID] integer NOT NULL, > PRIMARY KEY ([ItemID], [AttributeID]), > FOREIGN KEY ([AttributeID]) > REFERENCES [Attribute]([AttributeID]) ON DELETE CASCADE ON UPDATE CASCADE, > FOREIGN KEY ([ItemID]) > REFERENCES [Item]([ItemID]) ON DELETE CASCADE ON UPDATE CASCADE > )
Excuse me, I'm stating some things for beginners, and you probably know them already. You absolutely need indexes on each of your FOREIGN KEY REFERENCES. So in this case you need an index on the 'AttributeID' column of TABLE Attribute, and an index on the ItemID column of TABLE Item. If those fields are the INTEGER PRIMARY KEYs, they will automatically have been indexed. You've already tried the thing I was going to suggest: making the primary key an INTEGER PRIMARY KEY rather than the compound which reflects your database design. Purely for testing, remove one of the FOREIGN KEY REFERENCES. If this dramatically speeds up your app, it's probably something about the primary keys you need to pay attention to. if not, maybe not. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
