I did this at a previous job, and we split the data up more or less this way (we used a pre-existing item number for the split which was essentially random in relation to the text data), with a aggregator that did the query X ways, each to a separate box holding 1/X of the data. The results from each unit were paged and sorted, so all the aggregator did was do a simple merge sort on a "page" of the set, which was fast. On a 6M record dataset, it produced millisecond-range search results. Not exactly Google-class, but pretty good for 12 Linux boxes, two programmers, and about six weeks of effort.
james montebello On Thu, 7 Feb 2002, Brian Bray wrote: > > It seems to me like the best solution that could be implemented as-is > would be to keep a random int column in your table (with a range of say > 1-100) and then have fulltext server 1 psudo-replicate records with a > the random number in the range of 1-10, server 2 11-20 and server 3 > 21-30 and so on. > > Then run your query on all 10 servers and merge the result sets and > possibly re-sort them if you use the score column. > > The problem with splitting the index up by word is that is messes up all > your scoring and ranking. For example what if you search using 5 > keywords, all starting with letters from different groups? Your going > to get pretty bad score for each match, and it could totally break > boolean searches. > > -- > Brian Bray > > > > > Brian DeFeyter wrote: > > On Thu, 2002-02-07 at 15:40, Tod Harter wrote: > > [snip] > > > >>Wouldn't be too tough to write a little query routing system if you are using > >>perl. Use DBD::Proxy on the web server side, and just hack the perl proxy > >>server so it routes the query to several places and returns a single result > >>set. Ordering could be achieved as well. I'm sure there are commercial > >>packages out there as well. I don't see why the individual database servers > >>would need to do anything special. > >> > > [snip] > > > > If I'm understanding you correctly, I think you're refering to routing > > based on the first character of the word. That would work for cases > > where the query is searching for a word that begins with a certain > > character.. however fulltext searches also return results with the term > > in the middle. > > > > ie: a search for 'foo' could return: > > foo.txt > > foobar > > > > but also could return: > > thisisfoo > > that_is_foolish > > > > I could be wrong, but it's my understanding that MySQL stores it's > > fulltext index based on all the 'unique words' found. For such a system > > as you mentioned above, you'd probably have to create your own fulltext > > indexing system to determine: a) where to store the data 'segments' and > > b) how to route queries. It seems like this could probably be done much > > more efficiently inside of the server. > > > > - Brian > > > > > > > > --------------------------------------------------------------------- > > Before posting, please check: > > http://www.mysql.com/manual.php (the manual) > > http://lists.mysql.com/ (the list archive) > > > > To request this thread, e-mail <[EMAIL PROTECTED]> > > To unsubscribe, e-mail <[EMAIL PROTECTED]> > > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > > > > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
