Re: [sqlite] Question about database design

[Jay Kreibich]

On Feb 3, 2011, at 3:38 AM, Simon Slavin  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 same 
> column, so it might need just one.  But every table has at least one index.

The ROWID of a table doesn't have an "index," so it isn't really fair to say 
that every table has at least one index.  At least, not when using the word 
"index" to mean a secondary data structure in the database that must be updated 
and maintained in conjunction with the table data structure.

Both tables and indexes are stored within the SQLite file as a tree structure.  
The raw table data is stored in a tree structure that happens to be sorted by 
ROWID.  For every index, SQLite creates an addition tree structure that is 
sorted by whatever columns make up the index.  The structures are extremely 
similar, since they serve the same purpose.

Both explicit indexes (CREATE INDEX...), and implicit indexes (non-integer 
PRIMARY KEYs, UNIQUE constraints) create additional tree structures in the 
database.  However, if no PK is defined, or if the PK is defined as an INTEGER 
PRIMARY KEY, then no external index is created.  The only data structure is the 
table itself.

This is also why INTEGER PRIMARY KEYs are so desirable over other PK types.  
Not only do they reduce database size, by using the inherent structure of the 
main table data store as their "index," they also tend to be  about twice as 
fast at doing explicit row lookups (vs a traditional index), as only one tree 
structure needs to be searched to retrieve any column.  That speed difference 
means the break-even point for indexed lookup vs table scan for a set of  
INTEGER PK values is closer to 15% to 20% of the rows, vs the traditional 5% to 
10% rule of thumb that applies to standard indexes.

 -j



> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about database design

[Simon Slavin]

On 3 Feb 2011, at 10:43am, Mihai Militaru wrote:

> Nicolas Williams  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, it doesn't.  Use EXPLAIN QUERY PLAN to see what SQLite3 is doing.
> 
> Hmm SqLite does create persistent indices on UNIQUE - and consequently
> PRIMARY - keys, doesn't it?

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 same column, so it 
might need just one.  But every table has at least one index.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about database design

[Mihai Militaru]

On Wed, 2 Feb 2011 18:59:48 -0600
Nicolas Williams  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, it doesn't.  Use EXPLAIN QUERY PLAN to see what SQLite3 is doing.

Hmm SqLite does create persistent indices on UNIQUE - and consequently
PRIMARY - keys, doesn't it?

-- 
Mihai Militaru 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about database design

