[thelist] Very Large MySQL Table

[Stephen Rider]

Off the top of my head -- SQL gurus feel free to point and laugh.

You could also index it with a simple 26-digit number -- each digit is  
the number of times a particular letter appears (up to 9, anyway).

Then it's a single index.  To check for the frequency of "e", look at  
the 5th digit... and so forth.

Or go binary -- 0s or 1s simply indicate "contains this letter" or not.

Then you could have a separate index of the alphabet based on  
frequency within the English language, and based on that, search the  
*least* common letters first.  If searching for the string "quaker",  
it would look for "q" first -- not because it's the first in the  
string, but because it's the least common letter.  if it's not present  
move on to the second least-common.

This allows you to eliminate candidates without grepping the whole  
string -- you're just pulling particular digits (by position) out of  
the index number.

Stephen

On Feb 10, 2009, at 8:42 AM, Luther, Ron wrote:

> Jason Handby asked:
>
>> sadly, mysql can use only one index for each table in a query
>
>>> Hmmm that sucks... Ah well. Anyone else have any ideas? I'm  
>>> intrigued.
>
>
> If you index the 'big' table numerically ... just a counter on each  
> record?  Then you can implement your idea by constructing 26 side  
> tables.  The "A" table would contain the index reference number for  
> entries that start with the letter 'A', etc.