Re: 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