[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 like that:
> 
> 

You want to know something hilarious?  Your space saving mechanism uses
*more* space than the empty columns would consume.  By my calculations 150%
additional space for the example above!

Some databases have a storage scheme where if you declare a column
"char(256)" then 256 bytes of storage are consumed even if you store nothing
in that column.  If you are used to that then empty columns would indeed add
up to a lot.

SQLite does not work that way.  Look up its type affinity documentation
(link below) to see.  Every record has a header followed by the values for
that record.  The header entries indicates the type (and for blob/string the
length) of the corresponding value.

A record with 100 columns where every single one contains NULL or a zero
length string would occupy 100 bytes of record header and zero bytes of
record values.  Note that an old school database would be substantially
similar even if it recorded the type information for the table rather than
the record since it would still need a per record way of indicating whether
a value is null versus the declared type.  (ie it would need at least one
bit per value, so in this case would need around 16 bytes.)

Using your example above, you have 6 columns.  Storing them as 6 columns in
SQLite with the two numbers would occupy 22 bytes for both the record header
and 2 non-empty values.

Storing it as one column with that hackery consumes 54 bytes.  Heck your
empty storage is 37 bytes compared to 6 empty SQLite columns being 6 bytes.
 The only time your scheme would actually save space is if all values were
empty and you stored a zero length string in which case you'd use 1 byte for
the column instead of 6 bytes for 6 empty ones.

The important lesson here is you should always do measurements first to
confirm your beliefs as they may not actually be stupid!  And secondly as
everyone else pointed out, you aren't the first person wanting to store lots
of information in a database and there are best practises such as
normalization that help address your needs if you seek them out.  It is
never stupid to ask!  (Or read a book, or web sites etc)

SQLite type information:

  http://en.wikipedia.org/wiki/Database_normalization

SQLite file format - record:

  http://www.sqlite.org/fileformat.html#record_format

Schema normalization:

  http://en.wikipedia.org/wiki/Database_normalization

SQLite books:

  http://www.sqlite.org/books.html

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEUEARECAAYFAk1KCYcACgkQmOOfHg372QRC1gCfbaE7tCvNKFKNkq9N14tSTOzh
S30Al3Tosxpwa4qPvzqXFxeNptxfwPs=
=Jrag
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about database design

[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
> >>attributes.  My timing comparisons with postgres show sqlite to be as
> >>much as 10x-15x slower than pg.
> >>
> >>My timing code is at http://paste.tclers.tk/2346
> >
> >You need an index on props(id) -- you always need an index on columns
> >that form a foreign key.  SQLite3 requires that if you want to cascade
> >deletions.
> 
> Ok, that caught me by surprise, but it improved things a lot.  With
> that index sqlite is now significantly faster than pg on 3 of the
> tests, but still similar to or slower than pg on the remaining 2 -
> in my code, "sx" and "sx3".   (I can't be sure of the exact timings
> because I can't run sqlite and the pg server on the same machine)
> 
> 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, it doesn't.  Use EXPLAIN QUERY PLAN to see what SQLite3 is doing.

Also, see the followup about covering indexes.  Also, run ANALYZE.

Finally, you might denormalize somewhat by having a trigger to copy the
obj.name column to the props table and add that column to the _end_ of
the covering indexes.  Then SQLite3 might be able to do your joins using
a single index.

Nico
-- 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about database design

[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 the index 
around and reuse it any number of times, at the expense of using up disk space 
and memory to store the index.  SQLite is better designed for small light 
systems and has to place minimal load on disk space and memory, so it doesn't 
keep indexes around between uses.

What it comes down to is that if you use SQLite you have to work out what 
indexes are worthwhile for yourself whereas Postgres does some of the work for 
you but takes up more memory and disk space to do so.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about database design

[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 as
>> much as 10x-15x slower than pg.
>>
>> My timing code is at http://paste.tclers.tk/2346
>
> You need an index on props(id) -- you always need an index on columns
> that form a foreign key.  SQLite3 requires that if you want to cascade
> deletions.

Ok, that caught me by surprise, but it improved things a lot.  With that 
index sqlite is now significantly faster than pg on 3 of the tests, but 
still similar to or slower than pg on the remaining 2 - in my code, "sx" 
and "sx3".   (I can't be sure of the exact timings because I can't run 
sqlite and the pg server on the same machine)

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?

> Without that index your joins will use full table scans.
>
> Also, it's better to use FROM table1 t1 JOIN table2 t2 ON t1.col1 =
> t2.col2 than to put the join conditions in the WHERE clause, as that's
> much easier to read.  But I can see that you have JOINs using that
> un-indexed column.

I wrote this test code some time ago, I think originally against a 
database that didn't support JOIN syntax.  I would use that were I 
rewriting it now.  (I also don't remember exactly what conditions I was 
trying to exercise, but I think it was the case where two attributes 
each match a large set but the intersection of those sets is small)

Thanks,
-J

>
>> This is a synthetic test, but I ran across the issue in a real
>> application.  I'm not sure what else I can do do optimize the queries;
>> using a denormalized table is the only thing that seems to help.
>
> Add the missing index.
>
> Nico

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about database design

[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 ON props (id, pnam, pval);
CREATE INDEX pind ON props (pval, pname, id);

The first is for when you need to join on 'id' and want to extract
attribute/values.  The second is for when you want to search by
attribute/values and need to get rowids back.

Nico
-- 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about database design

[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 2/2/2011 3:23 PM, 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 you want to query against multiple
> attributes.  My timing comparisons with postgres show sqlite to be as
> much as 10x-15x slower than pg.
>
> My timing code is at http://paste.tclers.tk/2346
>
> This is a synthetic test, but I ran across the issue in a real
> application.  I'm not sure what else I can do do optimize the queries;
> using a denormalized table is the only thing that seems to help.
>
> -J
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about database design

[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 you want to query against multiple 
> attributes.  My timing comparisons with postgres show sqlite to be as 
> much as 10x-15x slower than pg.
> 
> My timing code is at http://paste.tclers.tk/2346

You need an index on props(id) -- you always need an index on columns
that form a foreign key.  SQLite3 requires that if you want to cascade
deletions.

Without that index your joins will use full table scans.

Also, it's better to use FROM table1 t1 JOIN table2 t2 ON t1.col1 =
t2.col2 than to put the join conditions in the WHERE clause, as that's
much easier to read.  But I can see that you have JOINs using that
un-indexed column.

> This is a synthetic test, but I ran across the issue in a real 
> application.  I'm not sure what else I can do do optimize the queries; 
> using a denormalized table is the only thing that seems to help.

Add the missing index.

Nico
-- 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about database design

[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 comparisons with postgres show sqlite to be as 
much as 10x-15x slower than pg.

My timing code is at http://paste.tclers.tk/2346

This is a synthetic test, but I ran across the issue in a real 
application.  I'm not sure what else I can do do optimize the queries; 
using a denormalized table is the only thing that seems to help.

-J
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about database design

[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 empty)
> customer_Phone2 (will almost always stay empty)
> 
> 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:
> 
> 

It makes more sense to create a property table for your rarely-used columns.  
Remove them from the main table and add another TABLE customer_Properties

customer_Id
property_name
property_value

So if you know two normally-empty phone numbers for a customer that would be 
two entries in the table.  NO reason why you shouldn't use the property table 
for any field which would normally stay empty, for example 
customer_AddressBlling if that is rarely used.

By the way, you did not mention a customer_id column for your main customer 
table.  It is extremely important that you have one, and relate other tables to 
that table using that, rather than the name.  Customers change their names (get 
married, get Knighted, etc.) and you don't want to have to race through the 
rest of your system changing primary keys.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about database design

[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 values
> again.
>
> Can I get an opinion on it?
> I have not found any good information on the number of columns and
> relationship between their count and speed and diskspace used.

It seems to me that you are looking for
http://en.wikipedia.org/wiki/Database_normalization

-- 
Andreas Kupries
Senior Tcl Developer
ActiveState, The Dynamic Language Experts

P: 778.786.1122
F: 778.786.1133
andre...@activestate.com
http://www.activestate.com
Get insights on Open Source and Dynamic Languages at www.activestate.com/blog
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about database design

[Duquette, William H (318K)]

On 2/2/11 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 like that:
> 
> 
> 
> 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 values
> again.
> 
> Can I get an opinion on it?

It'll work so long as you don't want to write queries that discriminate
based on the kind of phone number, or on the phone number content.  You
might want to query customers by area code, for example, or get all
customers with a cell phone number.

A third approach would be to add an additional table with three columns:

* customer_id
* phone_number_type
* phone_number

Then JOIN that to your customer table.  Saves space, and lets you do the
two kinds of query I mention above.

Will


> I have not found any good information on the number of columns and
> relationship between their count and speed and diskspace used.
> 
> Thank you very much!
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

--
Will Duquette -- william.h.duque...@jpl.nasa.gov
Athena Development Lead -- Jet Propulsion Laboratory
"It's amazing what you can do with the right tools."


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about database?

[John Stanton]

Concurrency is the number of simultaneous, independent users of the 
database, not the transaction rate.  That is the criterion I would use 
to select the appropriate tool.


Cesar David Rodas Maldonado wrote:
About the concurrence, i don't know... about 2000 queries per second... 
Wich

db engine will be good?... i have a server with 2 GB ram, AMD sempron
64bits,

On 7/12/06, John Stanton <[EMAIL PROTECTED]> wrote:



Jay gave you the clue.  You need to have software and hardware which
will address data beyond 32 bits, i.e. 64 bit file addressing.  The 64
bit file version  of all the tools you mention  should perform your job.
  Now you choose according to the scale of your application, Enterprise,
Department or Embedded.  Postgresql would work in the Enterprise and
Sqlite for an embedded application.

For a heavy duty application 64 bits hardware and OS would be the best.

Cesar David Rodas Maldonado wrote:
> I don't care about hardware... i will do cluster... but which DB motor
do
> think will be good for work with great velocity in select (sqlite,
> postgresql or mysql).
> ah.. i almost forgot... i will do a great number of select...
>
> On 7/12/06, Jay Sprenkle <[EMAIL PROTECTED]> wrote:
>
>>
>> 32 bits, the size of the key, only holds numbers up to 2 or 4 billion.
>> You said you have 8 billion rows.
>> You need a 64 bit version or to split up the data into many tables.
>>
>>
>> On 7/12/06, Cesar David Rodas Maldonado <[EMAIL PROTECTED]> wrote:
>> > So Jay... Do you think that i have to use a PC of 64 bits, or 32bits
pc
>> with
>> > clustering?
>> >
>> >
>> > On 7/12/06, Jay Sprenkle <[EMAIL PROTECTED]> wrote:
>> > >
>> > > > Hello to everybody!
>> > > >
>> > > > I want to ask to the people something... If i have a table with
>> about
>> > > > 8.000.000.000 registers, and i have to do a select in a numeric
row
>> that
>> > > > will have index. what db do you recommend to me for use?
>> > >
>> > > If you design the database correctly it should work, but I believe
>> > > that large a total number of records will not be addressable on
>> > > a personal computer. I think the 32 bit version is limited to 2^32
>> > > records.
>> > >
>> >
>> >
>>
>>
>> --
>> --
>> SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
>> http://www.reddawn.net/~jsprenkl/Sqlite
>>
>> Cthulhu Bucks!
>> http://www.cthulhubucks.com
>>
>

Re: [sqlite] Question about database?

[drh]

"Cesar David Rodas Maldonado" <[EMAIL PROTECTED]> wrote:
> Thanks for explain me that... so what do you think will be the solution?

Lots more RAM.  Enough to hold the entire database.

--
D. Richard Hipp   <[EMAIL PROTECTED]>

Re: [sqlite] Question about database?

[Cesar David Rodas Maldonado]

Richard Hipp :
Thanks for explain me that... so what do you think will be the solution?
what do you think about db in Clustering.




On 7/13/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:


"Cesar David Rodas Maldonado" <[EMAIL PROTECTED]> wrote:
> About the concurrence, i don't know... about 2000 queries per second...
Wich
> db engine will be good?... i have a server with 2 GB ram, AMD sempron
> 64bits,
>

You have 8 billion records and 2 billion bytes of ram.
So your data will not fit in cache.  That means it
will have to be read from disk.  Let's assume each
query does a single disk access.  Suppose you have
a very high-performance disk drive with a 5ms access
time.  That gives you 200 queries per second - 1/10th
of what you want.

200 queries per second is your speed-of-light.  No
software will allow you to go faster.  You'll have
to get better hardware.

--
D. Richard Hipp   <[EMAIL PROTECTED]>

Re: [sqlite] Question about database?

[drh]

"Cesar David Rodas Maldonado" <[EMAIL PROTECTED]> wrote:
> About the concurrence, i don't know... about 2000 queries per second... Wich
> db engine will be good?... i have a server with 2 GB ram, AMD sempron
> 64bits,
> 

You have 8 billion records and 2 billion bytes of ram.
So your data will not fit in cache.  That means it
will have to be read from disk.  Let's assume each
query does a single disk access.  Suppose you have
a very high-performance disk drive with a 5ms access
time.  That gives you 200 queries per second - 1/10th
of what you want.

200 queries per second is your speed-of-light.  No
software will allow you to go faster.  You'll have
to get better hardware.

--
D. Richard Hipp   <[EMAIL PROTECTED]>

Re: [sqlite] Question about database?

[Eduardo]

At 17:22 13/07/2006, you wrote:

About the concurrence, i don't know... about 2000 queries per second... Wich
db engine will be good?... i have a server with 2 GB ram, AMD sempron
64bits,


First, i'll try PostgreSQL, Firebird and at last (very last) MySQL. 
For commercial databases, Valentina and perhaps DB2.

Re: [sqlite] Question about database?

[Cesar David Rodas Maldonado]

About the concurrence, i don't know... about 2000 queries per second... Wich
db engine will be good?... i have a server with 2 GB ram, AMD sempron
64bits,

On 7/12/06, John Stanton <[EMAIL PROTECTED]> wrote:


Jay gave you the clue.  You need to have software and hardware which
will address data beyond 32 bits, i.e. 64 bit file addressing.  The 64
bit file version  of all the tools you mention  should perform your job.
  Now you choose according to the scale of your application, Enterprise,
Department or Embedded.  Postgresql would work in the Enterprise and
Sqlite for an embedded application.

For a heavy duty application 64 bits hardware and OS would be the best.

Cesar David Rodas Maldonado wrote:
> I don't care about hardware... i will do cluster... but which DB motor
do
> think will be good for work with great velocity in select (sqlite,
> postgresql or mysql).
> ah.. i almost forgot... i will do a great number of select...
>
> On 7/12/06, Jay Sprenkle <[EMAIL PROTECTED]> wrote:
>
>>
>> 32 bits, the size of the key, only holds numbers up to 2 or 4 billion.
>> You said you have 8 billion rows.
>> You need a 64 bit version or to split up the data into many tables.
>>
>>
>> On 7/12/06, Cesar David Rodas Maldonado <[EMAIL PROTECTED]> wrote:
>> > So Jay... Do you think that i have to use a PC of 64 bits, or 32bits
pc
>> with
>> > clustering?
>> >
>> >
>> > On 7/12/06, Jay Sprenkle <[EMAIL PROTECTED]> wrote:
>> > >
>> > > > Hello to everybody!
>> > > >
>> > > > I want to ask to the people something... If i have a table with
>> about
>> > > > 8.000.000.000 registers, and i have to do a select in a numeric
row
>> that
>> > > > will have index. what db do you recommend to me for use?
>> > >
>> > > If you design the database correctly it should work, but I believe
>> > > that large a total number of records will not be addressable on
>> > > a personal computer. I think the 32 bit version is limited to 2^32
>> > > records.
>> > >
>> >
>> >
>>
>>
>> --
>> --
>> SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
>> http://www.reddawn.net/~jsprenkl/Sqlite
>>
>> Cthulhu Bucks!
>> http://www.cthulhubucks.com
>>
>

Re: [sqlite] Question about database?

[John Stanton]

Jay gave you the clue.  You need to have software and hardware which 
will address data beyond 32 bits, i.e. 64 bit file addressing.  The 64 
bit file version  of all the tools you mention  should perform your job. 
 Now you choose according to the scale of your application, Enterprise, 
Department or Embedded.  Postgresql would work in the Enterprise and 
Sqlite for an embedded application.


For a heavy duty application 64 bits hardware and OS would be the best.

Cesar David Rodas Maldonado wrote:

I don't care about hardware... i will do cluster... but which DB motor do
think will be good for work with great velocity in select (sqlite,
postgresql or mysql).
ah.. i almost forgot... i will do a great number of select...

On 7/12/06, Jay Sprenkle <[EMAIL PROTECTED]> wrote:



32 bits, the size of the key, only holds numbers up to 2 or 4 billion.
You said you have 8 billion rows.
You need a 64 bit version or to split up the data into many tables.


On 7/12/06, Cesar David Rodas Maldonado <[EMAIL PROTECTED]> wrote:
> So Jay... Do you think that i have to use a PC of 64 bits, or 32bits pc
with
> clustering?
>
>
> On 7/12/06, Jay Sprenkle <[EMAIL PROTECTED]> wrote:
> >
> > > Hello to everybody!
> > >
> > > I want to ask to the people something... If i have a table with
about
> > > 8.000.000.000 registers, and i have to do a select in a numeric row
that
> > > will have index. what db do you recommend to me for use?
> >
> > If you design the database correctly it should work, but I believe
> > that large a total number of records will not be addressable on
> > a personal computer. I think the 32 bit version is limited to 2^32
> > records.
> >
>
>


--
--
SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

Re: [sqlite] Question about database?

[John Stanton]

Cesar David Rodas Maldonado wrote:

Hello to everybody!

I want to ask to the people something... If i have a table with about
8.000.000.000 registers, and i have to do a select in a numeric row that
will have index. what db do you recommend to me for use?

Thanxs (:D) people!


How many concurrent users?

Re: [sqlite] Question about database?

[Eduardo]

At 19:59 12/07/2006, you wrote:

32 bits, the size of the key, only holds numbers up to 2 or 4 billion.
You said you have 8 billion rows.
You need a 64 bit version or to split up the data into many tables.


On 7/12/06, Cesar David Rodas Maldonado <[EMAIL PROTECTED]> wrote:

So Jay... Do you think that i have to use a PC of 64 bits, or 32bits pc with
clustering?


None of both is needed, but a lot of ram and a fast hard disk (scsi 
15000 rpm or raid system), yes and pragma tweaking will help in get 
faster responses.



> > I want to ask to the people something... If i have a table with about
> > 8.000.000.000 registers, and i have to do a select in a numeric row that
> > will have index. what db do you recommend to me for use?
>
> If you design the database correctly it should work, but I believe
> that large a total number of records will not be addressable on
> a personal computer. I think the 32 bit version is limited to 2^32
> records.
>


From FAQ, question 10: http://www.sqlite.org/faq.html explains that 
the limit of rows in a table is (2^64)-1 and 64 bit compilation isn't 
needed. Also, there is a limit of 32 (or was 31) to the maximum 
number of databases and some other minor limits. Check documentation.



(10) Are there any known size limits to SQLite databases?

A database is limited in size to 2 tibibytes (241 bytes). That is a 
theoretical limitation. In practice, you should try to keep your 
SQLite databases below 100 gigabytes to avoid performance problems. 
If you need to store 100 gigabytes or more in a database, consider 
using an enterprise database engine which is designed for that purpose.


The theoretical limit on the number of rows in a table is 264-1, 
though obviously you will run into the file size limitation prior to 
reaching the row limit. A single row can hold up to 230 bytes of data 
in the current implementation. The underlying file format supports 
row sizes up to about 262 bytes.


There are probably limits on the number of tables or indices or the 
number of columns in a table or index, but nobody is sure what those 
limits are. In practice, SQLite must read and parse the original SQL 
of all table and index declarations everytime a new database file is 
opened, so for the best performance of 
sqlite3_open() it 
is best to keep down the number of declared tables. Likewise, though 
there is no limit on the number of columns in a table, more than a 
few hundred seems extreme. Only the first 31 columns of a table are 
candidates for certain optimizations. You can put as many columns in 
an index as you like but indexes with more than 30 columns will not 
be used to optimize queries.


The names of tables, indices, view, triggers, and columns can be as 
long as desired. However, the names of SQL functions (as created by 
the 
sqlite3_create_function() 
API) may not exceed 255 characters in length.





-
Useful Acronymous : FAQ = Frequently 'Answered' Questions   

Re: [sqlite] Question about database?

[Jay Sprenkle]

On 7/12/06, Nuno Lucas <[EMAIL PROTECTED]> wrote:

On 7/12/06, Jay Sprenkle <[EMAIL PROTECTED]> wrote:
> 32 bits, the size of the key, only holds numbers up to 2 or 4 billion.
> You said you have 8 billion rows.
> You need a 64 bit version or to split up the data into many tables.

I never needed this so I'm not fully sure, but isn't "INTEGER PRIMARY
KEY" on sqlite v3 (it was 32 bits on sqlite v2) a 64 bits number (if
needs to be, as it's "compressed" on store) ?


You may be right, I would have to check.

Re: [sqlite] Question about database?

[Nuno Lucas]

On 7/12/06, Jay Sprenkle <[EMAIL PROTECTED]> wrote:

32 bits, the size of the key, only holds numbers up to 2 or 4 billion.
You said you have 8 billion rows.
You need a 64 bit version or to split up the data into many tables.


I never needed this so I'm not fully sure, but isn't "INTEGER PRIMARY
KEY" on sqlite v3 (it was 32 bits on sqlite v2) a 64 bits number (if
needs to be, as it's "compressed" on store) ?

If it is, depending on the usage, maybe sqlite is just the thing
(instead of mysql or postgres), but when things get big only test
cases will tell.


Regards,
~Nuno Lucas

Re: [sqlite] Question about database?

[Jay Sprenkle]

On 7/12/06, Gussimulator <[EMAIL PROTECTED]> wrote:

Yeah and while we're at it, he could invest a few bucks on a couple twin
diesel generators, a battery room, and whatnot, a group of hookers to save
the night.


You forgot the beer and pizza delivery contract!

Re: [sqlite] Question about database?

[Cesar David Rodas Maldonado]

Gussimulator No entendi tu mensaje, podrias porfavor escribirmelo en español

On 7/12/06, Gussimulator <[EMAIL PROTECTED]> wrote:


Yeah and while we're at it, he could invest a few bucks on a couple twin
diesel generators, a battery room, and whatnot, a group of hookers to save
the night.


- Original Message -
From: "Jay Sprenkle" <[EMAIL PROTECTED]>
To: 
Sent: Wednesday, July 12, 2006 3:15 PM
Subject: Re: [sqlite] Question about database?


> On 7/12/06, Cesar David Rodas Maldonado <[EMAIL PROTECTED]> wrote:
>> I don't care about hardware... i will do cluster... but which DB motor
do
>> think will be good for work with great velocity in select (sqlite,
>> postgresql or mysql).
>> ah.. i almost forgot... i will do a great number of select...
>
> I don't believe it makes a big difference on performance.
> Have you thought about backups and availability?

Re: [sqlite] Question about database?

[Gussimulator]

Yeah and while we're at it, he could invest a few bucks on a couple twin 
diesel generators, a battery room, and whatnot, a group of hookers to save 
the night.



- Original Message - 
From: "Jay Sprenkle" <[EMAIL PROTECTED]>

To: 
Sent: Wednesday, July 12, 2006 3:15 PM
Subject: Re: [sqlite] Question about database?



On 7/12/06, Cesar David Rodas Maldonado <[EMAIL PROTECTED]> wrote:

I don't care about hardware... i will do cluster... but which DB motor do
think will be good for work with great velocity in select (sqlite,
postgresql or mysql).
ah.. i almost forgot... i will do a great number of select...


I don't believe it makes a big difference on performance.
Have you thought about backups and availability? 

Re: [sqlite] Question about database?

[Cesar David Rodas Maldonado]

Yeah.

I was reading a few of Mysql replications and stuff like that.

Thanks for you help Jay

On 7/12/06, Jay Sprenkle <[EMAIL PROTECTED]> wrote:


On 7/12/06, Cesar David Rodas Maldonado <[EMAIL PROTECTED]> wrote:
> I don't care about hardware... i will do cluster... but which DB motor
do
> think will be good for work with great velocity in select (sqlite,
> postgresql or mysql).
> ah.. i almost forgot... i will do a great number of select...

I don't believe it makes a big difference on performance.
Have you thought about backups and availability?

Re: [sqlite] Question about database?

[Jay Sprenkle]

On 7/12/06, Cesar David Rodas Maldonado <[EMAIL PROTECTED]> wrote:

I don't care about hardware... i will do cluster... but which DB motor do
think will be good for work with great velocity in select (sqlite,
postgresql or mysql).
ah.. i almost forgot... i will do a great number of select...


I don't believe it makes a big difference on performance.
Have you thought about backups and availability?

Re: [sqlite] Question about database?

[Cesar David Rodas Maldonado]

I don't care about hardware... i will do cluster... but which DB motor do
think will be good for work with great velocity in select (sqlite,
postgresql or mysql).
ah.. i almost forgot... i will do a great number of select...

On 7/12/06, Jay Sprenkle <[EMAIL PROTECTED]> wrote:


32 bits, the size of the key, only holds numbers up to 2 or 4 billion.
You said you have 8 billion rows.
You need a 64 bit version or to split up the data into many tables.


On 7/12/06, Cesar David Rodas Maldonado <[EMAIL PROTECTED]> wrote:
> So Jay... Do you think that i have to use a PC of 64 bits, or 32bits pc
with
> clustering?
>
>
> On 7/12/06, Jay Sprenkle <[EMAIL PROTECTED]> wrote:
> >
> > > Hello to everybody!
> > >
> > > I want to ask to the people something... If i have a table with
about
> > > 8.000.000.000 registers, and i have to do a select in a numeric row
that
> > > will have index. what db do you recommend to me for use?
> >
> > If you design the database correctly it should work, but I believe
> > that large a total number of records will not be addressable on
> > a personal computer. I think the 32 bit version is limited to 2^32
> > records.
> >
>
>


--
--
SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

Re: [sqlite] Question about database?

[Jay Sprenkle]

32 bits, the size of the key, only holds numbers up to 2 or 4 billion.
You said you have 8 billion rows.
You need a 64 bit version or to split up the data into many tables.


On 7/12/06, Cesar David Rodas Maldonado <[EMAIL PROTECTED]> wrote:

So Jay... Do you think that i have to use a PC of 64 bits, or 32bits pc with
clustering?


On 7/12/06, Jay Sprenkle <[EMAIL PROTECTED]> wrote:
>
> > Hello to everybody!
> >
> > I want to ask to the people something... If i have a table with about
> > 8.000.000.000 registers, and i have to do a select in a numeric row that
> > will have index. what db do you recommend to me for use?
>
> If you design the database correctly it should work, but I believe
> that large a total number of records will not be addressable on
> a personal computer. I think the 32 bit version is limited to 2^32
> records.
>





--
--
SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

Re: [sqlite] Question about database?

[Cesar David Rodas Maldonado]

So Jay... Do you think that i have to use a PC of 64 bits, or 32bits pc with
clustering?


On 7/12/06, Jay Sprenkle <[EMAIL PROTECTED]> wrote:


> Hello to everybody!
>
> I want to ask to the people something... If i have a table with about
> 8.000.000.000 registers, and i have to do a select in a numeric row that
> will have index. what db do you recommend to me for use?

If you design the database correctly it should work, but I believe
that large a total number of records will not be addressable on
a personal computer. I think the 32 bit version is limited to 2^32
records.

Re: [sqlite] Question about database?

[Jay Sprenkle]

Hello to everybody!

I want to ask to the people something... If i have a table with about
8.000.000.000 registers, and i have to do a select in a numeric row that
will have index. what db do you recommend to me for use?


If you design the database correctly it should work, but I believe
that large a total number of records will not be addressable on
a personal computer. I think the 32 bit version is limited to 2^32 records.