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

Hi Miles, Thanks so much for your input. 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? 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. I do see what you mean about storing phone numbers as char fields. This makes perfect sense to me. 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