RE: fulltext search option
So far as I know, the only way to accomplish what you want is to do what you
tried: put the Boolean match in the WHERE clause and order by a natural
match. That's what I do on our web site.
By the way, you can't use prefix stemming: *abcd is the same as abcd.
Regards,
Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
www.the-infoshop.com
www.giiexpress.com
www.etudes-marche.com
-Original Message-
From: Tanner Postert [mailto:[EMAIL PROTECTED]
Sent: Thursday, July 26, 2007 7:56 PM
To: mysql@lists.mysql.com
Subject: fulltext search option
I'm having a problem with the fulltext searching, and was
looking for some
help.
i'm currently working with the following query:
select table.*
from table where match(title, description) against ('*search term*' IN
BOOLEAN MODE)
the reason I am using boolean mode, is so that it matches things like
search terms, rather than excluding those results if i left off the
boolean mode.
my problem, is that the exact phrase search term shows up
in the result
set, but is not ranked the highest since it's only a boolean
search, its
just checking if it exits.
if I add: match(title,description) against ('search term') as score
to my select and order by score, it works fine, but my query
time goes from
0.0623 to 3.7 seconds. which seems like a huge give in
performance, which I
assume comes from ordering by a dynamically created field, so
I tried to
fake out the query by changing the query to
select table.* from table where (match(title, description)
against ('search
term') or match(title, description) against ('*search term*'
in boolean
mode))
but the ordering is still off.
is there a way to have ordered by the non-boolean query, but
still be able
to include those results?
seems bad that I can only have exact matches and proper
ordering, or partial
matches and no ordering.
select video.id, video.title, video.description,
(match(title, description) against ('woot monkey')) as straight,
(match(title, description) against ('*woot monkey*' in
boolean mode)) as
stars,
(match(title,description) against ('woot monkey' in boolean
mode)) as
exact
from video where (match(title, description) against ('*woot
monkey*' IN
BOOLEAN MODE)
and file_complete = 'true') order by exact DESC, stars DESC,
straight DESC
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Fulltext search dilemma (IN BOOLEAN MODE)
Unless you changed the minimum word length, Key would be ignored because
it is too short. I would think the quotation marks at the start or end of
the words would be ignored. The asterisk operator is only valid at the end
of a word. Those initial asterisks, and the quotation marks, would be
treated as word delimiters as far as I can tell.
Also, the asterisk operator is only valid at the end of a word, not in the
beginning. The leading asterisks should be ignored, since they would be
treated as word delimiters.
Regards,
Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
-Original Message-
From: Andreas Iwanowski [mailto:[EMAIL PROTECTED]
Sent: Thursday, February 01, 2007 12:49 PM
To: mysql@lists.mysql.com
Subject: Fulltext search dilemma (IN BOOLEAN MODE)
Hello MySQL experts,
I'm trying to do a full text search on an indexed Keywords column that
contains quotation marks, and it's giving me a headache.
Suppose there are records in the database containing the folling
keywords:
1. Miami Beach City
2. Key West Florida
3. Key West Beach Florida
Now I do two fulltext searches on this column like this:
SELECT * FROM _my_table_here WHERE MATCH(Keywords) AGAINST ('*Key*
*West*' IN BOOLEAN MODE)0;
SELECT * FROM _my_table_here WHERE MATCH(Keywords) AGAINST ('*Miami*
*Beach*' IN BOOLEAN MODE)0;
The second query returns the correct rows in spite of the preceding
Miami.
The first one, however, returns no results, even though there are rows
in the table that *should* match the query.
I think is has something to do with noise words, since if I have a row
like this one:
4. Key West
Then the problem does not occur and the search returns that row.
Any help would be appreciated.
-Andy
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Fulltext search dilemma (IN BOOLEAN MODE)
Hi, thank you for your reply.
I have used the option ft_min_word_len=3.
If I have something like
1. Key West
in the database and I do a
SELECT * FROM my_table WHERE MATCH(Keywords) AGAINST ('Key' IN BOOLEAN
MODE)0;
then I don't get any results either.
If I leave the quotation marks away, I find the row.
However, the quotation marks are necessary for another part of the
program,
so I can't leave them out of the column.
Any ideas why the quotation marks would cause this problem?
-Andy
-Original Message-
From: Jerry Schwartz [mailto:[EMAIL PROTECTED]
Sent: Thursday, February 01, 2007 1:52 PM
To: Andreas Iwanowski; mysql@lists.mysql.com
Subject: RE: Fulltext search dilemma (IN BOOLEAN MODE)
Unless you changed the minimum word length, Key would be ignored
because it is too short. I would think the quotation marks at the start
or end of the words would be ignored. The asterisk operator is only
valid at the end of a word. Those initial asterisks, and the quotation
marks, would be treated as word delimiters as far as I can tell.
Also, the asterisk operator is only valid at the end of a word, not in
the beginning. The leading asterisks should be ignored, since they would
be treated as word delimiters.
Regards,
Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
-Original Message-
From: Andreas Iwanowski [mailto:[EMAIL PROTECTED]
Sent: Thursday, February 01, 2007 12:49 PM
To: mysql@lists.mysql.com
Subject: Fulltext search dilemma (IN BOOLEAN MODE)
Hello MySQL experts,
I'm trying to do a full text search on an indexed Keywords column that
contains quotation marks, and it's giving me a headache.
Suppose there are records in the database containing the folling
keywords:
1. Miami Beach City
2. Key West Florida
3. Key West Beach Florida
Now I do two fulltext searches on this column like this:
SELECT * FROM _my_table_here WHERE MATCH(Keywords) AGAINST ('*Key*
*West*' IN BOOLEAN MODE)0; SELECT * FROM _my_table_here WHERE
MATCH(Keywords) AGAINST ('*Miami* *Beach*' IN BOOLEAN MODE)0;
The second query returns the correct rows in spite of the preceding
Miami.
The first one, however, returns no results, even though there are rows
in the table that *should* match the query.
I think is has something to do with noise words, since if I have a row
like this one:
4. Key West
Then the problem does not occur and the search returns that row.
Any help would be appreciated.
-Andy
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Fulltext search dilemma (IN BOOLEAN MODE)
Sorry, I have no idea what is going wrong.
Regards,
Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
-Original Message-
From: Andreas Iwanowski [mailto:[EMAIL PROTECTED]
Sent: Thursday, February 01, 2007 2:22 PM
To: Jerry Schwartz
Cc: mysql@lists.mysql.com
Subject: RE: Fulltext search dilemma (IN BOOLEAN MODE)
Hi, thank you for your reply.
I have used the option ft_min_word_len=3.
If I have something like
1. Key West
in the database and I do a
SELECT * FROM my_table WHERE MATCH(Keywords) AGAINST ('Key' IN BOOLEAN
MODE)0;
then I don't get any results either.
If I leave the quotation marks away, I find the row.
However, the quotation marks are necessary for another part of the
program,
so I can't leave them out of the column.
Any ideas why the quotation marks would cause this problem?
-Andy
-Original Message-
From: Jerry Schwartz [mailto:[EMAIL PROTECTED]
Sent: Thursday, February 01, 2007 1:52 PM
To: Andreas Iwanowski; mysql@lists.mysql.com
Subject: RE: Fulltext search dilemma (IN BOOLEAN MODE)
Unless you changed the minimum word length, Key would be ignored
because it is too short. I would think the quotation marks at
the start
or end of the words would be ignored. The asterisk operator is only
valid at the end of a word. Those initial asterisks, and the quotation
marks, would be treated as word delimiters as far as I can tell.
Also, the asterisk operator is only valid at the end of a word, not in
the beginning. The leading asterisks should be ignored, since
they would
be treated as word delimiters.
Regards,
Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
-Original Message-
From: Andreas Iwanowski [mailto:[EMAIL PROTECTED]
Sent: Thursday, February 01, 2007 12:49 PM
To: mysql@lists.mysql.com
Subject: Fulltext search dilemma (IN BOOLEAN MODE)
Hello MySQL experts,
I'm trying to do a full text search on an indexed Keywords
column that
contains quotation marks, and it's giving me a headache.
Suppose there are records in the database containing the folling
keywords:
1. Miami Beach City
2. Key West Florida
3. Key West Beach Florida
Now I do two fulltext searches on this column like this:
SELECT * FROM _my_table_here WHERE MATCH(Keywords) AGAINST ('*Key*
*West*' IN BOOLEAN MODE)0; SELECT * FROM _my_table_here WHERE
MATCH(Keywords) AGAINST ('*Miami* *Beach*' IN BOOLEAN MODE)0;
The second query returns the correct rows in spite of the
preceding
Miami.
The first one, however, returns no results, even though
there are rows
in the table that *should* match the query.
I think is has something to do with noise words, since if I
have a row
like this one:
4. Key West
Then the problem does not occur and the search returns that row.
Any help would be appreciated.
-Andy
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Fulltext search dilemma (IN BOOLEAN MODE) [RESOLVED]
Hello MySQL fellows,
The problem was solved after rebuilding all indices on the table by
using the table repair function.
I noticed the problem after the application returned an error issued by
MySQL that the table.myd file
was missing or corrupted.
Nevertheless, I don't know how that happened, the server was always shut
down properly.
-Andy
-Original Message-
From: Jerry Schwartz [mailto:[EMAIL PROTECTED]
Sent: Thursday, February 01, 2007 4:10 PM
To: Andreas Iwanowski
Cc: mysql@lists.mysql.com
Subject: RE: Fulltext search dilemma (IN BOOLEAN MODE)
Sorry, I have no idea what is going wrong.
Regards,
Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
-Original Message-
From: Andreas Iwanowski [mailto:[EMAIL PROTECTED]
Sent: Thursday, February 01, 2007 2:22 PM
To: Jerry Schwartz
Cc: mysql@lists.mysql.com
Subject: RE: Fulltext search dilemma (IN BOOLEAN MODE)
Hi, thank you for your reply.
I have used the option ft_min_word_len=3.
If I have something like
1. Key West
in the database and I do a
SELECT * FROM my_table WHERE MATCH(Keywords) AGAINST ('Key' IN BOOLEAN
MODE)0;
then I don't get any results either.
If I leave the quotation marks away, I find the row.
However, the quotation marks are necessary for another part of the
program, so I can't leave them out of the column.
Any ideas why the quotation marks would cause this problem?
-Andy
-Original Message-
From: Jerry Schwartz [mailto:[EMAIL PROTECTED]
Sent: Thursday, February 01, 2007 1:52 PM
To: Andreas Iwanowski; mysql@lists.mysql.com
Subject: RE: Fulltext search dilemma (IN BOOLEAN MODE)
Unless you changed the minimum word length, Key would be ignored
because it is too short. I would think the quotation marks at the
start or end of the words would be ignored. The asterisk operator is
only valid at the end of a word. Those initial asterisks, and the
quotation marks, would be treated as word delimiters as far as I can
tell.
Also, the asterisk operator is only valid at the end of a word, not in
the beginning. The leading asterisks should be ignored, since they
would be treated as word delimiters.
Regards,
Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
-Original Message-
From: Andreas Iwanowski [mailto:[EMAIL PROTECTED]
Sent: Thursday, February 01, 2007 12:49 PM
To: mysql@lists.mysql.com
Subject: Fulltext search dilemma (IN BOOLEAN MODE)
Hello MySQL experts,
I'm trying to do a full text search on an indexed Keywords
column that
contains quotation marks, and it's giving me a headache.
Suppose there are records in the database containing the folling
keywords:
1. Miami Beach City
2. Key West Florida
3. Key West Beach Florida
Now I do two fulltext searches on this column like this:
SELECT * FROM _my_table_here WHERE MATCH(Keywords) AGAINST ('*Key*
*West*' IN BOOLEAN MODE)0; SELECT * FROM _my_table_here WHERE
MATCH(Keywords) AGAINST ('*Miami* *Beach*' IN BOOLEAN MODE)0;
The second query returns the correct rows in spite of the
preceding
Miami.
The first one, however, returns no results, even though
there are rows
in the table that *should* match the query.
I think is has something to do with noise words, since if I
have a row
like this one:
4. Key West
Then the problem does not occur and the search returns that row.
Any help would be appreciated.
-Andy
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fulltext search optimization
Most of the queries you listed below probably aren't taking advantage of the full text index. MySQL doesn't consider song.mp3 or
document.pdf words, they would be considered two words because the period is considered a word separator. Keep in mind MySQL indexes
words not character strings. Punctuation (ACE-HIGH) and numbers (007) aren't indexed either. Again the hyphen is a word separator.
Also, searching on ends with won't use an index. So your *.mp3 won't be able to use the index. That's probably why everything is
taking so long. You might as well be searching on LIKE %.mp3%, probably would be about just as fast, and you could use a memory
table if you wanted.
- Original Message -
From: Svilen Spasov (Ancient Media) [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Thursday, July 13, 2006 9:57 AM
Subject: Re: fulltext search optimization
Thanks for your respond.
Here is the CREATE TABLE:
CREATE TABLE `results_1` (
`id` int(11) NOT NULL auto_increment,
`filename` varchar(255) collate cp1251_bulgarian_ci default NULL,
`fileext` varchar(10) collate cp1251_bulgarian_ci default NULL,
`username` varchar(16) collate cp1251_bulgarian_ci default NULL,
`filesize` float default NULL,
`date` datetime default NULL,
`is_dir` tinyint(1) NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `results_1_filesize` (`filesize`),
KEY `results_1_username` (`username`),
KEY `results_1_filename_1` (`filename`),
FULLTEXT KEY `results_1_filename` (`filename`,`username`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251 COLLATE=cp1251_bulgarian_ci
More details:
SELECT count(*) FROM results_1;
+--+
| count(*)
+--+
| 2399315
+--+
1 row in set (0.03 sec)
And some sample data:
INSERT INTO results_1 (id, filename, fileext, username, filesize, date, is_dir) VALUES (857845, '04 - Iron Maiden - Iron
Maiden.mp3', 'mp3', 'drawer', '7.6546e+06', '2006-03-05 01:28:00', 0);
INSERT INTO results_1 (id, filename, fileext, username, filesize, date, is_dir) VALUES (857844, '03 - Iron Maiden - Fear Of The
Dark.mp3', 'mp3', 'drawer', '1.25829e+07', '2006-03-05 01:29:00', 0);
INSERT INTO results_1 (id, filename, fileext, username, filesize, date, is_dir) VALUES (857843, '02 - Iron Maiden - Hallowed Be
Thy Name.mp3', 'mp3', 'drawer', '1.25829e+07', '2006-03-05 01:33:00', 0);
INSERT INTO results_1 (id, filename, fileext, username, filesize, date, is_dir) VALUES (857842, '01 - Iron Maiden - No More
Lies.mp3', 'mp3', 'drawer', '1.36315e+07', '2006-03-05 01:31:00', 0);
INSERT INTO results_1 (id, filename, fileext, username, filesize, date, is_dir) VALUES (857841, '09 - Iron Maiden - Lord Of The
Flies.mp3', 'mp3', 'drawer', '8.70318e+06', '2006-03-05 01:24:00', 0);
And I logged some of the queries which executes for more than 20sec:
query timing: 28.8102879524
SELECT id, filename, filesize, date, MATCH (filename, username) AGAINST('+*.mp3' IN BOOLEAN MODE) as coef FROM results_1 WHERE
MATCH (filename, username) AGAINST('+*.mp3' IN BOOLEAN MODE) 0 ORDER BY coef, filename DESC LIMIT 0, 50
query timing: 36.2581338882
SELECT count(id) as all_records FROM results_1 WHERE MATCH (filename, username)
AGAINST('+007' IN BOOLEAN MODE) 0
query timing: 31.0913391113
SELECT count(id) as all_records FROM results_1 WHERE MATCH (filename, username) AGAINST('+ACE-HIGH +MP3' IN BOOLEAN MODE) 0
UNION ALL SELECT count(id) as all_records FROM results_1 WHERE MATCH (filename, username) AGAINST('+ACE-HIGHMP3' IN BOOLEAN
MODE) 0
query timing: 32.1210138798
SELECT count(id) as all_records FROM results_1 WHERE MATCH (filename, username)
AGAINST('+.pdf' IN BOOLEAN MODE) 0
query timing: 29.8846437931
SELECT id, filename, filesize, date, MATCH (filename, username) AGAINST('+star +craft +mp3' IN BOOLEAN MODE) as coef FROM
results_1 WHERE MATCH (filename, username) AGAINST('+star +craft +mp3' IN BOOLEAN MODE) 0 UNION ALL SELECT id, filename,
filesize, date, MATCH (filename, username) AGAINST('+starcraft' IN BOOLEAN MODE) as coef FROM results_1 WHERE MATCH (filename,
username) AGAINST ('+starcraft' IN BOOLEAN MODE) 0 UNION ALL SELECT id, filename, filesize, date, MATCH (filename, username)
AGAINST('+starcraftmp3' IN BOOLEAN MODE) as coef FROM results_1 WHERE MATCH (filename, username) AGAINST('+starcraftmp3' IN
BOOLEAN MODE) 0 ORDER BY coef, filename LIMIT 0, 50
query timing: 28.6531541348
SELECT count(id) as all_records FROM results_1 WHERE MATCH (filename, username) AGAINST('+need +somebody +to +love' IN BOOLEAN
MODE) 0 UNION ALL SELECT count(id) as all_records FROM results_1 WHERE MATCH (filename, username) AGAINST('+needsomebody' IN
BOOLEAN MODE) 0 UNION ALL SELECT count(id) as all_records FROM results_1 WHERE MATCH (filename, username)
AGAINST('+needsomebodyto' IN BOOLEAN MODE) 0 UNION ALL SELECT count(id) as all_records FROM results_1 WHERE MATCH (filename,
username) AGAINST('+needsomebodytolove' IN BOOLEAN MODE) 0
I've tried
Re: fulltext search optimization
Thanks for your respond.
Here is the CREATE TABLE:
CREATE TABLE `results_1` (
`id` int(11) NOT NULL auto_increment,
`filename` varchar(255) collate cp1251_bulgarian_ci default NULL,
`fileext` varchar(10) collate cp1251_bulgarian_ci default NULL,
`username` varchar(16) collate cp1251_bulgarian_ci default NULL,
`filesize` float default NULL,
`date` datetime default NULL,
`is_dir` tinyint(1) NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `results_1_filesize` (`filesize`),
KEY `results_1_username` (`username`),
KEY `results_1_filename_1` (`filename`),
FULLTEXT KEY `results_1_filename` (`filename`,`username`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251 COLLATE=cp1251_bulgarian_ci
More details:
SELECT count(*) FROM results_1;
+--+
| count(*)
+--+
| 2399315
+--+
1 row in set (0.03 sec)
And some sample data:
INSERT INTO results_1 (id, filename, fileext, username, filesize,
date, is_dir) VALUES (857845, '04 - Iron Maiden - Iron Maiden.mp3',
'mp3', 'drawer', '7.6546e+06', '2006-03-05 01:28:00', 0);
INSERT INTO results_1 (id, filename, fileext, username, filesize,
date, is_dir) VALUES (857844, '03 - Iron Maiden - Fear Of The
Dark.mp3', 'mp3', 'drawer', '1.25829e+07', '2006-03-05 01:29:00', 0);
INSERT INTO results_1 (id, filename, fileext, username, filesize,
date, is_dir) VALUES (857843, '02 - Iron Maiden - Hallowed Be Thy
Name.mp3', 'mp3', 'drawer', '1.25829e+07', '2006-03-05 01:33:00', 0);
INSERT INTO results_1 (id, filename, fileext, username, filesize,
date, is_dir) VALUES (857842, '01 - Iron Maiden - No More Lies.mp3',
'mp3', 'drawer', '1.36315e+07', '2006-03-05 01:31:00', 0);
INSERT INTO results_1 (id, filename, fileext, username, filesize,
date, is_dir) VALUES (857841, '09 - Iron Maiden - Lord Of The
Flies.mp3', 'mp3', 'drawer', '8.70318e+06', '2006-03-05 01:24:00', 0);
And I logged some of the queries which executes for more than 20sec:
query timing: 28.8102879524
SELECT id, filename, filesize, date, MATCH (filename, username)
AGAINST('+*.mp3' IN BOOLEAN MODE) as coef FROM results_1 WHERE MATCH
(filename, username) AGAINST('+*.mp3' IN BOOLEAN MODE) 0 ORDER BY
coef, filename DESC LIMIT 0, 50
query timing: 36.2581338882
SELECT count(id) as all_records FROM results_1 WHERE MATCH (filename,
username) AGAINST('+007' IN BOOLEAN MODE) 0
query timing: 31.0913391113
SELECT count(id) as all_records FROM results_1 WHERE MATCH (filename,
username) AGAINST('+ACE-HIGH +MP3' IN BOOLEAN MODE) 0 UNION ALL
SELECT count(id) as all_records FROM results_1 WHERE MATCH (filename,
username) AGAINST('+ACE-HIGHMP3' IN BOOLEAN MODE) 0
query timing: 32.1210138798
SELECT count(id) as all_records FROM results_1 WHERE MATCH (filename,
username) AGAINST('+.pdf' IN BOOLEAN MODE) 0
query timing: 29.8846437931
SELECT id, filename, filesize, date, MATCH (filename, username)
AGAINST('+star +craft +mp3' IN BOOLEAN MODE) as coef FROM results_1
WHERE MATCH (filename, username) AGAINST('+star +craft +mp3' IN
BOOLEAN MODE) 0 UNION ALL SELECT id, filename, filesize, date,
MATCH (filename, username) AGAINST('+starcraft' IN BOOLEAN MODE) as
coef FROM results_1 WHERE MATCH (filename, username) AGAINST
('+starcraft' IN BOOLEAN MODE) 0 UNION ALL SELECT id, filename,
filesize, date, MATCH (filename, username) AGAINST('+starcraftmp3' IN
BOOLEAN MODE) as coef FROM results_1 WHERE MATCH (filename, username)
AGAINST('+starcraftmp3' IN BOOLEAN MODE) 0 ORDER BY coef,
filename LIMIT 0, 50
query timing: 28.6531541348
SELECT count(id) as all_records FROM results_1 WHERE MATCH (filename,
username) AGAINST('+need +somebody +to +love' IN BOOLEAN MODE) 0
UNION ALL SELECT count(id) as all_records FROM results_1 WHERE MATCH
(filename, username) AGAINST('+needsomebody' IN BOOLEAN MODE) 0
UNION ALL SELECT count(id) as all_records FROM results_1 WHERE MATCH
(filename, username) AGAINST('+needsomebodyto' IN BOOLEAN MODE) 0
UNION ALL SELECT count(id) as all_records FROM results_1 WHERE MATCH
(filename, username) AGAINST('+needsomebodytolove' IN BOOLEAN MODE) 0
I've tried with the MEMORY storage engine, unfortunately it doesn't
support fulltext indexes.
Svilen Spasov
On Jul 12, 2006, at 7:05 PM, John Hicks wrote:
Svilen Spasov (Ancient Media) wrote:
Hello,
I have a website with a MySQL database and I have a table with ~2
millions row (usernames, filenames; ~120MB db data file and ~230MB
db index file) with I would like to search using the fulltext
indeces.
Unfortunately the server get loaded pretty much. It always stays
on 20 load average and often gets 50-60 load average.
I'm sure that this is because of slow mysql response (slow
queries) to the apache web server.
Can you give me some advice how to optimize the mysql server?
The server hardware is: 1GB RAM, 1.8GHz Celeron, 40GB HDD
(currently I have 512MB free memory)
Run a SHOW CREATE TABLE tablename on the table and post the
results
Re: fulltext search optimization
Svilen Spasov (Ancient Media) wrote: Hello, I have a website with a MySQL database and I have a table with ~2 millions row (usernames, filenames; ~120MB db data file and ~230MB db index file) with I would like to search using the fulltext indeces. Unfortunately the server get loaded pretty much. It always stays on 20 load average and often gets 50-60 load average. I'm sure that this is because of slow mysql response (slow queries) to the apache web server. Can you give me some advice how to optimize the mysql server? The server hardware is: 1GB RAM, 1.8GHz Celeron, 40GB HDD (currently I have 512MB free memory) Run a SHOW CREATE TABLE tablename on the table and post the results here. (We're particularly interested in how it is indexed.) Tell us what queries are being run against the table. If possible post the actual SQL queries. -J Thanks, Svilen Spasov --MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext search for term 'c++'
I think the '+' will not get into the index in the first place. So, there's
probably no way to get a search to use the index. However if you want a
consistant query format for your search you could probably get the correct
result by wrapping the search term in double quotes:
select f1 from t1 where match(f1) against ('c++' in boolean mode);
HTH,
James
At 12:30 pm -0700 10/5/06, klute wrote:
Is there any way I can search for a term such as 'c++'
using a fulltext search index?
select f1 from t1 where match(f1) against('c++' in
boolean mode);
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fulltext search
On Sun, 18 Dec 2005, Octavian Rasnita wrote: Hi, Please tell me how can I configure MySQL 5 in order to be able to search (using fulltext indexes) for combined words like s-au. This is a single word and not 2 words but I think MySQL thinks that there are 2 words, one of them having a single character, and the second 2 chars, so it is not found because I have configured MySQL to index only the words that have at least 3 chars. I don't think it would be a good idea to configure it to index one-char words, so I hope there is another method. Thank you. Teddy Hi Teddy, you may try the following hack to make '-' a normal text character (these code fragments are from mysql-4.0.25 but it may work with 5.0.x as well): Change the source in myisam/ftdefs.h #define true_word_char(X) (isalnum(X) || (X)=='_') to #define true_word_char(X) (isalnum(X) || (X)=='_' || (X)=='-') and substitute another char for '-' in myisam/ft_static.c: const char *ft_boolean_syntax=+ -()~*:\\|; which could become something like const char *ft_boolean_syntax=+ =()~*:\\|; Then recompile and try your luck. You have to rebuild your indexes. Thomas Spahni -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fulltext search
AFAIK you are right - MySQL treats a hypen as a word-break. And, AFAIK you
cannot modify that behaviour.
The only possibility, I think, would be to modify the source and compile your
own MySQL. :-(
However if you do a full-text search using IN BOOLEAN MODE, then you can put
quotes around hypenated words.
... MATCH (col_name) AGAINST ('s-au' IN BOOLEAN MODE) ...
HTH,
James Harvard
At 5:26 pm +0200 18/12/05, Octavian Rasnita wrote:
Please tell me how can I configure MySQL 5 in order to be able to search
(using fulltext indexes) for combined words like s-au.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fulltext search
AFAIK you are right - MySQL treats a hypen as a word-break. And, AFAIK you
cannot modify that behaviour.
The only possibility, I think, would be to modify the source and compile
your own MySQL. :-(
However if you do a full-text search using IN BOOLEAN MODE, then you can
put quotes around hypenated words.
... MATCH (col_name) AGAINST ('s-au' IN BOOLEAN MODE) ...
HTH,
James Harvard
I have tried that, but it doesn't found anything. I think this is because
MySQL doesn't put the words s and au in the fulltext index at all, so it
is not able to find s-au.
Isn't possible to set somewhere which are the word chars?
I am not sure how other special chars (which are real chars) like s, t, â,
a, î, S, T, Â, Î, A are viewd by MySQL fulltext index... as word chars, or
as word break chars.
If I could add some of these chars, maybe I could also add the -
character.
Teddy
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fulltext search on words inside words
Merlin wrote: I am wondering if it is possible to find words inside words with the help of fulltext search. Is this possible? Google does that, so somehow there should be a way. Somehow I don't think that Google runs on a single MySQL database. Full text indexes in MySQL mean that words (MySQL's definition of a word that is) are indexed, not parts of words. Google's purpose is to provide a searchable index, so they have built their own data structures for these features. Another thing is, how do I exclude popular words like and for and similar from the search? Is there a MySQL setting for this. Like words_to_exclude = Fine tuning full-text search can be found at: http://dev.mysql.com/doc/mysql/en/fulltext-fine-tuning.html The default stop words are at: http://dev.mysql.com/doc/mysql/en/fulltext-stopwords.html Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FULLTEXT search result requirement
2005/8/11, Eric Jensen [EMAIL PROTECTED]: I am looking into using the FULLTEXT search features for our FAQ system. Problem is the 50% limitation. We aren't going to have thousands of questions or articles, so the odds of most of the questions/articles matching is high and a desireable effect for us. Is there a away to disable this requirment? Yes: use IN BOOLEAN MODE for your fulltext searches. See the MySQL manual for further information. Jan Pieter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Fulltext search question
[snip] Given a search string of 'NASD' my default Fulltext search doesn't find it. Wondered why? [/snip] Quote from http://dev.mysql.com/doc/mysql/en/Fulltext_Search.html: MySQL uses a very simple parser to split text into words. A word is any sequence of true word characters (letters, digits, and underscores), optionally separated by no more than one sequential `'` character. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext search string less than 4 characters
Lee, establish the fulltext minimum word length system variable as follows... [mysqld] ft_min_word_len=3 reference: http://dev.mysql.com/doc/mysql/en/Fulltext_Fine-tuning.html cheers, --bemansell Brian E. Mansell MySQL Professional On Thu, 06 Jan 2005 20:59:23 -0500, leegold [EMAIL PROTECTED] wrote: I want to know on Solaris how I could lower the the minimum fulltext search string from 4 to 3. Right now using the FullText search any string less than 4 chars is ignored. I'm sure there's a link explaining how. Maybe UNIX help in general on his would be good as well. Thanks, Lee G. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext search Strategy - Need Help
Search '+ford +focus' [in boolean mode] Santino At 13:22 + 3-11-2004, Lee Denny wrote: Hello, I'm doing fairly straight forward fulltext searches, but I want to nest them - basically do a keyword search on 'phrase 1' and then search the results this returns for 'phrase 2', for example if phrase 1 is 'ford' and phrase 2 is 'focus' - I search once for 'ford' and then go through the record-set this returns for 'focus'. My first thought is use a temporary table - but is there a way of doing this with one query? Cheers, lee -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Fulltext Search help
Try to search in keyword table:
select * from keywords WHERE MATCH(keyword_txt) AGAINST
('$radio_keyword' IN BOOLEAN MODE);
If it works the problem is in the join.
Santino
$query = SELECT page.* FROM `page` LEFT JOIN `keywords` USING
(`page_id`) WHERE MATCH (`keywords`.`keyword_txt`) AGAINST
('$radio_keyword' IN BOOLEAN MODE);
At 20:05 -0400 15-10-2004, leegold wrote:
On Fri, 15 Oct 2004 15:00:10 -0700, Chris W. Parker
[EMAIL PROTECTED] said:
leegold mailto:[EMAIL PROTECTED]
on Friday, October 15, 2004 2:32 PM said:
I do fulltext search on work. And AFAIK the search will not find
work. For that matter the seach will not find ingm. How do I
implement in MYSQL/PHP a search that will have this action?
please share the current query you are trying to use and we can go from
there.
Well, my current query is below, it searchs a text type field full of
keywords with each record. The eventual user now said she wants a search
action like I described above, kinda like a find this string that a
text editor does. I would love to keep using Fulltext cause it has
awesome features, but the wildcard can not be prepended *and* appended
to a search term ie. *work*, no can do AFAIK. He's my current query in
php,
$query = SELECT page.* FROM `page` LEFT JOIN `keywords` USING
(`page_id`) WHERE MATCH (`keywords`.`keyword_txt`) AGAINST
('$radio_keyword' IN BOOLEAN MODE);
So I guess I could remove the fulltext index and reindex and do
...WHERE field LIKE '%string%';. But maybe there's a better way? I
wish I could do *searchstring* in Fulltext even if the speed was slow as
molasass it's the spec the user wants.
Lee G.
however, something simple is the following:
SELECT *
FROM table
WHERE field LIKE '%string%';
hth,
chris.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Fulltext Search help
On Fri, 15 Oct 2004 20:05:57 -0400, leegold [EMAIL PROTECTED] said: On Fri, 15 Oct 2004 15:00:10 -0700, Chris W. Parker ... But maybe there's a better way? I wish I could do *searchstring* in Fulltext even if the speed was slow as molasass it's the spec the user wants. Of course *searchstring* would invalidate the indexing. Thanks. Lee G. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Fulltext Search help
leegold mailto:[EMAIL PROTECTED] on Friday, October 15, 2004 2:32 PM said: I do fulltext search on work. And AFAIK the search will not find work. For that matter the seach will not find ingm. How do I implement in MYSQL/PHP a search that will have this action? please share the current query you are trying to use and we can go from there. however, something simple is the following: SELECT * FROM table WHERE field LIKE '%string%'; hth, chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fulltext search
Try this:
select column1, match(column1) against ('+orange -fruit' IN BOOLEAN MODE) as score
from some_table where match(column1) against('+orange -fruit' IN BOOLEAN MODE)
order by score desc
this way you have your results ordered by relevance, and you also get the relevance
value in
the result if you want to.
Remi Mikalsen
E-Mail: [EMAIL PROTECTED]
URL:http://www.iMikalsen.com
On 5 Oct 2004 at 10:53, Ed Lazor wrote:
Is there a way to have boolean fulltext searches and still have results
automatically sorted in order of decreasing relevance?
It would be nice to enter a search for
+orange -fruit
And have the results come back in order of decreasing relevance.
Thanks,
Ed
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: fulltext search
-Original Message-
Try this:
select column1, match(column1) against ('+orange -fruit' IN BOOLEAN MODE)
as score
from some_table where match(column1) against('+orange -fruit' IN BOOLEAN
MODE)
order by score desc
this way you have your results ordered by relevance, and you also get the
relevance value in
the result if you want to.
Looks interesting. I'm not sure if it's working. I'm looking at the
results of running the query manually against the database and every record
in the result set has a score value of 1. Is that the way it should be?
-Ed
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: fulltext search
- If you use one word in your search, 1 is a probable score, because all the results
that
appear have the same relevance (they all contain that word!).
- If you use two words, where the second isn't present in all results, you shouldn't
get
relevance value 1 in all results.
- If you use the example query (+orange -fruit) it's also natural that the relevance
value is 1,
because it's a very strict query.
If you go to my site (imikalsen.com), and try the internal search engine, you will see
a
practical application of the query. On the site, the result with the highest relevance
value is
always given 100% relevance. The relative relevance is calculated from the relevance
the
other results have compared to the most relevant. There might not be a linear
relationship
between the relevance values, but for my use it's enough.
Try searching for: mikalsen (all 100% - relevance value = 2)
Try searching for: mikalsen remi (different values)
100% is for the results that include remi AND mikalsen, the rest get 50%
I checked the query I sent you, and it works the way I wrote it.
If you are looking for more advanced relevance values (higher relevance if a word
appears
many times in a certain text, etc.) I'm not sure if I can help you.
Remi Mikalsen
E-Mail: [EMAIL PROTECTED]
URL:http://www.iMikalsen.com
On 5 Oct 2004 at 12:42, Ed Lazor wrote:
-Original Message-
Try this:
select column1, match(column1) against ('+orange -fruit' IN BOOLEAN MODE)
as score
from some_table where match(column1) against('+orange -fruit' IN BOOLEAN
MODE)
order by score desc
this way you have your results ordered by relevance, and you also get the
relevance value in
the result if you want to.
Looks interesting. I'm not sure if it's working. I'm looking at the
results of running the query manually against the database and every record
in the result set has a score value of 1. Is that the way it should be?
-Ed
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: fulltext search
-Original Message- - If you use one word in your search, 1 is a probable score, because all the results that appear have the same relevance (they all contain that word!). - If you use two words, where the second isn't present in all results, you shouldn't get relevance value 1 in all results. - If you use the example query (+orange -fruit) it's also natural that the relevance value is 1, because it's a very strict query. If you go to my site (imikalsen.com), and try the internal search engine, you will see a practical application of the query. On the site, the result with the highest relevance value is always given 100% relevance. The relative relevance is calculated from the relevance the other results have compared to the most relevant. There might not be a linear relationship between the relevance values, but for my use it's enough. Try searching for: mikalsen (all 100% - relevance value = 2) Try searching for: mikalsen remi (different values) 100% is for the results that include remi AND mikalsen, the rest get 50% I checked the query I sent you, and it works the way I wrote it. I definitely appreciate the query - thanks. If you are looking for more advanced relevance values (higher relevance if a word appears many times in a certain text, etc.) I'm not sure if I can help you. I think your help has been great. I wasn't sure if I understood the scoring correctly, but what you've written above helped to clarify things a lot. -Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Fulltext Search takes 17 sec.
Hmmm... looks like it may be impossible to do fulltext searching on 3 million rows as
few have chimed in on this one.
Just to add some background to my setup, I am currently running on MySQL 4.0.17 (plan
to upgrade soon.. will this help??) and I created the index that took 9 minutes to
process using phpmyadmin. I did notice that when I created the fulltext index that
the fulltext has field size of 1 when looking at the keys in phpmyadmin. More
specifically, it looks like this:
KeynameTypeCardinalityAction__Field
PRIMARY...PRIMARY.3237981.product_id
search_text...FULLTEXT3237981.search_text..1
Also, would tuning mysql settings get the processing time down from 5-40 seconds to
something more reasonable like 0.3 - 1.0 sec? My current system variables are:
ft_min_word_len: 3
ft_max_word_len: 100
ft_max_word_len_for_sort: 20
max_heap_table_size: 16777216
max_join_size: 4294967295
max_seeks_for_key: 4294967295
max_sort_length: 1024
myisam_max_extra_sort_file_size: 268435456
myisam_max_sort_file_size: 2147483647
myisam_sort_buffer_size: 8388608
query_alloc_block_size: 8192
read_buffer_size: 131072
sort_buffer_size: 89128952
tmp_table_size: 33554432
I think I've heard of people getting good results will fulltext in under a second with
3 million rows+ so I think its possible...
Help greatly appreciated,
- John
- Original Message
I am trying to do a fulltext search on my database of 3.3 million rows (~ 1 Gb). I
attempted to make the searching faster by concatenating all columns I wanted to search
and placed them in a table:
product_fulltext (product_id, search_text)
Then I added the fulltext index:
ALTER TABLE product_fulltext ADD FULLTEXT search_ft (search_text)
The index was created in about 9 minutes.
Next, I need the results grouped into categories. For instance, if I search for
'Mountain' I would like to see the top 3 results for each category. For instance
'mountain' might return:
Apparel
Mountainsmith Backpack
Mountain Hardware Sub Zero Jacket
Mountain Horse Stable Loafer
Food Beverage
Green Mountain Coffee
Mountain Grown Fodgers
Mountain Berry Tart
Video Games
No Fear Downhill Mountain Biking
Mountain Climber - Xbox
Cliff Hanger
Books
Mountain High, Mountain Rescue
Mountain Bike Magic
Go Tell It on the Mountain
.. etc ...
Obviously doing a fulltext search on each category individually wouldn't be fast since
there are about 20 categories. I decided instead to make a TEMPORARY TABLE, insert all
the matching product_ids into it, then group by category and limit for the top 3.
Unfortunately, the temporary table insert combined with the fulltext query takes much
too
long:
CREATE TEMPORARY TABLE temp_table_326 SELECT product_id FROM product_fulltext WHERE
MATCH
( search_text ) AGAINST ('Mountain')
Query took: 17.46 seconds
So I tried a plain-jane fulltext select query to see if the temp table was the issue:
SELECT product_id FROM product_fulltext WHERE MATCH ( search_text ) AGAINST ('United
States')
Executed in: 13.52 seconds
At this point, I haven't even grouped by the top 3 results per category (not sure how I
will do that yet) or joined the products on any tables for images etc. I didn't set
any
LIMIT parameters because if I said LIMIT 20, all 20 matches could be Apparel products
and the other categories would not show up. The BEST performance I can get is about 5
seconds on a single search term (as opposed to the 2 terms in 'United States')
I'm at a loss here...
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Fulltext Search takes 17 sec.
We Fulltext index multi million rows with very good response time. The
cardinality of `1` is okay. Did you fulltext index the rows as is or did you
create some sort of combined field?
-Original Message-
From: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: 8/12/04 7:43 AM
Subject: RE: Fulltext Search takes 17 sec.
Hmmm... looks like it may be impossible to do fulltext searching on 3
million rows as few have chimed in on this one.
Just to add some background to my setup, I am currently running on MySQL
4.0.17 (plan to upgrade soon.. will this help??) and I created the index
that took 9 minutes to process using phpmyadmin. I did notice that when
I created the fulltext index that the fulltext has field size of 1
when looking at the keys in phpmyadmin. More specifically, it looks
like this:
KeynameTypeCardinalityAction__Field
PRIMARY...PRIMARY.3237981.product_id
search_text...FULLTEXT3237981.search_text..1
Also, would tuning mysql settings get the processing time down from 5-40
seconds to something more reasonable like 0.3 - 1.0 sec? My current
system variables are:
ft_min_word_len: 3
ft_max_word_len: 100
ft_max_word_len_for_sort: 20
max_heap_table_size: 16777216
max_join_size: 4294967295
max_seeks_for_key: 4294967295
max_sort_length: 1024
myisam_max_extra_sort_file_size: 268435456
myisam_max_sort_file_size: 2147483647
myisam_sort_buffer_size: 8388608
query_alloc_block_size: 8192
read_buffer_size: 131072
sort_buffer_size: 89128952
tmp_table_size: 33554432
I think I've heard of people getting good results will fulltext in under
a second with 3 million rows+ so I think its possible...
Help greatly appreciated,
- John
- Original Message
I am trying to do a fulltext search on my database of 3.3 million rows
(~ 1 Gb). I
attempted to make the searching faster by concatenating all columns I
wanted to search
and placed them in a table:
product_fulltext (product_id, search_text)
Then I added the fulltext index:
ALTER TABLE product_fulltext ADD FULLTEXT search_ft (search_text)
The index was created in about 9 minutes.
Next, I need the results grouped into categories. For instance, if I
search for
'Mountain' I would like to see the top 3 results for each category. For
instance
'mountain' might return:
Apparel
Mountainsmith Backpack
Mountain Hardware Sub Zero Jacket
Mountain Horse Stable Loafer
Food Beverage
Green Mountain Coffee
Mountain Grown Fodgers
Mountain Berry Tart
Video Games
No Fear Downhill Mountain Biking
Mountain Climber - Xbox
Cliff Hanger
Books
Mountain High, Mountain Rescue
Mountain Bike Magic
Go Tell It on the Mountain
.. etc ...
Obviously doing a fulltext search on each category individually wouldn't
be fast since
there are about 20 categories. I decided instead to make a TEMPORARY
TABLE, insert all
the matching product_ids into it, then group by category and limit for
the top 3.
Unfortunately, the temporary table insert combined with the fulltext
query takes much too
long:
CREATE TEMPORARY TABLE temp_table_326 SELECT product_id FROM
product_fulltext WHERE MATCH
( search_text ) AGAINST ('Mountain')
Query took: 17.46 seconds
So I tried a plain-jane fulltext select query to see if the temp table
was the issue:
SELECT product_id FROM product_fulltext WHERE MATCH ( search_text )
AGAINST ('United
States')
Executed in: 13.52 seconds
At this point, I haven't even grouped by the top 3 results per category
(not sure how I
will do that yet) or joined the products on any tables for images etc.
I didn't set any
LIMIT parameters because if I said LIMIT 20, all 20 matches could be
Apparel products
and the other categories would not show up. The BEST performance I can
get is about 5
seconds on a single search term (as opposed to the 2 terms in 'United
States')
I'm at a loss here...
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext Search takes 17 sec.
Missed your original message. What do you mean you concatenated the
columns you wanted to search on and placed them in a table? You should
just create the full text index on multiple columns. For instance, in a
contacts table, you may have firstname, lastname. So you would create a
full text index like:
FULLTEXT namesearch(firstname,lastname)
You don't show the value for the key_buffer_size variable. This is very
important for taking advantage of your indexes. Here is a link for the
documentation:
http://dev.mysql.com/doc/mysql/en/Server_system_variables.html
As for get the top three in each category. That's a tough one. You may
try using a GROUP BY with the GROUP_CONCAT function. This would give
you one line per group. And limit the groups returns by setting a max
length for the group concat. Although this is a kludge since you are
limiting the amount of text returned rather than the number of values
concatenated.
The other way would be to just get all the matched rows, sorted and/or
grouped appropriately, and then filtering the list with your front end
(i.e. php). If the physical size of the text returned is not that
large, it should process it fairly quickly.
On Aug 12, 2004, at 8:43 AM, [EMAIL PROTECTED] wrote:
Hmmm... looks like it may be impossible to do fulltext searching on 3
million rows as few have chimed in on this one.
Just to add some background to my setup, I am currently running on
MySQL 4.0.17 (plan to upgrade soon.. will this help??) and I created
the index that took 9 minutes to process using phpmyadmin. I did
notice that when I created the fulltext index that the fulltext has
field size of 1 when looking at the keys in phpmyadmin. More
specifically, it looks like this:
KeynameTypeCardinalityAction__Field
PRIMARY...PRIMARY.3237981.product_id
search_text...FULLTEXT3237981.search_text..1
Also, would tuning mysql settings get the processing time down from
5-40 seconds to something more reasonable like 0.3 - 1.0 sec? My
current system variables are:
ft_min_word_len: 3
ft_max_word_len: 100
ft_max_word_len_for_sort: 20
max_heap_table_size: 16777216
max_join_size: 4294967295
max_seeks_for_key: 4294967295
max_sort_length: 1024
myisam_max_extra_sort_file_size: 268435456
myisam_max_sort_file_size: 2147483647
myisam_sort_buffer_size: 8388608
query_alloc_block_size: 8192
read_buffer_size: 131072
sort_buffer_size: 89128952
tmp_table_size: 33554432
I think I've heard of people getting good results will fulltext in
under a second with 3 million rows+ so I think its possible...
Help greatly appreciated,
- John
- Original Message
I am trying to do a fulltext search on my database of 3.3 million rows
(~ 1 Gb). I
attempted to make the searching faster by concatenating all columns I
wanted to search
and placed them in a table:
product_fulltext (product_id, search_text)
Then I added the fulltext index:
ALTER TABLE product_fulltext ADD FULLTEXT search_ft (search_text)
The index was created in about 9 minutes.
Next, I need the results grouped into categories. For instance, if I
search for
'Mountain' I would like to see the top 3 results for each category.
For instance
'mountain' might return:
Apparel
Mountainsmith Backpack
Mountain Hardware Sub Zero Jacket
Mountain Horse Stable Loafer
Food Beverage
Green Mountain Coffee
Mountain Grown Fodgers
Mountain Berry Tart
Video Games
No Fear Downhill Mountain Biking
Mountain Climber - Xbox
Cliff Hanger
Books
Mountain High, Mountain Rescue
Mountain Bike Magic
Go Tell It on the Mountain
.. etc ...
Obviously doing a fulltext search on each category individually
wouldn't be fast since
there are about 20 categories. I decided instead to make a TEMPORARY
TABLE, insert all
the matching product_ids into it, then group by category and limit for
the top 3.
Unfortunately, the temporary table insert combined with the fulltext
query takes much too
long:
CREATE TEMPORARY TABLE temp_table_326 SELECT product_id FROM
product_fulltext WHERE MATCH
( search_text ) AGAINST ('Mountain')
Query took: 17.46 seconds
So I tried a plain-jane fulltext select query to see if the temp table
was the issue:
SELECT product_id FROM product_fulltext WHERE MATCH ( search_text )
AGAINST ('United
States')
Executed in: 13.52 seconds
At this point, I haven't even grouped by the top 3 results per
category (not sure how I
will do that yet) or joined the products on any tables for images etc.
I didn't set any
LIMIT parameters because if I said LIMIT 20, all 20 matches could be
Apparel products
and the other categories would not show up. The BEST performance I
can get is about 5
seconds on a single search term (as opposed to the 2 terms in 'United
States')
I'm at a loss here...
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To
RE: Fulltext Search takes 17 sec.
Hi Victor,
The fulltext index was created on 1 column only that is of type text.
The benchmarks I get are very inconsistant...
SELECT product_id FROM product_fulltext WHERE MATCH ( search_text ) AGAINST ('China')
Query took 20.17 seconds
SELECT product_id FROM product_fulltext WHERE MATCH ( search_text ) AGAINST
('Mongolia')
Query took 0.43 seconds
SELECT product_id FROM product_fulltext WHERE MATCH ( search_text ) AGAINST ('russia')
Query took 6.18 seconds
SELECT product_id FROM product_fulltext WHERE MATCH ( search_text ) AGAINST ('United
States')
Query took 35.57 seconds
SELECT product_id FROM product_fulltext WHERE MATCH ( search_text ) AGAINST ('Chicago')
Query took 11.81 seconds
SELECT product_id FROM product_fulltext WHERE MATCH ( search_text ) AGAINST ('New
York')
Query took 43.14 seconds
SELECT product_id FROM product_fulltext WHERE MATCH ( search_text ) AGAINST
('heserfretzel')
Query took 0.04 seconds
The last word I made up. It seems to be directly proportional to the number of
results it pulls up.
:(
- John
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Fulltext Search takes 17 sec.
On your two word plus searches, `New York` for example, have you tried using
`IN BOOLEAN MODE` to reduce the number of false positives?
AGAINST(New York IN BOOLEAN MODE)
-Original Message-
From: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: 8/12/04 8:33 AM
Subject: RE: Fulltext Search takes 17 sec.
Hi Victor,
The fulltext index was created on 1 column only that is of type
text.
The benchmarks I get are very inconsistant...
SELECT product_id FROM product_fulltext WHERE MATCH ( search_text )
AGAINST ('China')
Query took 20.17 seconds
SELECT product_id FROM product_fulltext WHERE MATCH ( search_text )
AGAINST ('Mongolia')
Query took 0.43 seconds
SELECT product_id FROM product_fulltext WHERE MATCH ( search_text )
AGAINST ('russia')
Query took 6.18 seconds
SELECT product_id FROM product_fulltext WHERE MATCH ( search_text )
AGAINST ('United States')
Query took 35.57 seconds
SELECT product_id FROM product_fulltext WHERE MATCH ( search_text )
AGAINST ('Chicago')
Query took 11.81 seconds
SELECT product_id FROM product_fulltext WHERE MATCH ( search_text )
AGAINST ('New York')
Query took 43.14 seconds
SELECT product_id FROM product_fulltext WHERE MATCH ( search_text )
AGAINST ('heserfretzel')
Query took 0.04 seconds
The last word I made up. It seems to be directly proportional to the
number of results it pulls up.
:(
- John
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Fulltext Search takes 17 sec.
Hi Brent,
Oops, sorry about forgetting to post the key buffer size, its:
key_buffer_size 262144000
As far as the combined column I'm using, I did it to make the fulltext as simple as
possible and keep the index at only 1 column. I am aware that I can create an index
for multiple columns but basically I just selected all the columns that may contain
keywords for each product and tossed them into 1 table with the PK as product_id.
Just thought this might help performance... maybe I'm wrong, I don't know.
If I can ever get the MATCH, AGAINST query down to a faster speed then I will really
focus on getting the top 3 for each category. Sorting into categories in PHP would
work but I'd need a much larger dataset in order to insure that I got all the possible
matches for each category... what if there was only 1 match in the 'Movies' category
and it was at the bottom of the results...? I'm thinking that we will have to use
some other method such as listing by relavence (fulltext ordering) then showing
Search within: Category X, Category Y, Category Z... links on the side (like Ebay I
guess). Still the problem arises as to which categories qualify. It would be nice to
order them by the category with the most matches but I doubt that can be accomplished.
Previously I had not been using a LIMIT because I was going to do processing for
category grouping etc. However, if I do use a LIMIT, the query speeds are almost
totally dependant on the number of rows returned:
WITH LIMIT:
SELECT product_id FROM product_fulltext WHERE MATCH ( search_text ) AGAINST ('san
francisco') LIMIT 1000
Query doine in 2.01 seconds
Num Rows: 1000
WITHOUT LIMIT:
SELECT product_id FROM product_fulltext WHERE MATCH ( search_text ) AGAINST ('san
francisco')
Query doine in 13.45 seconds
Num Rows: 9287
Strange. Is this typical or do I need to tweek my system variables?
- John
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext Search takes 17 sec.
If you really want to test the raw query speed, then do a SELECT
COUNT(*). That will return just a count of the number of records found,
eliminating any speed issues caused by display processing or transfer
speeds from the database to the interface. Adding a limit usually
speeds things up since you limit the amount of data being transferred
out of the database. If your front end and database are not on the same
machine, then the amount of data being transferred over the network can
have a huge impact.
What does your EXPLAIN look like for these queries?
On Aug 12, 2004, at 10:23 AM, [EMAIL PROTECTED] wrote:
Hi Brent,
Oops, sorry about forgetting to post the key buffer size, its:
key_buffer_size 262144000
As far as the combined column I'm using, I did it to make the
fulltext as simple as possible and keep the index at only 1 column. I
am aware that I can create an index for multiple columns but basically
I just selected all the columns that may contain keywords for each
product and tossed them into 1 table with the PK as product_id. Just
thought this might help performance... maybe I'm wrong, I don't know.
If I can ever get the MATCH, AGAINST query down to a faster speed then
I will really focus on getting the top 3 for each category. Sorting
into categories in PHP would work but I'd need a much larger dataset
in order to insure that I got all the possible matches for each
category... what if there was only 1 match in the 'Movies' category
and it was at the bottom of the results...? I'm thinking that we
will have to use some other method such as listing by relavence
(fulltext ordering) then showing Search within: Category X, Category
Y, Category Z... links on the side (like Ebay I guess). Still the
problem arises as to which categories qualify. It would be nice to
order them by the category with the most matches but I doubt that can
be accomplished.
Previously I had not been using a LIMIT because I was going to do
processing for category grouping etc. However, if I do use a LIMIT,
the query speeds are almost totally dependant on the number of rows
returned:
WITH LIMIT:
SELECT product_id FROM product_fulltext WHERE MATCH ( search_text )
AGAINST ('san francisco') LIMIT 1000
Query doine in 2.01 seconds
Num Rows: 1000
WITHOUT LIMIT:
SELECT product_id FROM product_fulltext WHERE MATCH ( search_text )
AGAINST ('san francisco')
Query doine in 13.45 seconds
Num Rows: 9287
Strange. Is this typical or do I need to tweek my system variables?
- John
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext Search takes 17 sec.
Thanks for all your help guys,
Using COUNT(*) I get the following:
mysql SELECT COUNT(*) FROM product_fulltext WHERE MATCH(search_text) AGAINST('blue
jeans');
+--+
| COUNT(*) |
+--+
|51513 |
+--+
1 row in set (48.58 sec)
The EXPLAIN for the fulltext queries look like this:
mysql EXPLAIN SELECT COUNT(*) FROM product_fulltext WHERE MATCH(search_text)
AGAINST('blue jeans');
+--+--+---+---+-+--+--+-+
| table| type | possible_keys | key | key_len | ref | rows |
Extra |
+--+--+---+---+-+--+--+-+
| product_fulltext | fulltext | search_ft | search_ft | 0 | |1 |
Using where |
+--+--+---+---+-+--+--+-+
1 row in set (0.00 sec)
Seems like the explain is working properly.
But even using COUNT and not pulling any data from the db, mysql still seems to lag
significantly depending on the number of fulltext matches.
mysql SELECT COUNT(*) FROM product_fulltext WHERE MATCH(search_text)
AGAINST('Oregon');
+--+
| COUNT(*) |
+--+
| 1876 |
+--+
1 row in set (3.14 sec)
mysql SELECT COUNT(*) FROM product_fulltext WHERE MATCH(search_text) AGAINST('Brown');
+--+
| COUNT(*) |
+--+
|18510 |
+--+
1 row in set (21.19 sec)
mysql SELECT COUNT(*) FROM product_fulltext WHERE MATCH(search_text) AGAINST('Black')
LIMIT 10;
+--+
| COUNT(*) |
+--+
| 120309 |
+--+
1 row in set (1 min 25.00 sec)
Ouch, that last one hurt.
- John
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext Search takes 17 sec.
Those times shouldn't be that far apart. What is the hardware (RAM,
CPU, etc.) and OS you are running this on? Is there anything else
running on it that might be causing memory to page out?
If you are running Unix, try running: vm_stat 1
That will show you memory stats every second. Watch the pagein/pageout
columns when you have a query running. Ideally, they should be zero.
Otherwise you are basically using your hard drive as RAM, which would
be the cause of your slow down.
On Aug 12, 2004, at 12:53 PM, [EMAIL PROTECTED] wrote:
Thanks for all your help guys,
Using COUNT(*) I get the following:
mysql SELECT COUNT(*) FROM product_fulltext WHERE MATCH(search_text)
AGAINST('blue jeans');
+--+
| COUNT(*) |
+--+
|51513 |
+--+
1 row in set (48.58 sec)
The EXPLAIN for the fulltext queries look like this:
mysql EXPLAIN SELECT COUNT(*) FROM product_fulltext WHERE
MATCH(search_text) AGAINST('blue jeans');
+--+--+---+---+-
+--+--+-+
| table| type | possible_keys | key | key_len |
ref | rows | Extra |
+--+--+---+---+-
+--+--+-+
| product_fulltext | fulltext | search_ft | search_ft | 0 |
|1 | Using where |
+--+--+---+---+-
+--+--+-+
1 row in set (0.00 sec)
Seems like the explain is working properly.
But even using COUNT and not pulling any data from the db, mysql still
seems to lag significantly depending on the number of fulltext
matches.
mysql SELECT COUNT(*) FROM product_fulltext WHERE MATCH(search_text)
AGAINST('Oregon');
+--+
| COUNT(*) |
+--+
| 1876 |
+--+
1 row in set (3.14 sec)
mysql SELECT COUNT(*) FROM product_fulltext WHERE MATCH(search_text)
AGAINST('Brown');
+--+
| COUNT(*) |
+--+
|18510 |
+--+
1 row in set (21.19 sec)
mysql SELECT COUNT(*) FROM product_fulltext WHERE MATCH(search_text)
AGAINST('Black') LIMIT 10;
+--+
| COUNT(*) |
+--+
| 120309 |
+--+
1 row in set (1 min 25.00 sec)
Ouch, that last one hurt.
- John
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Fulltext Search takes 17 sec.
Thanks for the vmstat tip. I ran vmstat 1 on the query on a slightly quicker query so
I wouldn't have a ton of numbers to post from the vmstat.
mysql SELECT COUNT(*) FROM product_fulltext WHERE MATCH(search_text) AGAINST('Pink');
+--+
| COUNT(*) |
+--+
|12231 |
+--+
1 row in set (8.05 sec)
procs memoryswap io system cpu
r b w swpd free buff cache si sobibo incs us sy id
0 0 0 200888 29572 14820 356696 0 0 0 0 53140 0 0 100
0 1 0 200888 28356 14828 357772 0 0 1084 0 614 128 1 0 99
0 1 0 200888 25932 14836 360204 0 0 243228 835 360 0 0 99
0 1 0 200888 24412 14836 362944 0 0 2740 0 867 398 0 1 99
0 1 0 200888 21792 14840 365560 0 0 261612 859 374 0 0 100
0 1 0 200888 18664 14840 368688 0 0 3128 0 903 400 0 1 99
0 1 0 200888 16016 14840 371336 0 0 2648 0 870 390 0 0 100
0 1 0 200888 13500 14844 373848 0 0 251224 870 372 0 0 99
0 1 0 200888 11368 14840 375984 0 0 2132 0 822 392 0 0 99
0 0 0 200888 10100 14848 377244 0 0 126012 717 289 0 0 100
0 0 0 200888 10100 14828 377264 0 0 0 0 52420 0 0 100
I really don't know what I'm looking for here, is bi and bo the page-in and
page-out variables you were talking about, or is it si and so. The system
shouldn't be taxed at all since I am the only user on the machine and its not running
anything else at the moment.
The server is a Dual Xeon 2GHz, 512 MB RAM, 72 GB hard drive running on Linux RedHat
7.3
I did notice that the key_buffer_size and sort_buffer_size were a little high (I guess
I though the machine had 1Gb of RAM instead of 512) so I decreased the key_buffer_size
to 125Mb and the sort_buffer_size to 45 Mb. Restarted mysql and ran an identical
query, the result was about 20% slower after I lowered the buffer sizes.(Guess it
didn't help)
- John
- John
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext Search takes 17 sec.
Grumble, grumble. I was hoping vmstat would tell us more. In a
nutshell, every system has a bottleneck and the bottleneck is always
CPU, RAM, I/O or Network. Nutshells are great since they make things
seem so simple. You've got a 1 in 4 chance sf picking the right piece
that is your bottleneck without doing any analysis.
What vmstat does tell us is that the bottleneck is not the CPU, since
it is idle almost the entire time (the last column). Your memory swapd,
si, so columns don't change, so the OS is fine with the RAM. So we've
ruled out CPU and RAM. At least as far as the OS is concerned. That
leaves disk and network. Well, it's not the network, since we are not
using it. So that leaves I/O, which is almost always disk.
Your bi (block in) column climbs as does your interrupt (in) and
context switching (cs). A process can be kicked off the CPU (interrupt
and/or context switching) because it lost priority or because it is
blocked (i.e. waiting for data). Since I'm guessing I/O is your
problem, you may try running iotstat now. That will show you the disk
and CPU activity.
Now, all these numbers are from the OS point of view, not MySQL. So if
MySQL has a low priority (nice value), it won't be given enough
resources to tax any part of the system. That's an extreme example, you
won't likely need to change the nice value of MySQL. But for fun, you
could try changing the nice value of MySQL on your system to see how it
affects things. But this isn't a technique you should rely on.
On Aug 12, 2004, at 2:50 PM, [EMAIL PROTECTED] wrote:
Thanks for the vmstat tip. I ran vmstat 1 on the query on a slightly
quicker query so I wouldn't have a ton of numbers to post from the
vmstat.
mysql SELECT COUNT(*) FROM product_fulltext WHERE MATCH(search_text)
AGAINST('Pink');
+--+
| COUNT(*) |
+--+
|12231 |
+--+
1 row in set (8.05 sec)
procs memoryswap io system
cpu
r b w swpd free buff cache si sobibo incs
us sy id
0 0 0 200888 29572 14820 356696 0 0 0 0 53140
0 0 100
0 1 0 200888 28356 14828 357772 0 0 1084 0 614 128
1 0 99
0 1 0 200888 25932 14836 360204 0 0 243228 835 360
0 0 99
0 1 0 200888 24412 14836 362944 0 0 2740 0 867 398
0 1 99
0 1 0 200888 21792 14840 365560 0 0 261612 859 374
0 0 100
0 1 0 200888 18664 14840 368688 0 0 3128 0 903 400
0 1 99
0 1 0 200888 16016 14840 371336 0 0 2648 0 870 390
0 0 100
0 1 0 200888 13500 14844 373848 0 0 251224 870 372
0 0 99
0 1 0 200888 11368 14840 375984 0 0 2132 0 822 392
0 0 99
0 0 0 200888 10100 14848 377244 0 0 126012 717 289
0 0 100
0 0 0 200888 10100 14828 377264 0 0 0 0 52420
0 0 100
I really don't know what I'm looking for here, is bi and bo the
page-in and page-out variables you were talking about, or is it si
and so. The system shouldn't be taxed at all since I am the only
user on the machine and its not running anything else at the moment.
The server is a Dual Xeon 2GHz, 512 MB RAM, 72 GB hard drive running
on Linux RedHat 7.3
I did notice that the key_buffer_size and sort_buffer_size were a
little high (I guess I though the machine had 1Gb of RAM instead of
512) so I decreased the key_buffer_size to 125Mb and the
sort_buffer_size to 45 Mb. Restarted mysql and ran an identical
query, the result was about 20% slower after I lowered the buffer
sizes.(Guess it didn't help)
- John
- John
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext Search takes 17 sec.
Wow, thanks Brent, nice to learn something new (I'm not much of a 'server' guy)
Tried the iostat and got some results while performing a general query:
mysql SELECT count(*) FROM product_fulltext WHERE MATCH(search_text) AGAINST('DISK');
+--+
| count(*) |
+--+
| 4975 |
+--+
1 row in set (11.28 sec)
IOStat Output
--
avg-cpu: %user %nice%sys %idle
0.250.000.00 99.75
Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
dev8-00.00 0.00 0.00 0 0
dev8-10.00 0.00 0.00 0 0
avg-cpu: %user %nice%sys %idle
0.250.000.00 99.75
Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
dev8-0 110.00 1872.0088.00 1872 88
dev8-10.00 0.00 0.00 0 0
avg-cpu: %user %nice%sys %idle
0.250.000.25 99.50
Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
dev8-0 347.00 6200.00 240.00 6200240
dev8-10.00 0.00 0.00 0 0
avg-cpu: %user %nice%sys %idle
1.250.000.75 98.00
Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
dev8-0 140.00 2024.00 1408.00 2024 1408
dev8-10.00 0.00 0.00 0 0
avg-cpu: %user %nice%sys %idle
0.000.000.50 99.50
Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
dev8-0 342.00 6568.00 112.00 6568112
dev8-10.00 0.00 0.00 0 0
avg-cpu: %user %nice%sys %idle
0.000.000.50 99.50
Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
dev8-0 368.00 7600.00 0.00 7600 0
dev8-10.00 0.00 0.00 0 0
avg-cpu: %user %nice%sys %idle
0.250.000.50 99.25
Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
dev8-0 363.00 7160.0088.00 7160 88
dev8-10.00 0.00 0.00 0 0
avg-cpu: %user %nice%sys %idle
0.250.000.50 99.25
Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
dev8-0 371.00 7104.00 0.00 7104 0
dev8-10.00 0.00 0.00 0 0
avg-cpu: %user %nice%sys %idle
0.000.000.25 99.75
Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
dev8-0 343.00 6360.00 0.00 6360 0
dev8-10.00 0.00 0.00 0 0
avg-cpu: %user %nice%sys %idle
0.250.000.50 99.25
Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
dev8-0 360.00 6696.0064.00 6696 64
dev8-10.00 0.00 0.00 0 0
avg-cpu: %user %nice%sys %idle
0.000.000.25 99.75
Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
dev8-0 367.00 7184.00 0.00 7184 0
dev8-10.00 0.00 0.00 0 0
avg-cpu: %user %nice%sys %idle
0.250.000.00 99.75
Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
dev8-0 330.00 6448.0056.00 6448 56
dev8-10.00 0.00 0.00 0 0
avg-cpu: %user %nice%sys %idle
0.250.001.00 98.75
Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
dev8-0 380.00 7176.00 0.00 7176 0
dev8-10.00 0.00 0.00 0 0
avg-cpu: %user %nice%sys %idle
0.000.000.00 100.00
Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
dev8-00.00 0.00 0.00 0 0
dev8-10.00 0.00 0.00 0 0
Not knowing much about this, it looks like the write speed is pretty slow (I guess it
should take longer than reads though).
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FULLTEXT search
OR From the manual: Every correct word in the collection and in the query is weighted according to its significance in the collection or query. http://dev.mysql.com/doc/mysql/en/Fulltext_Search.html If you want AND, look at the boolean full-text searches format: http://dev.mysql.com/doc/mysql/en/Fulltext_Boolean.html On Jul 22, 2004, at 10:17 AM, Schalk Neethling wrote: When doing a MATCH() AGAINST() search. Does a result get returned only when all of the words in the AGAINST() 'tag' matches a document or if any words match. Basically is this an AND or OR type of result that is returned? Thanks! -- Kind Regards Schalk Neethling Web Developer.Designer.Programmer.President Volume4.Development.Multimedia.Branding emotionalize.conceptualize.visualize.realize Tel: +27125468436 Fax: +27125468436 email:[EMAIL PROTECTED] web: www.volume4.co.za This message contains information that is considered to be sensitive or confidential and may not be forwarded or disclosed to any other party without the permission of the sender. If you received this message in error, please notify me immediately so that I can correct and delete the original email. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FULLTEXT search
Match() can be called 'In Boolean Mode' if you wish. See the docs at http://dev.mysql.com/doc/mysql/en/Fulltext_Search.html. PB - Original Message - From: Schalk Neethling To: [EMAIL PROTECTED] Sent: Thursday, July 22, 2004 9:17 AM Subject: FULLTEXT search When doing a MATCH() AGAINST() search. Does a result get returned only when all of the words in the AGAINST() 'tag' matches a document or if any words match. Basically is this an AND or OR type of result that is returned? Thanks! -- Kind Regards Schalk Neethling Web Developer.Designer.Programmer.President Volume4.Development.Multimedia.Branding emotionalize.conceptualize.visualize.realize Tel: +27125468436 Fax: +27125468436 email:[EMAIL PROTECTED] web: www.volume4.co.za This message contains information that is considered to be sensitive or confidential and may not be forwarded or disclosed to any other party without the permission of the sender. If you received this message in error, please notify me immediately so that I can correct and delete the original email. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext search over multiple tables
I think you want a UNION, not a JOIN.
Availabilty of UNION depends on the version you are running.
Remi Mikalsen wrote:
Hello.
I have a problem I believe must have been solved lots of times before. I am doing a
fulltext search on two tables at the same time. The problem is that it takes about 20-30
seconds (two really small tables! 400 entries!!!).
Here is the query:
SELECT DISTINCT object.id_object FROM object , short_info, long_info
WHERE object.owner='2' AND ((short_info.object=object.id_object AND
MATCH (short_info.short_info) AGAINST ('any searchstring' IN BOOLEAN
MODE)) OR (long_info.object=object.id_object AND MATCH
(long_info.long_info) AGAINST ('any searchstring' IN BOOLEAN MODE)));
The columns long_info and short_info are both indexed with fulltext indexes.
If I divide the query in two, and execute the two fulltext boolean searches separately,
they take about 0.1 seconds each! Is there a way to optimize the above query to improve
performance, or is the only solution to execute two queries?
Remodeling the database is not possible in my case. The fulltext search is a small part
of a new search engine for a database that has been on-line for over a year now.
Remi Mikalsen
E-Mail: [EMAIL PROTECTED]
URL:http://www.iMikalsen.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FULLTEXT search in form of MATCH...AGAINST
Hello Robb, Thursday, April 8, 2004, 4:30:46 PM, you wrote: RK I've got several tables with FULLTEXT indexes and on none of them can I get RK this syntax to work. What's up? Your version of MySQL perhaps? -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FULLTEXT search in form of MATCH...AGAINST
Robb Kerr [EMAIL PROTECTED] wrote:
I can't seem to get a FULLTEXT search in the form of MATCH...AGAINST to
work. I even copied verbatim the example listed on mysql.com at
(http://www.mysql.com/doc/en/Fulltext_Search.html). When I use...
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database')
it works as expected. However when I use...
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('following')
it doesn't return any results.
I've got several tables with FULLTEXT indexes and on none of them can I get
this syntax to work. What's up?
Because 'following' is a stopword.
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Victoria Reznichenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext search question: words with numbers (ie DB2)
The other thing to consider is the 50% rule. If more than 50% of the records match, the search is consider irrelevant and no records are returned. So if you have 120 records and 61 have DB2 in them, you won't get a result set. On Mar 31, 2004, at 12:28 AM, Shane Allen wrote: apologies, I forgot to mention that. I was aware of this, and just finished changing the ft_min_word_length to 2 and rebuilding my indexes. searches for 'SAS' or 'net' yield results as expected, but DB2 still does not. -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext search question: words with numbers (ie DB2)
Brent Baisley wrote: The other thing to consider is the 50% rule. If more than 50% of the records match, the search is consider irrelevant and no records are returned. So if you have 120 records and 61 have DB2 in them, you won't get a result set. But the 50% rule is overridden when the search is in boolean mode, isn't it? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext search question: words with numbers (ie DB2)
Hello Shane,
Wednesday, March 31, 2004, 5:43:10 AM, you wrote:
SA using MATCH(description) AGAINST('+db2' IN BOOLEAN MODE) returns no results.
SA Is this expected behaviour? If so, is there a way to circumvent it?
By default, the full text indexing engine doesn't include words with
less than 4 characters in. If you are using MySQL 4 you can change the
minimum length via the ft_min_word_len variable. On MySQL 3.x
there's nothing you can do short of changing the actual source code
and recompiling.
--
Best regards,
Richard Davey
http://www.phpcommunity.org/wiki/296.html
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext search question: words with numbers (ie DB2)
Richard Davey wrote:
Hello Shane,
Wednesday, March 31, 2004, 5:43:10 AM, you wrote:
SA using MATCH(description) AGAINST('+db2' IN BOOLEAN MODE) returns no results.
SA Is this expected behaviour? If so, is there a way to circumvent it?
By default, the full text indexing engine doesn't include words with
less than 4 characters in. If you are using MySQL 4 you can change the
minimum length via the ft_min_word_len variable. On MySQL 3.x
there's nothing you can do short of changing the actual source code
and recompiling.
apologies, I forgot to mention that.
I was aware of this, and just finished changing the ft_min_word_length
to 2 and rebuilding my indexes.
searches for 'SAS' or 'net' yield results as expected, but DB2 still
does not.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fulltext search always returns no results
Don Dikunetsis [EMAIL PROTECTED] wrote:
Hi,
The comments in the fulltext doc page
(http://www.mysql.com/doc/en/Fulltext_Search.html) discuss the issues of
stopwords and over 50% hits, so I did my best to avoid those particular
bombs in my searches.
The subject column contains subjects for message posts/entries, and as
such they're strings of around six words, on average. Here's some searches
that returned blank results:
SELECT subject FROM entry WHERE MATCH (subject) AGAINST ('your');
SELECT subject FROM entry WHERE MATCH (subject) AGAINST ('spam');
SELECT subject FROM entry WHERE MATCH (subject) AGAINST ('hatching');
your is a possible stopword, but the other two are words that appear just
once in the 16 (now 19) records.
For the list in general, here's some things I tried since my last post:
1. Attempted to verify that the table is MyISAM. CHECK TABLE and ANALYZE
TABLE were processed okay, which _seems_ to indicate that the table is
MyISAM:
CHECK TABLE entry; LIMIT 0, 30
TableOp Msg_type Msg_text
entrycheckstatusOK
ANALYZE TABLE entry; LIMIT 0, 30
TableOp Msg_typeMsg_text
entryanalyzestatus Table is already up to date
2. Based on a comment in
(http://www.mysql.com/doc/en/Fulltext_Fine-tuning.html), ran:
ALTER TABLE entry TYPE=MyISAM;
Result: command returned without an error; however, searches still come up
blank.
3. Noted in the fulltext restrictions doc
(http://www.mysql.com/doc/en/Fulltext_Restrictions.html) that fulltext
before 4.1.1 doesn't work with Unicode.
To check the current settings, tried running SHOW CHARACTER SET; and SHOW
COLLATION;, but got errors:
MySQL said: You have an error in your SQL syntax near 'CHARACTER SET' at
line 1
MySQL said: You have an error in your SQL syntax near 'COLLATION' at line 1
So I flushed the data from the table, and reloaded with data with a
character encoding explicitly set at iso-8859-1. However, my searches still
return blank results.
I must admit that at this point I'm stumped!
Could you provide repeatable test case?
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Victoria Reznichenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fulltext search always returns no results
Hi Matt, thanks for shedding light on the version and key_len issues.
Either phpMyAdmin or MySQL is labelling the return of blank results as an
error, thus my use of the term. The output from phpMyAdmin looks like this:
Error
SQL-query:
SELECT subject FROM entry WHERE MATCH (subject) AGAINST ('spam');
MySQL said:
That seemed less than informative to me, so I tried EXPLAIN, hoping to
uncover a clue:
table type possible_keys keykey_len ref rowsExtra
--
entry fulltext subject_index subject_index 0 1 where used
Regarding the search term, in response to your comment I tried different
searches, for words either at the beginning, middle, or end of the strings
which are stored in the subject column, but they all returned blank
results such as seen above.
General questions to the list:
key_len of 0 is okay, I know now . . . but should rows be 1 when the DB has
16 (now 19) records?
Is the syntax I'm using for the query, and to add the index, okay?
Is there an SQL command I can use to look into the index and see if it
actually contains anything?
From: Matt W [EMAIL PROTECTED]
To: Don Dikunetsis [EMAIL PROTECTED], [EMAIL PROTECTED]
Subject: Re: fulltext search always returns no results
Date: Thu, 26 Feb 2004 01:26:17 -0600
Hi Don,
No, full-text search was added in MySQL 3.23.23, I believe (4.0.1 just
added boolean searches along with more speed overall). It doesn't need
to be compiled in or anything, it's there by default. Unless someone
compiled it and actually *removed* the full-text code or something. :-)
Also, key_len of 0 in EXPLAIN is normal.
It sounded like you are getting some kind of error in your first
message? If so, what is it? Are you SURE that the EXACT word you're
searching for is present in the table (for example, with a space, etc.
on either side of it)?
Matt
- Original Message -
From: Don Dikunetsis
Sent: Thursday, February 26, 2004 12:21 AM
Subject: Re: fulltext search always returns no results
Hi, thanks for your reply, but it looks like:
As of Version 3.23.23, MySQL has support for full-text indexing and
searching.
--according to:
http://www.mysql.com/doc/en/Fulltext_Search.html
However, I would be unsurprised (though disappointed) to find that the
answer is some variant of this won't work with your
configuration--my
setup certainly doesn't seem to be responding to the normal setup
instructions for fulltext search.
From: [EMAIL PROTECTED]
Subject: Re: fulltext search always returns no results
Date: Thu, 26 Feb 2004 10:12:42 +1100 (EST)
Excuse if i'm not correct but this may be your problemo ?
MySQL 3.23.55 running on my webhost's Linux box
phpMyAdmin 2.1.0
I didnt think fulltext was in 3.23 wasnt this a Mysql 4 feature ??
Summary: When I run a fulltext search, it always returns no
results. I
have added a fulltext index to the column being searched. Also, I
am
searching for a term that is in the table, but not in more than
50% of
the rows.
I notice that when I add EXPLAIN to my search, the key_len of my
fulltext index is 0. Does that mean my fulltext index is empty,
explaining why my searches never return any results?
Either way, I'd be grateful for any suggestions on how to get
fulltext
search to work!
_
Store more e-mails with MSN Hotmail Extra Storage 4 plans to choose from!
http://click.atdmt.com/AVE/go/onm00200362ave/direct/01/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: fulltext search always returns no results
Hi,
The comments in the fulltext doc page
(http://www.mysql.com/doc/en/Fulltext_Search.html) discuss the issues of
stopwords and over 50% hits, so I did my best to avoid those particular
bombs in my searches.
The subject column contains subjects for message posts/entries, and as
such they're strings of around six words, on average. Here's some searches
that returned blank results:
SELECT subject FROM entry WHERE MATCH (subject) AGAINST ('your');
SELECT subject FROM entry WHERE MATCH (subject) AGAINST ('spam');
SELECT subject FROM entry WHERE MATCH (subject) AGAINST ('hatching');
your is a possible stopword, but the other two are words that appear just
once in the 16 (now 19) records.
For the list in general, here's some things I tried since my last post:
1. Attempted to verify that the table is MyISAM. CHECK TABLE and ANALYZE
TABLE were processed okay, which _seems_ to indicate that the table is
MyISAM:
CHECK TABLE entry; LIMIT 0, 30
TableOp Msg_type Msg_text
entrycheckstatusOK
ANALYZE TABLE entry; LIMIT 0, 30
TableOp Msg_typeMsg_text
entryanalyzestatus Table is already up to date
2. Based on a comment in
(http://www.mysql.com/doc/en/Fulltext_Fine-tuning.html), ran:
ALTER TABLE entry TYPE=MyISAM;
Result: command returned without an error; however, searches still come up
blank.
3. Noted in the fulltext restrictions doc
(http://www.mysql.com/doc/en/Fulltext_Restrictions.html) that fulltext
before 4.1.1 doesn't work with Unicode.
To check the current settings, tried running SHOW CHARACTER SET; and SHOW
COLLATION;, but got errors:
MySQL said: You have an error in your SQL syntax near 'CHARACTER SET' at
line 1
MySQL said: You have an error in your SQL syntax near 'COLLATION' at line 1
So I flushed the data from the table, and reloaded with data with a
character encoding explicitly set at iso-8859-1. However, my searches still
return blank results.
I must admit that at this point I'm stumped!
From: Peter Lovatt [EMAIL PROTECTED]
To: Matt W [EMAIL PROTECTED], Don Dikunetsis
[EMAIL PROTECTED], [EMAIL PROTECTED]
Subject: RE: fulltext search always returns no results
Date: Thu, 26 Feb 2004 07:57:21 -
Hi
Full text can have some strange results.
It ignores common words, like the, if, and, etc as they are not relevant.
If you have a word that appears in most records then it will ignore it,
sometimes returning 0 results even if it is what you were looking for!
The aim of full text is to return the most relevant records. If there are
no
records that are more relevant than any others it may return none.
Could this be the cause?
Peter
---
Excellence in internet and open source software
---
Sunmaia
Birmingham
UK
www.sunmaia.net
tel. 0121-242-1473
International +44-121-242-1473
---
-Original Message-
From: Matt W [mailto:[EMAIL PROTECTED]
Sent: 26 February 2004 07:26
To: Don Dikunetsis; [EMAIL PROTECTED]
Subject: Re: fulltext search always returns no results
Hi Don,
No, full-text search was added in MySQL 3.23.23, I believe (4.0.1 just
added boolean searches along with more speed overall). It doesn't need
to be compiled in or anything, it's there by default. Unless someone
compiled it and actually *removed* the full-text code or something. :-)
Also, key_len of 0 in EXPLAIN is normal.
It sounded like you are getting some kind of error in your first
message? If so, what is it? Are you SURE that the EXACT word you're
searching for is present in the table (for example, with a space, etc.
on either side of it)?
Matt
- Original Message -
From: Don Dikunetsis
Sent: Thursday, February 26, 2004 12:21 AM
Subject: Re: fulltext search always returns no results
Hi, thanks for your reply, but it looks like:
As of Version 3.23.23, MySQL has support for full-text indexing and
searching.
--according to:
http://www.mysql.com/doc/en/Fulltext_Search.html
However, I would be unsurprised (though disappointed) to find that the
answer is some variant of this won't work with your
configuration--my
setup certainly doesn't seem to be responding to the normal setup
instructions for fulltext search.
From: [EMAIL PROTECTED]
Subject: Re: fulltext search always returns no results
Date: Thu, 26 Feb 2004 10:12:42 +1100 (EST)
Excuse if i'm not correct but this may be your problemo ?
MySQL 3.23.55 running on my webhost's Linux box
phpMyAdmin 2.1.0
I didnt think fulltext was in 3.23 wasnt this a Mysql 4 feature ??
Summary: When I run a fulltext search, it always returns no
results. I
have added a fulltext index to the column being searched. Also, I
am
searching for a term that is in the table, but not in more than
50% of
the rows.
I notice that when I add EXPLAIN
Re: fulltext search always returns no results
Excuse if i'm not correct but this may be your problemo ?
MySQL 3.23.55 running on my webhost's Linux box
phpMyAdmin 2.1.0
I didnt think fulltext was in 3.23 wasnt this a Mysql 4 feature ??
Summary: When I run a fulltext search, it always returns no results. I
have added a fulltext index to the column being searched. Also, I am
searching for a term that is in the table, but not in more than 50% of
the rows.
I notice that when I add EXPLAIN to my search, the key_len of my
fulltext index is 0. Does that mean my fulltext index is empty,
explaining why my searches never return any results?
Either way, I'd be grateful for any suggestions on how to get fulltext
search to work!
Config:
MySQL 3.23.55 running on my webhost's Linux box
phpMyAdmin 2.1.0
Long description:
My test database has two tables and 16 rows. The table named entry
starts out looking like this:
Field Type AttributesNullDefaultExtra
---
itemid mediumint(8) UNSIGNED No 0
permid mediumint(8) UNSIGNED No 0
eventtime datetimeNo -00-00 00:00:00
subjectvarchar(255)Yes
event textYes
current_music varchar(255)Yes
current_mood varchar(255)Yes
Indexes
KeynameUniqueField
--
PRIMARYYes itemid
To allow fulltext search, I add a fulltext index via phpMyAdmin's Run
SQL query textarea:
ALTER TABLE entry ADD FULLTEXT subject_index (subject);
I log out of and back into phpMyAdmin. Now there is a new row in the
list of indexes:
KeynameUniqueField
--
PRIMARYYes itemid
subject_index Nosubject
I do a fulltext search for a term I know is in the subject data:
SELECT subject FROM entry WHERE MATCH (subject) AGAINST ('contact');
But I get back an error/empty results:
Error
SQL-query:
SELECT subject FROM entry WHERE MATCH (subject) AGAINST ('contact');
MySQL said:
I run the query again, using EXPLAIN:
EXPLAIN SELECT subject FROM entry WHERE MATCH (subject) AGAINST
('contact');
The query is echoed (with an additional LIMIT apparently added by
phpMyAdmin):
EXPLAIN SELECT subject FROM entry WHERE MATCH (subject) AGAINST
('contact'); LIMIT 0, 30
And the EXPLAIN output is:
table type possible_keys keykey_len ref rows
Extra
--
entry fulltext subject_index subject_index 0 1 where
used
_
Watch high-quality video with fast playback at MSN Video. Free!
http://click.atdmt.com/AVE/go/onm00200365ave/direct/01/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fulltext search always returns no results
Hi, thanks for your reply, but it looks like: As of Version 3.23.23, MySQL has support for full-text indexing and searching. --according to: http://www.mysql.com/doc/en/Fulltext_Search.html However, I would be unsurprised (though disappointed) to find that the answer is some variant of this won't work with your configuration--my setup certainly doesn't seem to be responding to the normal setup instructions for fulltext search. From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: Re: fulltext search always returns no results Date: Thu, 26 Feb 2004 10:12:42 +1100 (EST) Excuse if i'm not correct but this may be your problemo ? MySQL 3.23.55 running on my webhost's Linux box phpMyAdmin 2.1.0 I didnt think fulltext was in 3.23 wasnt this a Mysql 4 feature ?? Summary: When I run a fulltext search, it always returns no results. I have added a fulltext index to the column being searched. Also, I am searching for a term that is in the table, but not in more than 50% of the rows. I notice that when I add EXPLAIN to my search, the key_len of my fulltext index is 0. Does that mean my fulltext index is empty, explaining why my searches never return any results? Either way, I'd be grateful for any suggestions on how to get fulltext search to work! _ Get a FREE online computer virus scan from McAfee when you click here. http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fulltext search always returns no results
i think u have to compile it in as an option, it doesnt as default, i wouldnt be suprised if Mysql 4 did a better job though, trying to convince work to upgrade all machines to Mysql 4 hopefully 4.1 is a task in itself. Hi, thanks for your reply, but it looks like: As of Version 3.23.23, MySQL has support for full-text indexing and searching. --according to: http://www.mysql.com/doc/en/Fulltext_Search.html However, I would be unsurprised (though disappointed) to find that the answer is some variant of this won't work with your configuration--my setup certainly doesn't seem to be responding to the normal setup instructions for fulltext search. From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: Re: fulltext search always returns no results Date: Thu, 26 Feb 2004 10:12:42 +1100 (EST) Excuse if i'm not correct but this may be your problemo ? MySQL 3.23.55 running on my webhost's Linux box phpMyAdmin 2.1.0 I didnt think fulltext was in 3.23 wasnt this a Mysql 4 feature ?? Summary: When I run a fulltext search, it always returns no results. I have added a fulltext index to the column being searched. Also, I am searching for a term that is in the table, but not in more than 50% of the rows. I notice that when I add EXPLAIN to my search, the key_len of my fulltext index is 0. Does that mean my fulltext index is empty, explaining why my searches never return any results? Either way, I'd be grateful for any suggestions on how to get fulltext search to work! _ Get a FREE online computer virus scan from McAfee when you click here. http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fulltext search always returns no results
Hi Don, No, full-text search was added in MySQL 3.23.23, I believe (4.0.1 just added boolean searches along with more speed overall). It doesn't need to be compiled in or anything, it's there by default. Unless someone compiled it and actually *removed* the full-text code or something. :-) Also, key_len of 0 in EXPLAIN is normal. It sounded like you are getting some kind of error in your first message? If so, what is it? Are you SURE that the EXACT word you're searching for is present in the table (for example, with a space, etc. on either side of it)? Matt - Original Message - From: Don Dikunetsis Sent: Thursday, February 26, 2004 12:21 AM Subject: Re: fulltext search always returns no results Hi, thanks for your reply, but it looks like: As of Version 3.23.23, MySQL has support for full-text indexing and searching. --according to: http://www.mysql.com/doc/en/Fulltext_Search.html However, I would be unsurprised (though disappointed) to find that the answer is some variant of this won't work with your configuration--my setup certainly doesn't seem to be responding to the normal setup instructions for fulltext search. From: [EMAIL PROTECTED] Subject: Re: fulltext search always returns no results Date: Thu, 26 Feb 2004 10:12:42 +1100 (EST) Excuse if i'm not correct but this may be your problemo ? MySQL 3.23.55 running on my webhost's Linux box phpMyAdmin 2.1.0 I didnt think fulltext was in 3.23 wasnt this a Mysql 4 feature ?? Summary: When I run a fulltext search, it always returns no results. I have added a fulltext index to the column being searched. Also, I am searching for a term that is in the table, but not in more than 50% of the rows. I notice that when I add EXPLAIN to my search, the key_len of my fulltext index is 0. Does that mean my fulltext index is empty, explaining why my searches never return any results? Either way, I'd be grateful for any suggestions on how to get fulltext search to work! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fulltext search always returns no results
Hi Don, No, full-text search was added in MySQL 3.23.23, I believe (4.0.1 just added boolean searches along with more speed overall). It doesn't need to be compiled in or anything, it's there by default. Unless someone compiled it and actually *removed* the full-text code or something. :-) Also, key_len of 0 in EXPLAIN is normal. It sounded like you are getting some kind of error in your first message? If so, what is it? Are you SURE that the EXACT word you're searching for is present in the table (for example, with a space, etc. on either side of it)? Thanks for enlightening me on that, i only started using it in mysql 4, but i only really use mysql4 now, sometimes 3 and have to painfully downgrade my code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext search
Is it possible to create a InnoDB table and a MyIsam table with fulltext indexes and use a join to search in fulltext indexes? Santino At 18:43 -0600 23-01-2004, Paul DuBois wrote: At 18:34 -0600 1/23/04, Sidar LC. wrote: How can I implement fulltext search engine on InnoDB and MySQL 5. You can't. FULLTEXT indexes are supported only for MyISAM tables. http://www.mysql.com/doc/en/Fulltext_Search.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext search
Santino [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Is it possible to create a InnoDB table and a MyIsam table with fulltext indexes and use a join to search in fulltext indexes? Santino Yup.. you can.. create the tables on the same DB and make a JOIN.. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext search
At 18:34 -0600 1/23/04, Sidar LC. wrote: How can I implement fulltext search engine on InnoDB and MySQL 5. You can't. FULLTEXT indexes are supported only for MyISAM tables. http://www.mysql.com/doc/en/Fulltext_Search.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Fulltext search
when when when will it be available for innodb ? -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Saturday, January 24, 2004 11:43 AM To: Sidar LC.; [EMAIL PROTECTED] Subject: Re: Fulltext search At 18:34 -0600 1/23/04, Sidar LC. wrote: How can I implement fulltext search engine on InnoDB and MySQL 5. You can't. FULLTEXT indexes are supported only for MyISAM tables. http://www.mysql.com/doc/en/Fulltext_Search.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FULLTEXT Search and Hyphens
Michael,
have a look at the sources, especially myisam/ft_parser.c near line 108
and at myisam/ftdefs.h. It should not be difficult to hack the sources to
make the hyphen a real character. This will solve your problem (but could
create some new ones on others types of text input).
Thomas
On Wed, 31 Dec 2003, michael elston wrote:
I am having some trouble with fulltext search when searching a Table
for parts numbers which contain HYPHENS ( - ) and i am 90% sure that is
where the problem is.
My query is:
SELECT * FROM ms_items
where MATCH (it_mnfgID, it_title, it_descrip) AGAINST ('*kk-4835*' IN
BOOLEAN MODE )
What i want to be able to accomplish is say your are searching for a
KK-4835
I want to be able to type in KK-4835, KK4835, or even KK 4835) and
all return the same part. as it stands right now, searching for even
just kk- returns nothing but searching for 4835 will return items
with 4835.
is this a known problem with MySQL or is there a way around it? I am
half tempted to create another column for keywords.
thanks for any help
-me
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FULLTEXT Search and Hyphens
In the last episode (Dec 31), michael elston said:
I am having some trouble with fulltext search when searching a Table
for parts numbers which contain HYPHENS ( - ) and i am 90% sure that is
where the problem is.
My query is:
SELECT * FROM ms_items
where MATCH (it_mnfgID, it_title, it_descrip) AGAINST ('*kk-4835*' IN
BOOLEAN MODE )
What i want to be able to accomplish is say your are searching for a
KK-4835
I want to be able to type in KK-4835, KK4835, or even KK 4835) and
all return the same part. as it stands right now, searching for even
just kk- returns nothing but searching for 4835 will return items
with 4835.
Make sure you have changed ft_min_word_len to 2; I bet KK never got
indexed at all.
Searching for KK4835 probably won't work, since that's one word and
KK-4835 gets indexed as KK and 4835. You may have to parse your
search string and split the words up before calling mysql.
--
Dan Nelson
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FULLTEXT Search and Hyphens
Hello,
Fulltext section of mysql manual: MySQL uses a very
simple parser to split text into words. A word is
any sequence of characters consisting of letters,
digits, ' , and _ . Any word that is present
in the stopword list or is just too short is ignored.
Thus a hyphen would be viewed as a non-word character
and kk-4835 would be split in kk and 4835. And since
the default min. word length is 4, kk would not be
index but 4835 would be. For more info:
http://www.mysql.com/doc/en/Fulltext_Search.html
The only way to search for kk-4835 is to do the search
in Boolean mode and put things in quotes - kk-4835
So your query would look like this:
SELECT * FROM ms_items
where MATCH (it_mnfgID, it_title, it_descrip)
AGAINST ('kk-4835' IN BOOLEAN MODE )
and
I want to be able to type in KK-4835, KK4835, or
even KK 4835)
To be able to search for the variations you described
above I would just parse the search and insert a
hyphen where it is missing and then perform the same
search above.
Hope this helps,
TK
__
Do you Yahoo!?
Find out what made the Top Yahoo! Searches of 2003
http://search.yahoo.com/top2003
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FULLTEXT Search and Hyphens
Sorry about this,
The only way to search for kk-4835 is to do the
search
in Boolean mode and put things in quotes -
kk-4835
So your query would look like this:
SELECT * FROM ms_items
where MATCH (it_mnfgID, it_title, it_descrip)
AGAINST ('kk-4835' IN BOOLEAN MODE )
Put things in quotes not question marks.
TK
__
Do you Yahoo!?
Find out what made the Top Yahoo! Searches of 2003
http://search.yahoo.com/top2003
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fulltext search speed issue with SQL_CALC_FOUND_ROWS
Hello, Could one not store the total while using the index and use select FOUND_ROWS() without SQL_CALC_FOUND_ROWS to retrieve the total? Yes, it could. It is the optimization that wasn't implemented yet. (but it's in the TODO) Once again, thanks for the response. Could you give an estimation (even if rough) of how soon this optimization will be implemented? Thanks, TK Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fulltext search speed issue with SQL_CALC_FOUND_ROWS
Hi! On Dec 04, tk wrote: Hello, Thanks for the response. There is one thing that is not clear however. Regardless of whether or not I perform the fulltext search with or without the SQL_CALC_FOUND_ROWS keyword, the results that I get are exactly the same. Also, the notion of stopping after the limit is reached cannot apply in the fulltext search or otherwise we would only get the first 10 matches but not the first 10 most relevant matches. This leads me to believe that the fulltext search must be looking at all the rows in both cases since it otherwise would not find the same first 10 most relevant records. Hence the question why there should be a difference in time. The difference is that you only need to read 10 rows from the disk without SQL_CALC_FOUND_ROWS. With SQL_CALC_FOUND_ROWS MySQL goes on and reads all rows, it takes time. Finding relevant rows and sorting is based on index only, row data are not read. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: fulltext search speed issue with SQL_CALC_FOUND_ROWS
Hi TK,
There was an optimizer bug in MySQL 4.0
This bug is fixed in 4.0.17 (not yet released)
# Fixed bug when the optimiser did not
# take SQL_CALC_FOUND_ROWS into account
# if LIMIT clause was present. (Bug #1274)
Kind regards
Gunnar
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Donnerstag, 4. Dezember 2003 16:13
To: [EMAIL PROTECTED]
Subject: fulltext search speed issue with SQL_CALC_FOUND_ROWS
I have some_table with 100,000 rows and with an
average of 500 words in some_column of each row. When
i do a fulltext search on this table using a query
such as the following, all of my results are under 0.1
seconds:
SELECT something
FROM some_table
WHERE MATCH (some_column) AGAINST ('some_search_term')
LIMIT 0,10
However, when i add the SQL_CALC_FOUND_ROWS keyword
like in the following query, some queries take longer
than 1 minute:
SELECT SQL_CALC_FOUND_ROWS something
FROM some_table
WHERE MATCH (some_column) AGAINST ('some_search_term')
LIMIT 0,10
How can there be a huge difference in speed if both
queries always return the exact same results?
Thanks,
TK
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fulltext search speed issue with SQL_CALC_FOUND_ROWS
Hello Sergei, Gunnar, and others, thank you for your quick responses. One little mystery remains: Why does one need to read all the row data (with SQL_CALC_FOUND_ROWS) to get the total number of results when using a limit? When the index is used to find relevant rows and sort the results, the code certainly must know how many total results there are. Could one not store the total while using the index and use select FOUND_ROWS() without SQL_CALC_FOUND_ROWS to retrieve the total? Thanks and regards, TK --- Sergei Golubchik [EMAIL PROTECTED] wrote: Hi! On Dec 04, tk wrote: Hello, Thanks for the response. There is one thing that is not clear however. Regardless of whether or not I perform the fulltext search with or without the SQL_CALC_FOUND_ROWS keyword, the results that I get are exactly the same. Also, the notion of stopping after the limit is reached cannot apply in the fulltext search or otherwise we would only get the first 10 matches but not the first 10 most relevant matches. This leads me to believe that the fulltext search must be looking at all the rows in both cases since it otherwise would not find the same first 10 most relevant records. Hence the question why there should be a difference in time. The difference is that you only need to read 10 rows from the disk without SQL_CALC_FOUND_ROWS. With SQL_CALC_FOUND_ROWS MySQL goes on and reads all rows, it takes time. Finding relevant rows and sorting is based on index only, row data are not read. Regards, Sergei __ Do you Yahoo!? Free Pop-Up Blocker - Get it now http://companion.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fulltext search speed issue with SQL_CALC_FOUND_ROWS
Hi! On Dec 05, tk wrote: Hello Sergei, Gunnar, and others, thank you for your quick responses. One little mystery remains: Why does one need to read all the row data (with SQL_CALC_FOUND_ROWS) to get the total number of results when using a limit? When the index is used to find relevant rows and sort the results, the code certainly must know how many total results there are. Yes, but it's different code, it works on a different level and knows nothing about SQL_CALC_FOUND_ROWS :( Could one not store the total while using the index and use select FOUND_ROWS() without SQL_CALC_FOUND_ROWS to retrieve the total? Yes, it could. It is the optimization that wasn't implemented yet. (but it's in the TODO) Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fulltext search speed issue with SQL_CALC_FOUND_ROWS
Hi,
Yes, you would have similar results with any query that uses
SQL_CALC_FOUND_ROWS. That's because MySQL has to see how many rows would
be found without the LIMIT. So in your case, it can't just abort the
query after it finds 10 rows. All rows that match the WHERE need to be
found.
You might want to try your fulltext search IN BOOLEAN MODE to see if
that runs any faster. :-)
Hope that helps.
Matt
- Original Message -
From: [EMAIL PROTECTED]
Sent: Thursday, December 04, 2003 9:13 AM
Subject: fulltext search speed issue with SQL_CALC_FOUND_ROWS
I have some_table with 100,000 rows and with an
average of 500 words in some_column of each row. When
i do a fulltext search on this table using a query
such as the following, all of my results are under 0.1
seconds:
SELECT something
FROM some_table
WHERE MATCH (some_column) AGAINST ('some_search_term')
LIMIT 0,10
However, when i add the SQL_CALC_FOUND_ROWS keyword
like in the following query, some queries take longer
than 1 minute:
SELECT SQL_CALC_FOUND_ROWS something
FROM some_table
WHERE MATCH (some_column) AGAINST ('some_search_term')
LIMIT 0,10
How can there be a huge difference in speed if both
queries always return the exact same results?
Thanks,
TK
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fulltext search speed issue with SQL_CALC_FOUND_ROWS
Hello,
Thanks for the response.
There is one thing that is not clear however.
Regardless of whether or not I perform the fulltext
search with or without the SQL_CALC_FOUND_ROWS
keyword, the results that I get are exactly the same.
Also, the notion of stopping after the limit is
reached cannot apply in the fulltext search or
otherwise we would only get the first 10 matches but
not the first 10 most relevant matches. This leads
me to believe that the fulltext search must be looking
at all the rows in both cases since it otherwise would
not find the same first 10 most relevant records.
Hence the question why there should be a difference in
time.
Just to check, I also performed a search with a limit
that was greater than the number of rows in my table
and the first 10 records were again the same.
Here are the results:
rows: about 100,000
colums: average of 500 words
--- RUN 1 ---
test run with SQL_CALC_FOUND_ROWS (pc was rebooted)
---
SELECT SQL_CALC_FOUND_ROWS id
FROM main
WHERE MATCH (abstract) AGAINST ('access')
LIMIT 0,10
++
| id |
++
| 53957 |
| 21607 |
| 106369 |
| 1916 |
| 50071 |
| 39942 |
| 99764 |
| 99467 |
| 51820 |
| 19956 |
++
10 rows in set (94.16) sec
EXPLAIN SELECT SQL_CALC_FOUND_ROWS id
FROM main
WHERE MATCH (abstract) AGAINST ('access')
LIMIT 0,10
+---+--+---+--+
| table | type | possible_keys | key |
+---+--+---+--+
| main | fulltext | abstract | abstract |
+---+--+---+--+
-++--+-+
key_len | ref| rows | Extra |
-++--+-+
0 ||1 | Using where |
-++--+-+
SELECT SQL_CALC_FOUND_ROWS id
FROM main
WHERE MATCH (abstract) AGAINST ('access')
LIMIT 0,10;
select FOUND_ROWS()
17501 rows
--- RUN 2 ---
test run without SQL_CALC_FOUND_ROWS (pc was rebooted)
---
SELECT id
FROM main
WHERE MATCH (abstract) AGAINST ('access')
LIMIT 0,10
++
| id |
++
| 53957 |
| 21607 |
| 106369 |
| 1916 |
| 50071 |
| 39942 |
| 99764 |
| 99467 |
| 51820 |
| 19956 |
++
10 rows in set (0.11) sec
EXPLAIN SELECT id
FROM main
WHERE MATCH (abstract) AGAINST ('access')
LIMIT 0,10
+---+--+---+--+
| table | type | possible_keys | key |
+---+--+---+--+
| main | fulltext | abstract | abstract |
+---+--+---+--+
-++--+-+
key_len | ref| rows | Extra |
-++--+-+
0 ||1 | Using where |
-++--+-+
--- RUN 3 ---
test run without SQL_CALC_FOUND_ROWS and with high
limit (pc was rebooted)
---
SELECT id
FROM main
WHERE MATCH (abstract) AGAINST ('access')
limit 10
++
| ppt_id |
++
| 53957 |
| 21607 |
| 106369 |
| 1916 |
| 50071 |
| 39942 |
| 99764 |
| 99467 |
| 51820 |
| 19956 |
...
17501 rows in set (94.22) sec
EXPLAIN SELECT id
FROM main
WHERE MATCH (abstract) AGAINST ('access')
limit 10
+---+--+---+--+
| table | type | possible_keys | key |
+---+--+---+--+
| main | fulltext | abstract | abstract |
+---+--+---+--+
-++--+-+
key_len | ref| rows | Extra |
-++--+-+
0 ||1 | Using where |
-++--+-+
So to summarize the question:
To get the most relavent first 10 results, fulltext
seach must be going through all records with or
without the SQL_CALC_FOUND_ROWS keyword, so why would
there be such a huge difference in time.
Thanks,
TK
--- Matt W [EMAIL PROTECTED] wrote:
Hi,
Yes, you would have similar results with any query
that uses
SQL_CALC_FOUND_ROWS. That's because MySQL has to see
how many rows would
be found without the LIMIT. So in your case, it
can't just abort the
query after it finds 10 rows. All rows that match
the WHERE need to be
found.
You might want to try your fulltext search IN
BOOLEAN MODE to see if
that runs any faster. :-)
Hope that helps.
Matt
- Original Message -
From: [EMAIL PROTECTED]
Sent: Thursday, December 04, 2003 9:13 AM
Subject: fulltext search speed issue with
SQL_CALC_FOUND_ROWS
I have some_table with 100,000 rows and with an
average of 500 words in some_column of each row.
When
i do a fulltext search on this table using a query
such as the following, all of my results are under
0.1
seconds:
SELECT something
FROM some_table
WHERE MATCH (some_column) AGAINST
('some_search_term')
LIMIT 0,10
However, when i add the SQL_CALC_FOUND_ROWS
keyword
like in the following query, some queries take
Re: FullText search CJK in UTF-8
At 11:55 -0500 12/2/03, Hu, Yiguang wrote: Is the fulltext search working for cjk text (use UTF-8) in 4.1.0 alpha ? I have trouble doing search on cjk text in 4.1.0alpha, though it seems should be working according to the following doc: http://www.mysql.com/doc/en/Fulltext_Restrictions.html UTF-8 support was added in 4.1.1: http://www.mysql.com/doc/en/News-4.1.1.html Any trick there ? Thanks -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FullText search CJK in UTF-8
Hi! On Dec 02, Hu, Yiguang wrote: Is the fulltext search working for cjk text (use UTF-8) in 4.1.0 alpha ? I have trouble doing search on cjk text in 4.1.0alpha, though it seems should be working according to the following doc: http://www.mysql.com/doc/en/Fulltext_Restrictions.html Nope. The manual says it works with UTF-8 from 4.1.1. You have 4.1.0. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FullText search CJK in UTF-8
Hi! On Dec 02, Sergei Golubchik wrote: Hi! On Dec 02, Hu, Yiguang wrote: Is the fulltext search working for cjk text (use UTF-8) in 4.1.0 alpha ? I have trouble doing search on cjk text in 4.1.0alpha, though it seems should be working according to the following doc: http://www.mysql.com/doc/en/Fulltext_Restrictions.html Nope. The manual says it works with UTF-8 from 4.1.1. You have 4.1.0. Oops, sorry my fault. Looks like this as of 4.1.1 part was added only today :) Thank you for the hint, though. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FULLTEXT search oddity
This doesn't surprise me. I haven't looked at the code, but I'd bet
that double-quoted, exact phrase matches are handled the usual way by
mysql: First, do a regular fulltext search (using the index) to find
rows with your search words, then check the found rows to see if they
exactly contain the double-quoted string. Your result seems to show
that no additional constraints are placed on the second step, or at
least that the beginning of the phrase doesn't have to be a word boundary.
Hence, verandover andover does not match overandover andover because
it doesn't conatain the word verandover. It would have passed the
second step, as it does contain the quoted string.
On the other hand, andover andover does match overandover andover
because the fulltext search is looking for andover, which it finds,
and the exact phrase andover andover can be found in the row.
If I'm right, I'd expect andover and to match, but andover ando
would not.
Whether it should work this way is a philosophical matter, I suppose.
Michael
David Beavan wrote:
I have identified a strange case that seems to give false matches when
performing a FULLTEXT IN BOOLEAN search.
Please consider the following:
---
CREATE TABLE `fttest` (
`id` int(10) unsigned NOT NULL auto_increment,
`text` text,
PRIMARY KEY (`id`),
FULLTEXT KEY `text_index` (`text`)
) TYPE=MyISAM;
INSERT INTO `fttest` (`id`, `text`) VALUES(1, overandover andover);
---
SELECT *
FROM fttest
WHERE MATCH (text) AGAINST ('overandover andover' IN BOOLEAN MODE)
Matches - OK as expected
---
SELECT *
FROM fttest
WHERE MATCH (text) AGAINST ('verandover andover' IN BOOLEAN MODE)
Does not match - OK as expected
---
SELECT *
FROM fttest
WHERE MATCH (text) AGAINST ('andover andover' IN BOOLEAN MODE)
Does match - I would expect this NOT to. Am I missing something or is
this erroneous?
Thanks
Dave
_
Tired of 56k? Get a FREE BT Broadband connection
http://www.msn.co.uk/specials/btbroadband
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Fulltext search from multiple tables...
I have this strange feeling of deja-vu This was just posted to the list recently (last week?), and currently, you cannot do cross-table full-text indexes, excepting boolean text searches, which would be slow. I have this strange feeling of deja-vu Dan Greene -Original Message- From: Kutt Niinepuu [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 09, 2003 2:40 PM To: [EMAIL PROTECTED] Subject: Fulltext search from multiple tables... Hello everybody! Fulltext search fulfills all my needs, only it would be great if someone walked me through using multiple tables with this feature. How to address this MATCH to indexes on different tables? Things like MATCH(table1.column, table2.column) give me errors. Thanx in advance, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FULLTEXT search
At 08:56 PM 9/4/2003, you wrote: Hi all, At the moment, we all know that InnoDB does not yet have FULLTEXT indexes. This is not another message asking as to when we will have that functionality. This message is different. On the mailing list for DBMail, we've been discussing MySQL table types, with everyone highlighting the fact that MyISAM does not support transactions while InnoDB does (in addition to foreign keys). Then, I raised the point that MyISAM may not have transactions, but it does have FULLTEXT indexes, which could be a massive source of speed for MyISAM-based tables. My question is this: Obviously, if you throw SQL statements enclosed in BEGIN/COMMIT and issue ROLLBACK statements on MyISAM tables, MyISAM does the right thing and ignores it (personally, I think it would be better if it alerted the nearest admin, so that they could come and deal with any person touching their finely tuned database server). Is the converse-ish statement true? Is there any way that FULLTEXT searches could be executed on InnoDB (and other type) tables currently or with a quick patch that uses a full table scan? I'd be willing to get together with a few people to write such a thing, as it would speed development while FULLTEXT is a MyISAM exclusive while still allowing testing against all MySQL tables in the immediate future. Regards, Chris Chris, I agree with you, FullText in InnoDb would be nice. It is one reason why I'm sticking with MyISAM tables. I suppose you could modify MySQL to implement FullText on InnoDb tables but then who would support it? It would be a variant of MySQL. It would be better if InnoDb implements it so they would support it in all future MySQL versions. Heikki did mention a year ago that he would consider implementing FullText searching if a client was willing to fund it. Unfortunately he didn't say how much $ it would take. I would be willing to kick in $100 to see it happen. If enough people got together, perhaps we can encourage him to do it. I suspect not having FullText in InnoDb is a major hurdle that is forcing people to stay with MyISAM tables. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FULLTEXT search
Mike, What a brilliant idea! Seriously, setting up a community fund to sponsor this (and perhaps in future other things) might be something to consider. The only questions that come to mind are: 1. Which would be more effective, setting up a fund for this sort of thing or just going out and buying licences for MySQL/InnoDB and/or InnoDB hot backup? 2. What sort of amount would be required to sponsor it? Regards, Chris On Fri, 2003-09-05 at 14:36, mos wrote: At 08:56 PM 9/4/2003, you wrote: Hi all, At the moment, we all know that InnoDB does not yet have FULLTEXT indexes. This is not another message asking as to when we will have that functionality. This message is different. On the mailing list for DBMail, we've been discussing MySQL table types, with everyone highlighting the fact that MyISAM does not support transactions while InnoDB does (in addition to foreign keys). Then, I raised the point that MyISAM may not have transactions, but it does have FULLTEXT indexes, which could be a massive source of speed for MyISAM-based tables. My question is this: Obviously, if you throw SQL statements enclosed in BEGIN/COMMIT and issue ROLLBACK statements on MyISAM tables, MyISAM does the right thing and ignores it (personally, I think it would be better if it alerted the nearest admin, so that they could come and deal with any person touching their finely tuned database server). Is the converse-ish statement true? Is there any way that FULLTEXT searches could be executed on InnoDB (and other type) tables currently or with a quick patch that uses a full table scan? I'd be willing to get together with a few people to write such a thing, as it would speed development while FULLTEXT is a MyISAM exclusive while still allowing testing against all MySQL tables in the immediate future. Regards, Chris Chris, I agree with you, FullText in InnoDb would be nice. It is one reason why I'm sticking with MyISAM tables. I suppose you could modify MySQL to implement FullText on InnoDb tables but then who would support it? It would be a variant of MySQL. It would be better if InnoDb implements it so they would support it in all future MySQL versions. Heikki did mention a year ago that he would consider implementing FullText searching if a client was willing to fund it. Unfortunately he didn't say how much $ it would take. I would be willing to kick in $100 to see it happen. If enough people got together, perhaps we can encourage him to do it. I suspect not having FullText in InnoDb is a major hurdle that is forcing people to stay with MyISAM tables. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FULLTEXT search
At 10:09 PM 9/5/2003, Chris Nolan wrote: Mike, What a brilliant idea! Seriously, setting up a community fund to sponsor this (and perhaps in future other things) might be something to consider. The only questions that come to mind are: 1. Which would be more effective, setting up a fund for this sort of thing or just going out and buying licences for MySQL/InnoDB and/or InnoDB hot backup? I think you're on the right track. MySQL Innodb could add a check box on their order form that says something like Please donate $50 towards the InnoDb FullText search project. (MySQL AB can use any dollar amount that they're comfortable with. I only used $50 as an example) This will help MySQL AB get more licenses and InnoDb could sell more Hot Backups. When the donations reach a specific level, InnoDb starts work on the FullText search feature. 2. What sort of amount would be required to sponsor it? Only Heikki can answer that. It is definitely a feature that is long overdue. If people like me are staying with MyISAM tables because InnoDb lacks FullText support, then it's also impacting InnoDb's Hot Backup sales. So it is in their own best interest to get started on the project. :) Mike On Fri, 2003-09-05 at 14:36, mos wrote: At 08:56 PM 9/4/2003, you wrote: Hi all, At the moment, we all know that InnoDB does not yet have FULLTEXT indexes. This is not another message asking as to when we will have that functionality. This message is different. On the mailing list for DBMail, we've been discussing MySQL table types, with everyone highlighting the fact that MyISAM does not support transactions while InnoDB does (in addition to foreign keys). Then, I raised the point that MyISAM may not have transactions, but it does have FULLTEXT indexes, which could be a massive source of speed for MyISAM-based tables. My question is this: Obviously, if you throw SQL statements enclosed in BEGIN/COMMIT and issue ROLLBACK statements on MyISAM tables, MyISAM does the right thing and ignores it (personally, I think it would be better if it alerted the nearest admin, so that they could come and deal with any person touching their finely tuned database server). Is the converse-ish statement true? Is there any way that FULLTEXT searches could be executed on InnoDB (and other type) tables currently or with a quick patch that uses a full table scan? I'd be willing to get together with a few people to write such a thing, as it would speed development while FULLTEXT is a MyISAM exclusive while still allowing testing against all MySQL tables in the immediate future. Regards, Chris Chris, I agree with you, FullText in InnoDb would be nice. It is one reason why I'm sticking with MyISAM tables. I suppose you could modify MySQL to implement FullText on InnoDb tables but then who would support it? It would be a variant of MySQL. It would be better if InnoDb implements it so they would support it in all future MySQL versions. Heikki did mention a year ago that he would consider implementing FullText searching if a client was willing to fund it. Unfortunately he didn't say how much $ it would take. I would be willing to kick in $100 to see it happen. If enough people got together, perhaps we can encourage him to do it. I suspect not having FullText in InnoDb is a major hurdle that is forcing people to stay with MyISAM tables. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext search not returning matches..
You didn't miss a step, everything is working as it should be. Meaning, MySQL will classify your search results as irrelevant if it matches more than half of the records. If you only have 4 records, chances are your search is matching at least two. Put a few more records in your database and it should start returning results. On Wednesday, July 30, 2003, at 09:25 AM, Chad Day wrote: my query: SELECT * FROM `links` WHERE MATCH ( KEYWORDS ) AGAINST ( 'paper' ); I have a table with 4 records in it of links to various newspapers, paper and newspaper are both in the keywords field. The keywords field has a fulltext index on it. Did I totally miss a step somewhere? The query doesn't error, I just get 0 results back. Thanks, Chad Day -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext search not returning matches..
At 9:25 -0400 7/30/03, Chad Day wrote: my query: SELECT * FROM `links` WHERE MATCH ( KEYWORDS ) AGAINST ( 'paper' ); I have a table with 4 records in it of links to various newspapers, paper and newspaper are both in the keywords field. The keywords field has a fulltext index on it. Did I totally miss a step somewhere? The query doesn't error, I just get 0 results back. Thanks, Chad Day If you have only four records in the table, it's very likey that you're running into the 50% rule. (Words that occur in at least half the records are considered too common and ignored.) You can test this by inserting a few more records that don't contain paper. Or you can use IN BOOLEAN MODE, which ignores the 50% rule. (Available from MySQL 4.0.1 on.) -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FULLTEXT Search and Charsets
At 14:13 -0700 6/12/03, Peter Bryant wrote: Hi. I have a table with a fulltext search index on a field. I am inserting text using UTF-8 characters (with the JDBC driver). That includes words in English, French, Russian and Greek. The FULLTEXT search only seems to find words in the latin charset. Does anyone have any guidance on how I can get it working with other charsets? FULLTEXT currently works only with single-byte character sets. Regards, Peter MySQL SQL -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FULLTEXT Search and Charsets
... The FULLTEXT search only seems to find words in the latin charset. Does anyone have any guidance on how I can get it working with other charsets? FULLTEXT currently works only with single-byte character sets. And UTF-8 makes it easy to handle latin as if it were single-byte. You don't get all of the latin sets, though. -- Joel Rees [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext search -- no wildcards in phrases?
On Wed, 2 Apr 2003, Shamit Verma wrote: The replay on the webpage says that: Nope. That would be a really slow search since mysql cant use any indexes and a table scan would be the only way to find it. Then even LIKE operator should suffer from the same performance drawback, how does LIKE operator work with indexes? exactly. LIKE '%something' will not use indexes but LIKE 'some%' will. Thomas Spahni -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext search -- no wildcards in phrases?
The replay on the webpage says that: Nope. That would be a really slow search since mysql cant use any indexes and a table scan would be the only way to find it. Then even LIKE operator should suffer from the same performance drawback, how does LIKE operator work with indexes? Shamit Verma, http://www.vshamit.com - Original Message - From: Bernhard Döbler [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, April 01, 2003 3:01 PM Subject: Fw: Fulltext search -- no wildcards in phrases? I once got this answer to a similar question on another group. http://www.mail-archive.com/[EMAIL PROTECTED]/msg00280.html Bernhard - Original Message - From: Nick Arnett [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, March 27, 2003 8:59 PM Subject: Fulltext search -- no wildcards in phrases? It appears to me that fulltext phrase searches cannot include wildcards. For example, I would expect app* serv* to match application server, application services, etc. But it returns no results, so I'm having to run each variation separately. Can anyone confirm that wildcards, indeed, can't be used in phrase searches. I'm doing these in Boolean mode because I need exact counts of occurrences. This is on MySQL-4.0.12-nt. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fulltext search
Christian, While fulltext had some problems with latin1 charset and German language it worked quite well for me with latin1_de and a customized German stopword list. BTW I create my stopword list from the texts to be indexed. All words are sorted by frequency and then common words from the top of this list (example: doch weil aber dennoch) are added to the stopword list. ft_min_word_len is set to 3. What exactly are the problems you are seeing? Have a nice day Thomas Spahni On Thu, 27 Mar 2003, Christian Jaeger wrote: At 22:26 Uhr -0600 25.03.2003, mos wrote: How many people out there are willing to pay $$$ to see it done?? Please reply to this thread to see if there is a general interest and how much it is worth to you. IIRC, last time I looked, fulltext was not very good for i.e. the german language. If there would be some hooks (on C level, like UDF's) for adjusting the tokenizer(?), I could probably improve that quite easily myself. (Alternatively, documentation of the relevant code parts would help as well, so I don't spend much time trying to understand it). So if it either is going to be useful for german or provide hooks/documentation for adaptation, I'll pay as well (either myself or by a customer). Christian. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: Fulltext search -- no wildcards in phrases?
On Thursday 27 March 2003 20:59, Nick Arnett wrote: It appears to me that fulltext phrase searches cannot include wildcards. For example, I would expect app* serv* to match application server, application services, etc. But it returns no results, so I'm having to run each variation separately. Can anyone confirm that wildcards, indeed, can't be used in phrase searches. I'm doing these in Boolean mode because I need exact counts of occurrences. This is on MySQL-4.0.12-nt. Yes, phrase search currently doesn't work with operators. It's still in TODO. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fulltext search
At 22:26 Uhr -0600 25.03.2003, mos wrote: How many people out there are willing to pay $$$ to see it done?? Please reply to this thread to see if there is a general interest and how much it is worth to you. IIRC, last time I looked, fulltext was not very good for i.e. the german language. If there would be some hooks (on C level, like UDF's) for adjusting the tokenizer(?), I could probably improve that quite easily myself. (Alternatively, documentation of the relevant code parts would help as well, so I don't spend much time trying to understand it). So if it either is going to be useful for german or provide hooks/documentation for adaptation, I'll pay as well (either myself or by a customer). Christian. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fulltext search
On Mon, Mar 24, 2003 at 03:04:00PM -0800, Brian McCain wrote: Maybe they should set up some way to donate via PayPal. I'm sure there are plenty of developers around the world who'd be willing to chip in $10 here or $20 there in order to be able to use fulltext searching on InnoDB tables. I know I would. Perhaps you should suggest that to the MySQL/InnoDB folks? -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.8: up 50 days, processed 1,721,068,963 queries (396/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fulltext search
On Monday 24 March 2003 04:04 pm, Brian McCain wrote: Maybe they should set up some way to donate via PayPal. I'm sure there are plenty of developers around the world who'd be willing to chip in $10 here or $20 there in order to be able to use fulltext searching on InnoDB tables. I know I would. I actually like that idea a lot myself. I know I always try and get $current_company to get support contracts, but that doesnt' always happen. I think it'd be cool to 'vote' for future enhancements with our money. -- --Jayce^ pgp0.pgp Description: signature
Re: fulltext search
At 09:03 PM 3/25/2003, you wrote: On Monday 24 March 2003 04:04 pm, Brian McCain wrote: Maybe they should set up some way to donate via PayPal. I'm sure there are plenty of developers around the world who'd be willing to chip in $10 here or $20 there in order to be able to use fulltext searching on InnoDB tables. I know I would. I actually like that idea a lot myself. I know I always try and get $current_company to get support contracts, but that doesnt' always happen. I think it'd be cool to 'vote' for future enhancements with our money. -- --Jayce^ Yes, that would be nice. I brought that up with Heikki Tuuri a while ago and he says if he can find a client with the $$$ then it can be done. I'd pay $100 for full text search with InnoDb if it were built into the standard version (no royalties). How many people out there are willing to pay $$$ to see it done?? Please reply to this thread to see if there is a general interest and how much it is worth to you. Mike P.S. I don't know how much it would cost, but I don't think we'll get there with $20 donations unless we get a thousand people joining in. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: fulltext search
You don't. http://www.mysql.com/doc/en/Fulltext_Search.html There was a discussion on this mailing list last week or the week before on when innoDB support would be implemented. I'm too lazy to look it up, you should search the list archives for it. -Original Message- From: Sidar Lopez Cruz [mailto:[EMAIL PROTECTED] Sent: Monday, March 24, 2003 1:01 PM To: MySQL Subject: fulltext search how can i implements a fulltext search engine on InnoDB tables? i need it... :-) Sidar Lopez Cruz - Cero Riesgo, S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fulltext search
On Mon, Mar 24, 2003 at 12:59:17PM -0800, Jennifer Goodie wrote: You don't. http://www.mysql.com/doc/en/Fulltext_Search.html There was a discussion on this mailing list last week or the week before on when innoDB support would be implemented. I'm too lazy to look it up, you should search the list archives for it. The summary: It'll happen someday unless a paying sponsor steps upto the plate sooner. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.8: up 49 days, processed 1,689,767,714 queries (395/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fulltext search
Maybe they should set up some way to donate via PayPal. I'm sure there are plenty of developers around the world who'd be willing to chip in $10 here or $20 there in order to be able to use fulltext searching on InnoDB tables. I know I would. -Brian McCain - Original Message - From: Jeremy Zawodny [EMAIL PROTECTED] To: Jennifer Goodie [EMAIL PROTECTED] Cc: Sidar Lopez Cruz [EMAIL PROTECTED]; MySQL [EMAIL PROTECTED] Sent: Monday, March 24, 2003 2:44 PM Subject: Re: fulltext search On Mon, Mar 24, 2003 at 12:59:17PM -0800, Jennifer Goodie wrote: You don't. http://www.mysql.com/doc/en/Fulltext_Search.html There was a discussion on this mailing list last week or the week before on when innoDB support would be implemented. I'm too lazy to look it up, you should search the list archives for it. The summary: It'll happen someday unless a paying sponsor steps upto the plate sooner. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.8: up 49 days, processed 1,689,767,714 queries (395/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fulltext search
Wynne, - Original Message - From: Wynne Crisman [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: 'Heikki Tuuri' [EMAIL PROTECTED] Sent: Friday, March 21, 2003 5:18 AM Subject: Re: fulltext search Since InnoDB tables don't support fulltext searching yet, what is the recommended way to work around the problem? use MyISAM tables or some tool which builds separate fulltext index tables from a TEXT column. Thank you, Wynne Crisman patternWare Systems Inc. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB sql query -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 19, 2003 11:10 AM To: [EMAIL PROTECTED] Subject: Re: fulltext search Sidar, when MySQL will support fulltext search on InnoDB tables? we are still waiting for some customer to sponsor the porting. Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, hot backup, and foreign key support for MySQL See http://www.innodb.com, download MySQL-4.0 from http://www.mysql.com sql query - 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 search
Since InnoDB tables don't support fulltext searching yet, what is the recommended way to work around the problem? Thank you, Wynne Crisman patternWare Systems Inc. -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 19, 2003 11:10 AM To: [EMAIL PROTECTED] Subject: Re: fulltext search Sidar, when MySQL will support fulltext search on InnoDB tables? we are still waiting for some customer to sponsor the porting. Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, hot backup, and foreign key support for MySQL See http://www.innodb.com, download MySQL-4.0 from http://www.mysql.com sql query - 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 search
Sidar, - Original Message - From: Sidar Lopez Cruz [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Tuesday, March 18, 2003 10:50 PM Subject: fulltext search when MySQL will support fulltext search on InnoDB tables? we are still waiting for some customer to sponsor the porting. Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, hot backup, and foreign key support for MySQL See http://www.innodb.com, download MySQL-4.0 from http://www.mysql.com sql query - 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 Search Problem
No, that should work. Do any items in the db have the text 'madrid' in them?
Does it fail with an error or does it simply not return any results? Are you
executing it on the command line? If not, are you checking for errors? What
version of MySQL do you have?
Brian McCain
- Original Message -
From: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, March 18, 2003 8:33 AM
Subject: Fulltext Search Problem
Hi,
Why does this not work?
SELECT * FROM News WHERE category='sport' AND MATCH
(subcategory,headline,summary) AGAINST ('madrid') LIMIT 1,25
If i remove the category='sport' AND from the WHERE clause it works -
yet all
documents in the db currently have category='sport'. Is it not possible to
mix
a MATCH with another condition?
Any ideas, or is the only solution to run the first query, store the
results in
a temp table, and then run a 2nd query.
Regards
Martin Curmi
-
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: Fulltext Search Problem
Hi Brian,
Sorry, I was mistaken. It does work! The documents in question, were not
category='sport'.
Regards
Martin
Brian McCain [EMAIL PROTECTED]
18/03/2003 17:23
To: [EMAIL PROTECTED], [EMAIL PROTECTED]
cc:
Subject:Re: Fulltext Search Problem
No, that should work. Do any items in the db have the text 'madrid' in
them?
Does it fail with an error or does it simply not return any results? Are
you
executing it on the command line? If not, are you checking for errors?
What
version of MySQL do you have?
Brian McCain
- Original Message -
From: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, March 18, 2003 8:33 AM
Subject: Fulltext Search Problem
Hi,
Why does this not work?
SELECT * FROM News WHERE category='sport' AND MATCH
(subcategory,headline,summary) AGAINST ('madrid') LIMIT 1,25
If i remove the category='sport' AND from the WHERE clause it works -
yet all
documents in the db currently have category='sport'. Is it not possible
to
mix
a MATCH with another condition?
Any ideas, or is the only solution to run the first query, store the
results in
a temp table, and then run a 2nd query.
Regards
Martin Curmi
-
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: Fulltext search: Is there a way to disable the stop words?
Hi! On Dec 28, John Porter Simons wrote: Any system variables I could set, or any parameters I could add to MATCH ... AGAINST query syntax, or any plans to disable stopwords for boolean fulltext searches in future builds? The answer to the last question is yes, definitely, to others - sorry, but no. 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 search of words 3 chars in 3.23
On Sunday 29 December 2002 04:55, Frank Peavy wrote: Does anyone have a method of performing fulltext searches on words less than 3 characters on MySql 3.23? I am dealing with a web hosting company so a re-compile is out of the question. Anyone have any good suggestions? I need to perform searches on acronyms like php. You can change value of the ft_min_word_len (look at the ft_static.c) and recompile MySQL server. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - 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 search of words 3 chars in 3.23
Any one else with any suggestions? Remember, re-compile is out of the
question, it's a hosted site.
Thanks.
At 10:49 PM 12/28/02 -0700, Mike Hillyer wrote:
He wants to execute a FULLTEXT search as opposed to a simple LIKE statement,
so I think REGEXP is out of the question.
Mike Hillyer
-Original Message-
From: JamesD [mailto:[EMAIL PROTECTED]]
Sent: Saturday, December 28, 2002 10:48 PM
To: Frank Peavy; [EMAIL PROTECTED]
Subject: RE: Fulltext search of words 3 chars in 3.23
You need to use the REGEXP capability instead of Like in a where clause
select 'field(s)' from 'table' where 'field' REGEXP '^[a-z]{1,3}$';
Jim
-Original Message-
From: Frank Peavy [mailto:[EMAIL PROTECTED]]
Sent: Saturday, December 28, 2002 6:55 PM
To: [EMAIL PROTECTED]
Subject: Fulltext search of words 3 chars in 3.23
Does anyone have a method of performing fulltext searches on words less
than 3 characters on MySql 3.23? I am dealing with a web hosting company so
a re-compile is out of the question.
Anyone have any good suggestions? I need to perform searches on acronyms
like php.
Thanks.
-
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: Fulltext search of words 3 chars in 3.23
At 21:17 -0700 12/28/02, Mike Hillyer wrote: I would think that using the fulltext search IN BOOLEAN MODE would return results of any length, even 3 characters or less, check the bottom of http://www.mysql.com/doc/en/Fulltext_Search.html for examples on using boolean mode. Nope. IN BOOLEAN MODE wasn't implemented until 4.x. Mike Hillyer -Original Message- From: Frank Peavy [mailto:[EMAIL PROTECTED]] Sent: Saturday, December 28, 2002 7:55 PM To: [EMAIL PROTECTED] Subject: Fulltext search of words 3 chars in 3.23 Does anyone have a method of performing fulltext searches on words less than 3 characters on MySql 3.23? I am dealing with a web hosting company so a re-compile is out of the question. Anyone have any good suggestions? I need to perform searches on acronyms like php. Thanks. - 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
