Matt,
Comments interspersed ....
Miles
At 05:36 PM 12/2/2001 -0600, Matt Friedman wrote:
>Hi Miles,
>
>Thanks so much for your input.

You're welcome!

>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.


>Matt Friedman
>Web Applications Developer
>www.SpryNewMedia.com
>Email: [EMAIL PROTECTED]
>
>
>-----Original Message-----
>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
>
>Matt,
>
>A few things:
>          Indexes are fastest means of accessing data in a relational
>database.
>          Have the indexes you need for your queries, and no more,
>because
>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
>it.
>          Be aware that one unindexed search expression forces a table
>read
>- VERY SLOW.
>
>Finally, I see that you are planning to use integer fields. Is this
>because
>you will have to arithmetic on the id's, or just because they are
>numbers?
>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,
>the
>ones with the built-in hyphens. Of course there was no direct export
>route,
>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
>the
>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!
>
>Miles Thompson
>
>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:
> >
> >SELECT
> >         *
> >FROM
> >         theTable
> >WHERE
> >         object_id = 1
> >AND
> >         this_id = 2
> >AND
> >         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
> >www.SpryNewMedia.com
> >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:
>[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]


-- 
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]

Reply via email to