[Koha-devel] optimising the opac a little

[Gavin McCullagh]

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