RE: [PHP-DB] Large Table - Indexed - Select Time

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: matt@SpryNewMedia.com -----Original Message----- From: Miles Thompson [mailto:milesthompson@ns.sympatico.ca] Sent: Sunday December 2, 2001 2:53 PM To: Matt Friedman; php-db@lists.php.net 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: matt@SpryNewMedia.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: php-db-unsubscribe@lists.php.net For additional commands, e-mail: php-db-help@lists.php.net To contact the list administrators, e-mail: php-list-admin@lists.php.net -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: php-db-unsubscribe@lists.php.net For additional commands, e-mail: php-db-help@lists.php.net To contact the list administrators, e-mail: php-list-admin@lists.php.net