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: [EMAIL PROTECTED] > > >-----Original Message----- >From: Miles Thompson [mailto:[EMAIL PROTECTED]] >Sent: Sunday December 2, 2001 2:53 PM >To: Matt Friedman; [EMAIL PROTECTED] >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: [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] -- 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]
