Wouldn't creating completely unique keys for every animal be a waste of good
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
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.
29 GunMuse Lane
P.O. box 166
Lakewood NM 88254
469 228 2183
From: Kim Briggs [mailto:patiodragon@stripped]
Sent: Saturday, April 16, 2005 10:23 PM
To: David Blomstrom
Subject: Re: Relative Numeric Values
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?
On 4/16/05, David Blomstrom <david_blomstrom@stripped> 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
> 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
> 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!
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=1