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
