>This is interesting, but my first thought is then I'd have a second table with 40 million records (the length of our field is 80), in addition to the original table with 500K records. Seems excessive to be able to search a single field. :)
That is correct. It is not only excessive to be able to search: you are also passing the 2 GB limit. Perhaps, instead of copying 80 characters, you might copy the first 10-20 characters. Still excessive.
But I don't know of another approach to roptimize your query. And I believe that tools like phdbase have to use a similar approach, except that phdbase only creates one index entry for each word.
Difference in opinions hath cost many millions of lives: for instance, whether flesh be bread, or bread be flesh; whether whistling be a vice or a virtue; whether it be better to kiss a post, or throw it into the fire... (from Gulliver's Travels)
