The blob is fairly small but is referenced very often, so it wouldn't be feasible to move it to another table. Does the blob make a difference in this case? I thought that since the blob column is to the right of the column being summed that it would never get read during the summation.
Thanks -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of John Machin Sent: Tuesday, March 03, 2009 7:53 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Slow performance with Sum function On 4/03/2009 5:52 AM, Trainor, Chris wrote: > I am trying to use the Sum function on a column in a table with ~450K > rows in it. > > Select sum(Col4) from Table1 > > Where Table1 looks like this: > > Create TABLE Table1 ( > Col1 INTEGER NOT NULL, > Col2 INTEGER NOT NULL, > Col3 INTEGER NOT NULL, > Col4 BIGINT NOT NULL, > Col5 BIGINT NOT NULL, > Col6 BLOB NOT NULL, What is the min/max/average size of this blob and how often do you need to access it? If the answer tends towards "huge and rarely", consider putting it in a separate table. > Col7 CHAR(1) DEFAULT '0', > Col8 NUMERIC(2) NOT NULL, > Col9 NUMERIC(2) NOT NULL, > Col10 INTEGER NOT NULL, > Col11 INTEGER NOT NULL, > CONSTRAINT FK_1 FOREIGN KEY (Col1) REFERENCES Table2 (Col1) > ON DELETE CASCADE > ON UPDATE CASCADE, > CONSTRAINT PK_1 PRIMARY KEY (Col10, Col11, Col1, Col3 DESC) > ); _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users The information contained in this email message and its attachments is intended only for the private and confidential use of the recipient(s) named above, unless the sender expressly agrees otherwise. Transmission of email over the Internet is not a secure communications medium. If you are requesting or have requested the transmittal of personal data, as defined in applicable privacy laws by means of email or in an attachment to email you must select a more secure alternate means of transmittal that supports your obligations to protect such personal data. If the reader of this message is not the intended recipient and/or you have received this email in error, you must take no action based on the information in this email and you are hereby notified that any dissemination, misuse, copying, or disclosure of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by email and delete the original message. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
