Re: [sqlite] Question about database design

2011-02-03 Thread Mihai Militaru
On Wed, 2 Feb 2011 18:59:48 -0600 Nicolas Williams nicolas.willi...@oracle.com wrote: Any idea why pg does ok on these queries without the extra index - Maybe they're created by default? SQLIte doesn't create any indexes automatically on primary key fields or anything else, correct? No,

Re: [sqlite] Question about database design

2011-02-03 Thread Simon Slavin
On 3 Feb 2011, at 10:43am, Mihai Militaru wrote: Nicolas Williams nicolas.willi...@oracle.com wrote: Any idea why pg does ok on these queries without the extra index - Maybe they're created by default? SQLIte doesn't create any indexes automatically on primary key fields or anything else,

Re: [sqlite] Question about database design

2011-02-03 Thread Jay Kreibich
On Feb 3, 2011, at 3:38 AM, Simon Slavin slav...@bigfraud.org wrote: SQLite creates some indexes the programmer doesn't specifically ask for: on the rowid, on the primary key, and on any column declared as UNIQUE. Of course, in a particular table all three of these might actually be the

[sqlite] Question about database design

2011-02-02 Thread Bert Nelsen
Hello! I have over 100 columns in a table. Most of the columns will stay empty. Just an example: customer_lastname customer_firstname customer_street customer_PhonePrivate (will almost always stay empty) customer_PhoneCompany customer_PhoneMobile customer_PhoneWAP (will almost always stay empty)

Re: [sqlite] Question about database design

2011-02-02 Thread Duquette, William H (318K)
On 2/2/11 11:48 AM, Bert Nelsen bert.nel...@googlemail.com wrote: Because I felt so stupid about these mostly empty columns taking so much space, I tended to replace all the phone columns by a single column named customerPhone. I stored the values into customerPhone like that:

Re: [sqlite] Question about database design

2011-02-02 Thread Andreas Kupries
On 2/2/2011 11:48 AM, Bert Nelsen wrote: Hello! I have over 100 columns in a table. Most of the columns will stay empty. Just an example: [example elided] This is kind of an xml design, but it works and it takes up less space. I decompile this weird expression at runtime to get the separate

Re: [sqlite] Question about database design

2011-02-02 Thread Simon Slavin
On 2 Feb 2011, at 7:48pm, Bert Nelsen wrote: customer_lastname customer_firstname customer_street customer_PhonePrivate (will almost always stay empty) customer_PhoneCompany customer_PhoneMobile customer_PhoneWAP (will almost always stay empty) customer_Phone1 (will almost always stay

Re: [sqlite] Question about database design

2011-02-02 Thread Jeff Rogers
Andreas Kupries wrote: It seems to me that you are looking for http://en.wikipedia.org/wiki/Database_normalization SQLite seems to do quite poorly performance-wise with fully-normalized attribute tables like this, when you want to query against multiple attributes. My timing

Re: [sqlite] Question about database design

2011-02-02 Thread Nicolas Williams
On Wed, Feb 02, 2011 at 03:23:49PM -0800, Jeff Rogers wrote: Andreas Kupries wrote: It seems to me that you are looking for http://en.wikipedia.org/wiki/Database_normalization SQLite seems to do quite poorly performance-wise with fully-normalized attribute tables like this, when

Re: [sqlite] Question about database design

2011-02-02 Thread Jim Morris
I'd probably move the analyze out of the loop. Since your joining on props.id a better index pind might be create index pind on props (id, pnam) The name of column id in table props would be clearer as obj_id since it is not the id of the property but the id of the record in the obj table. On

Re: [sqlite] Question about database design

2011-02-02 Thread Nicolas Williams
On Wed, Feb 02, 2011 at 03:38:07PM -0800, Jim Morris wrote: I'd probably move the analyze out of the loop. Since your joining on props.id a better index pind might be create index pind on props (id, pnam) Yes, you probably want two covering or partially-covering indexes: CREATE INDEX pind

Re: [sqlite] Question about database design

2011-02-02 Thread Jeff Rogers
Nicolas Williams wrote: On Wed, Feb 02, 2011 at 03:23:49PM -0800, Jeff Rogers wrote: SQLite seems to do quite poorly performance-wise with fully-normalized attribute tables like this, when you want to query against multiple attributes. My timing comparisons with postgres show sqlite to be

Re: [sqlite] Question about database design

2011-02-02 Thread Simon Slavin
On 3 Feb 2011, at 12:45am, Jeff Rogers wrote: Any idea why pg does ok on these queries without the extra index - Maybe they're created by default? Both PostgreSQL and SQLite will make indexes on the fly if that's the most efficient way of scanning the table. However, PostgreSQL can keep

Re: [sqlite] Question about database design

2011-02-02 Thread Nicolas Williams
On Wed, Feb 02, 2011 at 04:45:16PM -0800, Jeff Rogers wrote: Nicolas Williams wrote: On Wed, Feb 02, 2011 at 03:23:49PM -0800, Jeff Rogers wrote: SQLite seems to do quite poorly performance-wise with fully-normalized attribute tables like this, when you want to query against multiple

Re: [sqlite] Question about database design

2011-02-02 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/02/2011 11:48 AM, Bert Nelsen wrote: Because I felt so stupid about these mostly empty columns taking so much space, I tended to replace all the phone columns by a single column named customerPhone. I stored the values into customerPhone