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]

Reply via email to