Обсуждение: table test
Hi. I need to know howto make a test for know the best accuracy of two types of tables: 1- a table with three fields, and with seven registers or more, and 2- a table with ten fields, and two registers. Like my language is very poor, I illustrate this: Table One: Table Two: 1 | 2 | 3 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | --------------- ----------------------------------------- 1|__|____|____| 1 ___|___|___|___|___|___|___|___|___|____| 2|__|____|____| 2 ___|___|___|___|___|___|___|___|___|____| . . . 7|__|____|____| How is the best way to know the better type of table of this two types? Thank you very much! Have a nice day ;-) TooManySecrets -- Manuel Trujillo manueltrujillo@dorna.es Technical Engineer http://www.motograndprix.com Dorna Sports S.L. +34 93 4702864
On Wed, Nov 28, 2001 at 10:53:45AM +0100, Manuel Trujillo wrote: Please, anybody can help me with this???? :((( > Hi. > > I need to know howto make a test for know the best accuracy of two types of tables: > 1- a table with three fields, and with seven registers or more, and > 2- a table with ten fields, and two registers. > Like my language is very poor, I illustrate this: > > Table One: Table Two: > 1 | 2 | 3 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | > --------------- ----------------------------------------- > 1|__|____|____| 1 ___|___|___|___|___|___|___|___|___|____| > 2|__|____|____| 2 ___|___|___|___|___|___|___|___|___|____| > . > . > . > 7|__|____|____| > > > How is the best way to know the better type of table of this two types? > > Thank you very much! > > Have a nice day ;-) > TooManySecrets > > -- > Manuel Trujillo manueltrujillo@dorna.es > Technical Engineer http://www.motograndprix.com > Dorna Sports S.L. +34 93 4702864 > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) -- Manuel Trujillo manueltrujillo@dorna.es Technical Engineer http://www.motograndprix.com Dorna Sports S.L. +34 93 4702864
On Thu, Nov 29, 2001 at 09:20:09AM -0600, Eric Johnson wrote: > We cannot help you unless you specify 'better for what'. Either > arrangement can be fine depending on what you are doing. Is for "a web site proposals". I only want to know what type is more fastest. Any field contain BLOB's, only character data. Is only to know if, for table design, is more fastest one or another... Sorry if I can't explain more, but my english is very bad, and the information I know, is this :( -- Manuel Trujillo manueltrujillo@dorna.es Technical Engineer http://www.motograndprix.com Dorna Sports S.L. +34 93 4702864
I did not understand the question. Can you restate your purpose or tell us more about what you are trying to achieve? (No entendía la pregunta. ¿Puede usted exponer su propósito en forma modificada o decirnos más sobre lo que usted está intentando alcanzar?) -Nick (with the aid of http://www.google.com/language_tools) (con la ayuda de http://www.google.com/language_tools) > -----Original Message----- > From: pgsql-admin-owner@postgresql.org > [mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Manuel Trujillo > Sent: Thursday, November 29, 2001 9:50 AM > To: pgsql-admin@postgresql.org > Subject: Re: [ADMIN] table test > > > On Wed, Nov 28, 2001 at 10:53:45AM +0100, Manuel Trujillo wrote: > > Please, anybody can help me with this???? :((( > > > Hi. > > > > I need to know howto make a test for know the best accuracy of > two types of tables: > > 1- a table with three fields, and with seven registers or more, and > > 2- a table with ten fields, and two registers. > > Like my language is very poor, I illustrate this: > > > > Table One: Table Two: > > 1 | 2 | 3 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | > > --------------- ----------------------------------------- > > 1|__|____|____| 1 ___|___|___|___|___|___|___|___|___|____| > > 2|__|____|____| 2 ___|___|___|___|___|___|___|___|___|____| > > . > > . > > . > > 7|__|____|____| > > > > > > How is the best way to know the better type of table of this two types? > > > > Thank you very much! > > > > Have a nice day ;-) > > TooManySecrets > > > > -- > > Manuel Trujillo manueltrujillo@dorna.es > > Technical Engineer http://www.motograndprix.com > > Dorna Sports S.L. +34 93 4702864 > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 2: you can get off all lists at once with the unregister command > > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > > -- > Manuel Trujillo manueltrujillo@dorna.es > Technical Engineer http://www.motograndprix.com > Dorna Sports S.L. +34 93 4702864 > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >
His original post is hard to understand. I guess the registers he said are records. But what type of a table does he want to know. table type? Is there a table type? Bangh Nick Fankhauser wrote: > I did not understand the question. Can you restate your purpose or tell us > more about what you are trying to achieve? > > (No entend�a la pregunta. �Puede usted exponer su prop�sito en forma > modificada o decirnos m�s sobre lo que usted est� intentando alcanzar?) > > -Nick (with the aid of http://www.google.com/language_tools) > (con la ayuda de http://www.google.com/language_tools) > > > -----Original Message----- > > From: pgsql-admin-owner@postgresql.org > > [mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Manuel Trujillo > > Sent: Thursday, November 29, 2001 9:50 AM > > To: pgsql-admin@postgresql.org > > Subject: Re: [ADMIN] table test > > > > > > On Wed, Nov 28, 2001 at 10:53:45AM +0100, Manuel Trujillo wrote: > > > > Please, anybody can help me with this???? :((( > > > > > Hi. > > > > > > I need to know howto make a test for know the best accuracy of > > two types of tables: > > > 1- a table with three fields, and with seven registers or more, and > > > 2- a table with ten fields, and two registers. > > > Like my language is very poor, I illustrate this: > > > > > > Table One: Table Two: > > > 1 | 2 | 3 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | > > > --------------- ----------------------------------------- > > > 1|__|____|____| 1 ___|___|___|___|___|___|___|___|___|____| > > > 2|__|____|____| 2 ___|___|___|___|___|___|___|___|___|____| > > > . > > > . > > > . > > > 7|__|____|____| > > > > > > > > > How is the best way to know the better type of table of this two types? > > > > > > Thank you very much! > > > > > > Have a nice day ;-) > > > TooManySecrets > > > > > > -- > > > Manuel Trujillo manueltrujillo@dorna.es > > > Technical Engineer http://www.motograndprix.com > > > Dorna Sports S.L. +34 93 4702864 > > > > > > ---------------------------(end of broadcast)--------------------------- > > > TIP 2: you can get off all lists at once with the unregister command > > > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > > > > -- > > Manuel Trujillo manueltrujillo@dorna.es > > Technical Engineer http://www.motograndprix.com > > Dorna Sports S.L. +34 93 4702864 > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster
On Thu, Nov 29, 2001 at 12:12:04PM -0500, Nick Fankhauser wrote: > I did not understand the question. Can you restate your purpose or tell us > more about what you are trying to achieve? > > (No entendía la pregunta. ¿Puede usted exponer su propósito en forma > modificada o decirnos más sobre lo que usted está intentando alcanzar?) Yes. We are try to achieve fields of "varchar" (with indeterminate lenght, my boss is too bit explicit). He only want to know, how is the fastest access to this table; with the manner like "Table One", or the "Table Two". Each table have only "varchar characters", about to 20-50 characters of maximum lenght (I would like to think :(). > > > Table One: Table Two: > > > 1 | 2 | 3 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | > > > --------------- ----------------------------------------- > > > 1|__|____|____| 1 ___|___|___|___|___|___|___|___|___|____| > > > 2|__|____|____| 2 ___|___|___|___|___|___|___|___|___|____| > > > . > > > . > > > . > > > 7|__|____|____| > > > > > > > > > How is the best way to know the better type of table of this two types? Thank you very much. Have a nice day ;-) TooManySecrets -- Manuel Trujillo manueltrujillo@dorna.es Technical Engineer http://www.motograndprix.com Dorna Sports S.L. +34 93 4702864
Manuel- I'm still a bit confused, but will try to answer (maybe someone else can jump in with a better interpretation). Generally, the structure of the information you are storing should determine the structure of the tables, so if there are three distinct fields in the data, table one would be best, but if there are seven distinct fields, table two would be best. Regarding speed, fewer records (rows), will always result in faster access to the *record*, but if you then have to search among the fields (columns) within each record to get to the bit of data you need, then the gain is lost. That is why you should usually make the database structure closely match the information to be stored. This foundation starts you with the best situation. This is true regardless of the datatypes of the fields. There are probably a few exceptions, but this rule is true for most situations. The nature of your question has more to do with table structure than database administration, so I'd suggest going to the pgsql-sql or pgsql-general lists in the future- you might get better answers there. -Nick -------------------------------------------------------------------------- Nick Fankhauser nickf@ontko.com Phone 1.765.935.4283 Fax 1.765.962.9788 Ray Ontko & Co. Software Consulting Services http://www.ontko.com/ > -----Original Message----- > From: pgsql-admin-owner@postgresql.org > [mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Manuel Trujillo > Sent: Friday, November 30, 2001 3:47 AM > To: Nick Fankhauser > Cc: pgsql-admin@postgresql.org > Subject: Re: [ADMIN] table test > > > On Thu, Nov 29, 2001 at 12:12:04PM -0500, Nick Fankhauser wrote: > > I did not understand the question. Can you restate your purpose > or tell us > > more about what you are trying to achieve? > > > > (No entendía la pregunta. ¿Puede usted exponer su propósito en forma > > modificada o decirnos más sobre lo que usted está intentando alcanzar?) > > Yes. We are try to achieve fields of "varchar" (with > indeterminate lenght, my boss is too bit explicit). > He only want to know, how is the fastest access to this table; > with the manner like "Table One", or the "Table Two". > Each table have only "varchar characters", about to 20-50 > characters of maximum lenght (I would like to think :(). > > > > > Table One: Table Two: > > > > 1 | 2 | 3 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | > > > > --------------- ----------------------------------------- > > > > 1|__|____|____| 1 ___|___|___|___|___|___|___|___|___|____| > > > > 2|__|____|____| 2 ___|___|___|___|___|___|___|___|___|____| > > > > . > > > > . > > > > . > > > > 7|__|____|____| > > > > > > > > > > > > How is the best way to know the better type of table of > this two types? > > Thank you very much. > > Have a nice day ;-) > TooManySecrets > > -- > Manuel Trujillo manueltrujillo@dorna.es > Technical Engineer http://www.motograndprix.com > Dorna Sports S.L. +34 93 4702864 > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html >
On Fri, 30 Nov 2001, Nick Fankhauser wrote: Actually, I suspect that accessing data in Table 1 might be faster. If they are gonna create the table like such: create table foo1 (f1 varchar(100), f2 varchar(100), f3 varchar(100)) ; Because of the variable length of each field, the record would have a length from 0 to 300 bytes (data only). Depending on how they wish to index this, it has been my experience (backed I believe by something in the documentation), that indexes on the first varchar are okay. Any field after the first varchar that you want to index takes a serious performance hit. I have noticed that since I re-arranged my schema to put most varchar data into separate tables performance has increased. I also arrange my fields in a table so that the varchar data is at the end of a record, whenever it doesn't make sense to create a separate table. For best performance (based on my past experience), if they want to use any kind of an index on the various fields, and assuming that all the fields will contain the same "type" of data, I would do the following: create bar (f1 varchar(100)) ; This also eliminates stupid SQL like: SELECT * FROM foo WHERE f1 like '%baz%' OR f2 like '%baz%' OR f3 like '%baz%' ; And then having to figure out which of the fields actually contains your data. - brian > Manuel- > > I'm still a bit confused, but will try to answer (maybe someone else can > jump in with a better interpretation). > > Generally, the structure of the information you are storing should determine > the structure of the tables, so if there are three distinct fields in the > data, table one would be best, but if there are seven distinct fields, table > two would be best. > > Regarding speed, fewer records (rows), will always result in faster access > to the *record*, but if you then have to search among the fields (columns) > within each record to get to the bit of data you need, then the gain is > lost. That is why you should usually make the database structure closely > match the information to be stored. This foundation starts you with the best > situation. > > This is true regardless of the datatypes of the fields. There are probably a > few exceptions, but this rule is true for most situations. > > The nature of your question has more to do with table structure than > database administration, so I'd suggest going to the pgsql-sql or > pgsql-general lists in the future- you might get better answers there. > > -Nick > > -------------------------------------------------------------------------- > Nick Fankhauser nickf@ontko.com Phone 1.765.935.4283 Fax 1.765.962.9788 > Ray Ontko & Co. Software Consulting Services http://www.ontko.com/ > > > > > -----Original Message----- > > From: pgsql-admin-owner@postgresql.org > > [mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Manuel Trujillo > > Sent: Friday, November 30, 2001 3:47 AM > > To: Nick Fankhauser > > Cc: pgsql-admin@postgresql.org > > Subject: Re: [ADMIN] table test > > > > > > On Thu, Nov 29, 2001 at 12:12:04PM -0500, Nick Fankhauser wrote: > > > I did not understand the question. Can you restate your purpose > > or tell us > > > more about what you are trying to achieve? > > > > > > (No entend�a la pregunta. �Puede usted exponer su prop�sito en forma > > > modificada o decirnos m�s sobre lo que usted est� intentando alcanzar?) > > > > Yes. We are try to achieve fields of "varchar" (with > > indeterminate lenght, my boss is too bit explicit). > > He only want to know, how is the fastest access to this table; > > with the manner like "Table One", or the "Table Two". > > Each table have only "varchar characters", about to 20-50 > > characters of maximum lenght (I would like to think :(). > > > > > > > Table One: Table Two: > > > > > 1 | 2 | 3 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | > > > > > --------------- ----------------------------------------- > > > > > 1|__|____|____| 1 ___|___|___|___|___|___|___|___|___|____| > > > > > 2|__|____|____| 2 ___|___|___|___|___|___|___|___|___|____| > > > > > . > > > > > . > > > > > . > > > > > 7|__|____|____| > > > > > > > > > > > > > > > How is the best way to know the better type of table of > > this two types? > > > > Thank you very much. > > > > Have a nice day ;-) > > TooManySecrets > > > > -- > > Manuel Trujillo manueltrujillo@dorna.es > > Technical Engineer http://www.motograndprix.com > > Dorna Sports S.L. +34 93 4702864 > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 5: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/users-lounge/docs/faq.html > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > Wm. Brian McCane | Life is full of doors that won't open Search http://recall.maxbaud.net/ | when you knock, equally spaced amid those Usenet http://freenews.maxbaud.net/ | that open when you don't want them to. Auction http://www.sellit-here.com/ | - Roger Zelazny "Blood of Amber"
On Thu, 29 Nov 2001, Manuel Trujillo wrote:
> > How is the best way to know the better type of table of this two types?
> >
> > Thank you very much!
We cannot help you unless you specify 'better for what'. Either
arrangement can be fine depending on what you are doing.
> >
> > Have a nice day ;-)
> > TooManySecrets
> >
> > --
-- Eric Johnson
"... man will occasionally stumble over the truth,
but usually manages to pick himself up,
walk over or around it, and carry on"
-- Winston Churchill