Full text search gotchas
We're having quite a lot of difficulty with MySQL's fulltext search. The problem is that it does partial matches. E.g., john matches johnson. Thus a match of author against dickens takes about 0.09 seconds with a 700,000 record table, but a match of a shorter word takes a good deal longer - a four-letter word takes several seconds. Does anyone know whether it's possible to change this so substring matches (such as john against johnson) are NOT made? (Perhaps someone has a patch or a suggestion as to how to make this work.) Also, having incidentally changed ftdefs.h to match 3-letter words, is it now necessary to rebuild the fulltext indexes? = Do You Yahoo!? Get your free @yahoo.co.uk address at http://mail.yahoo.co.uk or your free @yahoo.ie address at http://mail.yahoo.ie - 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
Re: Full text search gotchas
Hello Matthew I would be quite interested to know how you managed to make a partial search. I built a search engine recently using fulltext, and partial searches would never work. Say I type in child, I would never get any results with children. This is quite weird since you seem to want it the way I had my search, but I didnt change any of the settings (left everything at default), and still had only exact matches in the output. If you find out how to change it either way, please let me know. I thought it would not be possible to use partial searches. Cheers, Nessi *amazed* ;) PS: Maybe its coz my database was very small? Only had about 200 entries when I started off? At 13:11 26/06/01 , you wrote: We're having quite a lot of difficulty with MySQL's fulltext search. The problem is that it does partial matches. E.g., john matches johnson. Thus a match of author against dickens takes about 0.09 seconds with a 700,000 record table, but a match of a shorter word takes a good deal longer - a four-letter word takes several seconds. Does anyone know whether it's possible to change this so substring matches (such as john against johnson) are NOT made? (Perhaps someone has a patch or a suggestion as to how to make this work.) Also, having incidentally changed ftdefs.h to match 3-letter words, is it now necessary to rebuild the fulltext indexes? = Do You Yahoo!? Get your free @yahoo.co.uk address at http://mail.yahoo.co.uk or your free @yahoo.ie address at http://mail.yahoo.ie - 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
RE: Full text search gotchas
If you searched for '% john %' - note the spaces, you would not find 'johnson' etc.. -Original Message- From: Matthew Brealey [mailto:[EMAIL PROTECTED]] Sent: Tue, 26 June 2001 11:29 To: [EMAIL PROTECTED] Subject: Full text search gotchas We're having quite a lot of difficulty with MySQL's fulltext search. The problem is that it does partial matches. E.g., john matches johnson. Thus a match of author against dickens takes about 0.09 seconds with a 700,000 record table, but a match of a shorter word takes a good deal longer - a four-letter word takes several seconds. Does anyone know whether it's possible to change this so substring matches (such as john against johnson) are NOT made? (Perhaps someone has a patch or a suggestion as to how to make this work.) Also, having incidentally changed ftdefs.h to match 3-letter words, is it now necessary to rebuild the fulltext indexes? = Do You Yahoo!? Get your free @yahoo.co.uk address at http://mail.yahoo.co.uk or your free @yahoo.ie address at http://mail.yahoo.ie - 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
RE: Full text search gotchas
At 13:53 +0200 2001-06-26, Bruce Stewart wrote: If you searched for '% john %' - note the spaces, you would not find 'johnson' etc.. *Sigh* Since when do wildcards work with the fulltext index? Besides, there are characters beside a space wich can delimit words. Three examples: Commas. Line breaks. The start of the actual data. If you really want words, use rlike and \...\ (assuming that MySQL can do egrep-like escapes). But it'll be dirt slow compared to the fulltext index. -- Matthias Urlichs - 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
Re: Full text search gotchas
Hello, thanks for your reply. I was wondering...I thought the LIKE function is only for single words? I have to admit I never tested it and started right off with fulltext option. So would following work then... Lets assume one entry in somefield is: The children are playing on the play-ground and I want to search for it using LIKE: SELECT * FROM yourtable WHERE somefield LIKE '%child%'; Would that then give me above entry in the output?!? Sorry if this is a stupid question, its just that I understood it from the manual that you can only search for a word not a text with LIKE. Cheers, Nessi At 13:13 26/06/01 , you wrote: Partial searches can be done with 'LIKE', i.e.: SELECT * FROM yourtable WHERE somefield LIKE '%searchstring%' The '%' is MySQLs wildcard here, so if you only want strings starting with your searchstring, use 'searchstring%' Regards, Sebastiaan J.A. Kamp Hello Matthew I would be quite interested to know how you managed to make a partial search. I built a search engine recently using fulltext, and partial searches would never work. Say I type in child, I would never get any results with children. This is quite weird since you seem to want it the way I had my search, but I didnt change any of the settings (left everything at default), and still had only exact matches in the output. If you find out how to change it either way, please let me know. I thought it would not be possible to use partial searches. Cheers, Nessi *amazed* ;) PS: Maybe its coz my database was very small? Only had about 200 entries when I started off? At 13:11 26/06/01 , you wrote: We're having quite a lot of difficulty with MySQL's fulltext search. The problem is that it does partial matches. E.g., john matches johnson. Thus a match of author against dickens takes about 0.09 seconds with a 700,000 record table, but a match of a shorter word takes a good deal longer - a four-letter word takes several seconds. Does anyone know whether it's possible to change this so substring matches (such as john against johnson) are NOT made? (Perhaps someone has a patch or a suggestion as to how to make this work.) Also, having incidentally changed ftdefs.h to match 3-letter words, is it now necessary to rebuild the fulltext indexes? = Do You Yahoo!? Get your free @yahoo.co.uk address at http://mail.yahoo.co.uk or your free @yahoo.ie address at http://mail.yahoo.ie - 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
Re: Full text search gotchas
mysql select * from test; +---+---+ | a | b | +---+---+ | 1 | The children are playing on the play-ground | | 2 | And some other sentence too | | 3 | Just for testing purposes | | 4 | Even though I do not like children, I will mention them once more | +---+---+ mysql select * from test where b LIKE '%child%'; +---+---+ | a | b | +---+---+ | 1 | The children are playing on the play-ground | | 4 | Even though I do not like children, I will mention them once more | +---+---+ The search can *NOT* be done on a 'fulltext' index, though... From: Nessi [EMAIL PROTECTED] To: Sebastiaan J.A. Kamp [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, June 26, 2001 2:31 PM Subject: Re: Full text search gotchas Hello, thanks for your reply. I was wondering...I thought the LIKE function is only for single words? I have to admit I never tested it and started right off with fulltext option. So would following work then... Lets assume one entry in somefield is: The children are playing on the play-ground and I want to search for it using LIKE: SELECT * FROM yourtable WHERE somefield LIKE '%child%'; Would that then give me above entry in the output?!?
