[thelist] Very Large MySQL Table

[Judah McAuley]

I wouldn't treat this as a database problem. This is a text search
problem and you should consider using a text search tool, like Verity.
There are other tools out there but that's the one I'm most familiar
with. The general terms to look at will probably be either text
indexing or lexical analysis.

Databases are set up for doing a number of different types of indexes
whereas a text search tool is geared just toward text indexing and
does the job much better. The things that Ron is trying to pull out of
his brain are phrases like "stem searching" and soundex. That will
give you "everything that starts with sign" like sign, signs,
signature, etc. and soundex will give you words that sound alike such
as color and colour.

Judah

On Tue, Feb 10, 2009 at 6:42 AM, Luther, Ron <Ron.Luther at hp.com> 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.
>
>
> 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.
>
> --
>
> * * Please support the community that supports you.  * *
> http://evolt.org/help_support_evolt/
>
> For unsubscribe and other options, including the Tip Harvester
> and archives of thelist go to: http://lists.evolt.org
> Workers of the Web, evolt !
>