Re: Fwd: Re: FULLTEXT exact match queries?
Hello Nate, Monday, 24 September, 2001, 19:56:47, you wrote: NF I built my database and then used the command: NF ALTER TABLE disc ADD FULLTEXT (title,recentdata,specialsound,uniqueid) NF But if I try to search with this command: NF SELECT title, recentdata, specialsound,uniqueid FROM disc WHERE MATCH NF (title) AGAINST ('Superman') NFORDER BY ID; NF I get the following error: NF Error 1191: Can't find FULLTEXT index matching the column list. NF What am I doing wrong? I think I'm missing something in my ALTER TABLE NF statement but I'm not sure. I haven't found any examples on the web for NF what I need. TIA! You need to create fulltext index for one field 'title' ALTER TABLE disc ADD FULLTEXT (title) -- Best regards, Lyubomirmailto:[EMAIL PROTECTED] - 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
Fwd: Re: FULLTEXT exact match queries?
Hi! I built my database and then used the command: ALTER TABLE disc ADD FULLTEXT (title,recentdata,specialsound,uniqueid) But if I try to search with this command: SELECT title, recentdata, specialsound,uniqueid FROM disc WHERE MATCH (title) AGAINST ('Superman') ORDER BY ID; I get the following error: Error 1191: Can't find FULLTEXT index matching the column list. What am I doing wrong? I think I'm missing something in my ALTER TABLE statement but I'm not sure. I haven't found any examples on the web for what I need. TIA! Nate - 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: FULLTEXT exact match queries?
Hi! On Aug 28, Alok K. Dhir wrote: select * from Laws WHERE MATCH(Line) AGAINST ('growth hormone') AND Line like '%growth hormone%' ORDER BY ID; This defeats the purpose of the fulltext index, don't you think? Al Not really, as fulltext index is used to find the documents that contain either growth or hormone, and LIKE is applied only to those documents, not to all the rows. But, indeed, this is a hack and at some point - not in 4.0 though, MySQL will have support for phrase searches. Regards, Sergei -- MySQL Development Team __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ - 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
FULLTEXT exact match queries?
How do I do exact phrase matches using FULLTEXT? I have this 2 million row table, full of text data, that I want to search for an exact phrase. When I say: select * from Laws WHERE MATCH(Line) AGAINST ('MDMA') ORDER BY ID; It works correctly, returning 1 row, MUCH faster than doing a select ... LIKE! But when I do: select * from Laws WHERE MATCH(Line) AGAINST ('growth hormone') ORDER BY ID; MySQL returns every row with either growth OR hormone in it, which isn't what I wanted. Is there a way to only return the rows where the exact phrase growth hormone is present? -- Ed Carp, N7EKG - [EMAIL PROTECTED] - 214/341-4420 - http://www.pobox.com/~erc Squished Mosquito, Inc. Internet Applications Development Escapade Server-Side Scripting Language Development Team http://www.squishedmosquito.com Pensacola - Dallas - Dresden - London - Paris - 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: FULLTEXT exact match queries?
Hi! On Aug 28, Ed Carp wrote: How do I do exact phrase matches using FULLTEXT? I have this 2 million row table, full of text data, that I want to search for an exact phrase. When I say: select * from Laws WHERE MATCH(Line) AGAINST ('MDMA') ORDER BY ID; It works correctly, returning 1 row, MUCH faster than doing a select ... LIKE! But when I do: select * from Laws WHERE MATCH(Line) AGAINST ('growth hormone') ORDER BY ID; MySQL returns every row with either growth OR hormone in it, which isn't what I wanted. Is there a way to only return the rows where the exact phrase growth hormone is present? Try select * from Laws WHERE MATCH(Line) AGAINST ('growth hormone') AND Line like '%growth hormone%' ORDER BY ID; Regards, Sergei -- MySQL Development Team __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ - 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: FULLTEXT exact match queries?
On Aug 28, Ed Carp wrote: How do I do exact phrase matches using FULLTEXT? I have this 2 million row table, full of text data, that I want to search for an exact phrase. When I say: select * from Laws WHERE MATCH(Line) AGAINST ('MDMA') ORDER BY ID; It works correctly, returning 1 row, MUCH faster than doing a select ... LIKE! But when I do: select * from Laws WHERE MATCH(Line) AGAINST ('growth hormone') ORDER BY ID; MySQL returns every row with either growth OR hormone in it, which isn't what I wanted. Is there a way to only return the rows where the exact phrase growth hormone is present? Try select * from Laws WHERE MATCH(Line) AGAINST ('growth hormone') AND Line like '%growth hormone%' ORDER BY ID; It works - thanks! Hmmm...why didn't I think of that? ;) -- Ed Carp, N7EKG - [EMAIL PROTECTED] - 214/341-4420 - http://www.pobox.com/~erc Squished Mosquito, Inc. Internet Applications Development Escapade Server-Side Scripting Language Development Team http://www.squishedmosquito.com Pensacola - Dallas - Dresden - London - Paris - 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: RE: FULLTEXT exact match queries?
Alok K. Dhir ([EMAIL PROTECTED]) writes: select * from Laws WHERE MATCH(Line) AGAINST ('growth hormone') AND Line like '%growth hormone%' ORDER BY ID; This defeats the purpose of the fulltext index, don't you think? Not really - since the WHERE clause is executed left to right, the LIKE clause is run against the output of the MATCH clause, a much smaller subset. I tried it - on a 250MB database containing almost 1.7 million records, the query time went from 100+ seconds to just under 1 second - a significant increase in speed! -- Ed Carp, N7EKG - [EMAIL PROTECTED] - 214/341-4420 - http://www.pobox.com/~erc Is your web site data driven? Are you swamped with work managing, updating, and deploying that data on to your web site? You need Escapade! Check us out at http://www.squishedmosquito.com/cgi-bin/esp?PAGE=esp_intro.html - 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