[thelist] Very Large MySQL Table

Luther, Ron Ron.Luther at hp.com
Tue Feb 10 08:42:53 CST 2009


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.


Hi Jason,

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.

I'm pretty sure that's one way to get out of that box.

When you want "A" stuff from the 'big' file, you join your "A" table to the 'big' table and run your query on that.


{It might help some, but (from very very very distant memory here) I'm (probably misremembering) that word length may be a better break point for dictionary work.  However, within each 'length' file you might index by starting character and/or character string [dipthongs? Spelling pattern recognition?] ... sometimes searching through "st..." is faster than searching through "s..."  That kinda stuff.}


HTH,
RonL.




More information about the thelist mailing list