Hi, I'm a newbie here so please be patient with me. I'm going to open up straight away into a proposal, I hope people don't think this rude. If it can't be done, chances are you know why and I don't cause I'm new :-) I have a proposal which I'd like to suggest to optimise the Koha web based opac system. I'd like your opinions as to whether I'm right on track, doing redundant work or just plain off my rocker. It seems from a fairly cursory look that the opac system is basically constructing queries with LIKE '%xxx%' within them to interrogate the biblio table. This works fine of course but if those columns are unindexed this LIKE query seems (in my experience) a little slow. Suggestions to optimise: 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 The where clause does a word search of the title field which is very quick, as long as the indexes are in place. The TitleRelevance field in the select gives a match relevance so that you're best matches appear first (ORDER BY TitleRelevance DESC). I was mullign it over in my head and I suspect the way to combine two different column relevances is to multiply them. -- Reservations -- 1. FULLTEXT indexes and MATCH are in MySQL as of version 3.23.23. Is it likely people are using other databases which don't have FULLTEXT indexes? Is it likely people are using older versions of MySQL? http://dev.mysql.com/doc/mysql/en/fulltext-search.html http://www.databasejournal.com/features/mysql/article.php/1578331 http://jeremy.zawodny.com/blog/archives/000576.html 2. BOOLEAN MODE is a very recent addition. Indeed it's not in the MySQL package in Debian Woody. It is in sarge though (Mysql >v4.0.1). This is strictly optional for now as it just makes the searching more powerful (allowing use of wildcards, string removal, etc.). I'm not sure if an old version will ignore it or complain, but this could be left out for now. http://dev.mysql.com/doc/mysql/en/fulltext-boolean.html http://www.databasejournal.com/features/mysql/article.php/1587371 Most people don't use the boolean mode stuff, so it might be nice to have a random "tip" on the search page like: Tip: to search for partial strings use the * wildcard eg "Mac*" will match "MacCarthaigh". and a link to some advanced searching tips. 3. The addition of indexes on the biblio table will slow insertions/updates down. This is a price you pay for faster searching. In my experience it's worth the sacrifice. So, is this all useless nonsense? Should I crawl back under that rock? I'm willing to roll up my sleeves and get at it but I'd like to know this work is useful and it would help to have someone known in the community to keep an eye on the code. As I see it this is a mainly sql task and there shouldn't be too many changes to the perl code itself but I'm not so familiar with the greater part of the project code so I'd rather have someone to look over my shoulder now and then. Here is an example I've just done in PHP on MySQL with a similar database structure. There are ~20K records. http://simms.celt.dias.ie/~gavin/opac/ You can search the titles for 'gael*', the author for 'breatnach', keywords for 'royal' and 'ireland'. I have not yet implemented cross-linking. I'll take this down in a few days. Gavin
Gavin McCullagh wrote: [snip]
So, is this all useless nonsense? Should I crawl back under that rock?
Definitely not. Now is exactly the right time to be thinking of these sort of things. We have a stable 2.2 branch which is proving to be pretty solid, and we are early in the development of 2.3 (which will be come 2.4 when its ready to be released). So now is the time to try stuff :)
I'm willing to roll up my sleeves and get at it but I'd like to know this work is useful and it would help to have someone known in the community to keep an eye on the code. As I see it this is a mainly sql task and there shouldn't be too many changes to the perl code itself but I'm not so familiar with the greater part of the project code so I'd rather have someone to look over my shoulder now and then.
The way we have it set up at the moment is that all commits to cvs get posted to a mailing list koha-cvs .. which allows all of the developers (or at least the ones subscribed) to see what each other are doing.
Here is an example I've just done in PHP on MySQL with a similar database structure. There are ~20K records.
http://simms.celt.dias.ie/~gavin/opac/
You can search the titles for 'gael*', the author for 'breatnach', keywords for 'royal' and 'ireland'. I have not yet implemented cross-linking. I'll take this down in a few days.
Looks good to me, take a crack... you'll learn that with the Koha project we arent precious about our code, we want to do the best we can so if people can think of improvements we are more than willing to try them out. Great to have you aboard and good luck :) If you let me know your sourceforge id, i can give you write access to the cvs tree and you can go for your life :-) Chris -- Chris Cormack Katipo Communications Programmer www.katipo.co.nz 027 4500 789
Hi Chris, Thanks for the access. On Tue, 31 May 2005, Chris Cormack wrote:
Gavin McCullagh wrote:
So, is this all useless nonsense? Should I crawl back under that rock?
Definitely not. Now is exactly the right time to be thinking of these sort of things.
Okay cool. I'll have a go. Thanks for the welcome. Can I solicit some opinions on the researvations though?
1. FULLTEXT indexes and MATCH are in MySQL as of version 3.23.23. Is it likely people are using other databases which don't have FULLTEXT indexes? Is it likely people are using older versions of MySQL?
Is there a requirement to support certain databases/versions? Postgres can be hacked to do fulltext indexes but doesn't by default. Do many people use Postgres or is it all MySQL? Would this incompatibility concern people? http://www.devx.com/opensource/Article/21674/1954?pf=true http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/
2. BOOLEAN MODE is a very recent addition.
This is not such a big deal. We can just turn it off if we need to, or maybe even test for the db version and use it if it's available.
3. The addition of indexes on the biblio table will slow insertions/updates down.
I can't see this being a big issue. There is a slowdown but it's not usually too big and it pales into insignificance compared with the speed-ups in searching.
The way we have it set up at the moment is that all commits to cvs get posted to a mailing list koha-cvs .. which allows all of the developers (or at least the ones subscribed) to see what each other are doing.
Okay.
Here is an example I've just done in PHP on MySQL with a similar database structure. There are ~20K records.
Sorry had to take this down. It wasn't really supposed to be public info :-)
Looks good to me, take a crack... you'll learn that with the Koha project we arent precious about our code, we want to do the best we can so if people can think of improvements we are more than willing to try them out.
Sound. Thanks, Gavin
Hy everybody, I don't know if this question should be posted in this or the user's forum: I'm having a problem to complete the installation of Koha. Just after been installed I cannot open the initial page. and I got the following message in the opac-error.log: [Fri Jun 03 10:45:54 2005] [warn] [client 10.171.94.4] mod_include: Options +Includes (or IncludesNoExec) wasn't set, INCLUDES filter removed I guess is anything about my Apache... 2.0.53 about my httpd.conf et my koha-httpd.conf configuration, anyone could give me a light? My koha is v 2.0.2 Thanks.. Luis Samico --------------------------------- Découvrez le nouveau Yahoo! Mail : 1 Go d'espace de stockage pour vos mails, photos et vidéos ! Créez votre Yahoo! Mail
Luis Eduardo Samico <lesamico@yahoo.com.br> wrote:
I don't know if this question should be posted in this or the user's foru= m:=20 [...]
Users, I think. Never mind.
[Fri Jun 03 10:45:54 2005] [warn] [client 10.171.94.4] mod_include: Optio= ns +Includes (or IncludesNoExec) wasn't set, INCLUDES filter removed
I guess is anything about my Apache... 2.0.53 about my httpd.conf et my k= oha-httpd.conf configuration, anyone could give me a light?=20
My guess is that you need to add lines in your koha-httpd.conf that say Options +Includes and reload apache (making sure that koha-httpd.conf is actually read, which might be done by running /usr/sbin/apache2 -S Hope that helps, -- MJ Ray (slef), K. Lynn, England, email see http://mjr.towers.org.uk/ http://www.ttllp.co.uk/koha/
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
participants (4)
-
Chris Cormack -
Gavin McCullagh -
Luis Eduardo Samico -
MJ Ray