Wouldn't creating completely unique keys for every animal be a waste of good indexing?
It doesn't matter if you 30 or 3000 animals start with the same 3 letters if your using a 3 letter key for speed. As long as you avoided searching through 30,000,000 records. The method you described is of no speed benefit. If you have 30,000,000 records and wind up with 30,000 keys as a result that is a speed improvement. Putting 30,000,000 keys in there all your doing is limiting the amount of characters searched and not the records searched. Indexing everything only slows the MySql down. Stick with your original plan, and reduce to 2 characters for your index if the speed isn't what your looking for still, or throw hardware at the problem at that point. That will reduce the number of records for the first glance at the index. My search engine on a small dual Xeon runs through 1.7 million records with a 2 letter index for keywords in about .2 seconds It only has 8142 keys in the 2 letter index. And I am crawling about 8000 pages a day adding content without seeing a speed drop at this point. When we get to a point of bottlenecking on searches I intend to make a index jumping call. Find * where 2 letter index equals 'ab' and 3 letter index equals 'abcd' I am sure there will be a better way to write that because at that time I am certain abcd may reside on different servers. Thanks Donny Lairson President 29 GunMuse Lane P.O. box 166 Lakewood NM 88254 http://www.gunmuse.com 469 228 2183 -----Original Message----- From: Kim Briggs [mailto:[EMAIL PROTECTED] Sent: Saturday, April 16, 2005 10:23 PM To: David Blomstrom Cc: mysql@lists.mysql.com Subject: Re: Relative Numeric Values David, In reading through miscellaneous database design text on the web, I read just the other day that you should not try to include meaningful data in your key values. I assume there will be some kind of "lookup" tables for species, phylum, whatever. Trying to make your key field "smart" seems like way too much overhead and complexity. I'm wondering why, if the database is enormous, are you being so short and cryptic with the "user-friendly" values? my $.02 KB On 4/16/05, David Blomstrom <[EMAIL PROTECTED]> wrote: > I think my question is more oriented towards PHP, but > I'd like to ask it on this list, as I suspect the > solution may involve MySQL. > > I'm about to start developing an enormous database > focusing on the animal kingdom and want to find a key > system more user friendly than the traditional > scientific name. > > So imagine instead a page with the following in the > head section: > > $AnimalID = 'canlup'; > > This page displays information on the wolf, based on > the first three letters of its genus and species name, > Canis lupus. > > Now imagine a page with this value: > > $AnimalID = 'bal'; > > This page displays information on the whale family > Balaenidae. But what about the whale family > Balaenopteridae, which begins with the same three > letters? > > I could solve this problem by adding a numerical key > to my database and displaying the following: > > $AnimalID = 'bal23'; > $AnimalID = 'bal24'; > > The problem with this is that it makes it much harder > to work with my data. When tweaking a page or writing > a script, I can easily remember that bal = Balaenidae, > but I can't possibly remember which numeral is > associated with each mammal family. Also, what happens > if I add or subtract rows from my database table, and > the above values suddenly change to bal27 and bal28? > > So here's what I think I'd like to do: > > $AnimalID = 'canlup1'; > $AnimalID = 'bal1'; > $AnimalID = 'bal2'; > > The page with canlup1 will display the FIRST (and > only) instance of canlup in the database - the wolf. > > The page with bal1 will display the first instance of > bal, which will always be Balaenidae, whether the > absolute value is bal27 or bal2884. A page with bal2 > will always display the next mammal family that begins > with bal, Balaenopteridae. > > So I THINK all I need to do is create a variable that > reflects a particular value's ordinal position in a > database... > abc1 > abc2 > abc3, etc. > > Plus, I'll have to join two or three fields together > to form a key; e.g. animals.species + animals.numerals > > Does anyone know how I can do this? Thanks. > > __________________________________ > Do you Yahoo!? > Plan great trips with Yahoo! Travel: Now over 17,000 guides! > http://travel.yahoo.com/p-travelguide > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
