Comments interspersed ....
At 05:36 PM 12/2/2001 -0600, Matt Friedman wrote:
>Thanks so much for your input.
>I am interested in the idea of using char fields rather than integer
>fields. Generally for any primary key field in a database I use an
>unsigned integer field auto-incremented. I don't think you are
>suggesting otherwise, are you?
No, particularly when the database has a built-in engine to do so. Although
a friend of mine devised a base 256 function, using the whole ascci table,
(If I remember correctly) for primary keys which he figured would never repeat.
>I don't think it was clear in my original post that the table was a
>relationship table consisting of one primary key field and the others
>were foreign keys based on the primary keys from other tables.
Yes, well then it makes sense. My background is FoxPro / Visual FoxPro, so
I tend to think in terms first of all, and it doesn't allow one to mix
integer and char keys in a query.
>I do see what you mean about storing phone numbers as char fields. This
>makes perfect sense to me.
I was not happy with that FileMaker developer.
>Web Applications Developer
>Email: [EMAIL PROTECTED]
>From: Miles Thompson [mailto:[EMAIL PROTECTED]]
>Sent: Sunday December 2, 2001 2:53 PM
>To: Matt Friedman; [EMAIL PROTECTED]
>Subject: Re: [PHP-DB] Large Table - Indexed - Select Time
>A few things:
> Indexes are fastest means of accessing data in a relational
> Have the indexes you need for your queries, and no more,
>building/adding to the index takes time as well.
> Length of the index key value also affects speed - longer
>expression equates to longer key.
> Don't drag back all the information if you just need part of
> Be aware that one unindexed search expression forces a table
>- VERY SLOW.
>Finally, I see that you are planning to use integer fields. Is this
>you will have to arithmetic on the id's, or just because they are
>These are often best stored as chars for long term flexibility, and
>frequently to avoid a lot of type conversion/manipulation in your code.
>I was once given the job of converting a FileMaker Pro database to MSFT
>Access. One of the tables contained European phone numbers, you know,
>ones with the built-in hyphens. Of course there was no direct export
>so the data was exported via a comma separated values (.csv) file.
>Everything looked OK in the csv, so I imported to Access.
>No matter what I did, Access insisted on doing arithmetic on importing
>phone numbers as numeric, and evaluating them. Some came out as simply
>large numbers, a few were quite small, or negative, and a small minority
>were in exponential notation. I worked around that by importing the csv
>into FoxPro, opening the header and amending the field type, and then
>exporting to Access.
>A good rule of thumb is to stick with char type keys if at all possible.
>Anyway, have fun!
>At 02:28 AM 12/2/2001 -0600, Matt Friedman wrote:
> >Say you have a table that could potentially be several thousand rows or
> >more (lots) and you need to do a select based on an indexed field or a
> >group of indexed fields.
> >For example, let's say the table has the following fields:
> >content_id pKey
> >object_id indexed integer field
> >this_id indexed integer field
> >another_id indexed integer field
> >The select might look like:
> > *
> > theTable
> > object_id = 1
> > this_id = 2
> > another_id = 5;
> >Is there a size of table where the selects would become slow? Or does
> >the indexing of each of the fields take care of the problem? I am
> >concerned about the table becoming several hundred of thousands of rows
> >big. I'm using mysql and/or postgresql.
> >Matt Friedman
> >Web Applications Developer
> >Email: [EMAIL PROTECTED]
> >PHP Database Mailing List (http://www.php.net/)
> >To unsubscribe, e-mail: [EMAIL PROTECTED]
> >For additional commands, e-mail: [EMAIL PROTECTED]
> >To contact the list administrators, e-mail:
>PHP Database Mailing List (http://www.php.net/)
>To unsubscribe, e-mail: [EMAIL PROTECTED]
>For additional commands, e-mail: [EMAIL PROTECTED]
>To contact the list administrators, e-mail: [EMAIL PROTECTED]
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]