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,
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,
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
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)
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:
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
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
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
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
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
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
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
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
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
-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
15 matches
Mail list logo