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]
