Re: table test

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(at)ontko(dot)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(at)postgresql(dot)org
> > [mailto:pgsql-admin-owner(at)postgresql(dot)org]On Behalf Of Manuel Trujillo
> > Sent: Friday, November 30, 2001 3:47 AM
> > To: Nick Fankhauser
> > Cc: pgsql-admin(at)postgresql(dot)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 entenda la pregunta. Puede usted exponer su propsito en forma
> > > modificada o decirnos ms 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(at)dorna(dot)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"