Hi, On Mon, 30 May 2005, Gavin McCullagh wrote:
1. Add FULLTEXT indexes to each of the searched columns (author,title,...) 2. Change the LIKE queries to use something like the following:
SELECT ......, MATCH biblio.title AGAINST ('$titleSearchString' IN BOOLEAN MODE) AS TitleRelevance FROM biblio ..... WHERE ..... AND MATCH biblio.title AGAINST ('$titleSearchString' IN BOOLEAN MODE) ORDER BY TitleRelevance DESC
Just to fill people in, I did implement a proof of concept of this (opac-searc-biblio.pl in cvs). To my disappointment, when Joshua kindly tried it on about 150K records it was dreadfully slow, more than twice as slow as the existing method. It does add the boolean syntax which is good, but it should be about an order of magnitude quicker. While I might accept it not being much quicker, I don't think it should be slower. I'm going to spend a little time trying to clean it up and see if it can perform as (I think) it should. I have a suspicion something subtle is stopping the indexes from being used properly. Also, doing it all in one query is probably not such a good plan. There are various ways of tuning mysql to better use indexes but I'd expect such tuning shouldn't be required to get reasonable performance, even if tuning might give it a little boost. One thing I don't have right now is a nice big dataset to test with, though sanspach has kindly offered some which I will hopefully take advantage of shortly. I have about 3000 records currently from the demo system but that's too few to get a really good idea of how it scales. If it doesn't work out I'm not too worried. This might just turn out to be a learning experience into the limitations of mysql fulltext indexes :-). I notice on IRC that kados and chris had some other very interesting ideas too. It'd be nice if it did work well though. Gavin