Full text search not matching 2 letter word
Im trying to run a full text query on a two letter keyword 'K7'. I have
set ft_min_word_len=2 and restarted the server and if I view the system
vars in Mysql Workbench it shows it is set correctly.
I have then dropped and re-created the index on the descrip column. It
is an InnoDB table so I cannot do repair table.
Im running the following query which I expect to match the following
record but it doesnt. Full text searches for other words match OK.
select * from asset where type ='DOCUMENTS' and (match(descrip) against
('K7' in boolean mode)) ;
+-++--+---+---+--+-+
| id | type | descrip | subtype |
intendeduse | location | assetfileid |
+-++-++---+--+-+
| 153 | DOCUMENTS | Telephone Kiosk No. 7 K7 Interior promo photo from
field trial. | PHOTO | DISPLAY | STORAGE
| 152 |
+-++--++--+--+-+
Any ideas why this is not working?
Thanks
Andrew
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
RE: Full text search and sign as a part of the keyword
ft_min_word_len=3
stopwords! IDIOT! I was testing using stop words! :)
let me change my test org name.
thanks
FULLTEXT (at least the MyISAM version) has 3 gotchas:
ft_min_word_len=4, stopwords, and the 50% rule
-Original Message-
From: shawn green [mailto:shawn.l.gr...@oracle.com]
Sent: Tuesday, July 02, 2013 10:21 AM
To: mysql@lists.mysql.com
Subject: Re: Full text search and sign as a part of the keyword
Hello,
(my response is not top-posted)
On 7/2/2013 12:50 PM, l...@afan.net wrote:
Another correction: Searching for Com, the test org is NOT gonna be
listed but all others will.
Searching for Com no results at all.
Actually, looks like I'm wrong.
For testing purpose I made an org
CompMe
When search for Comp it's gonna
be shown on the list.
When search for Comp it's also gonna be shown.
But
Construction Company as well.
Then I changed the name of
the test org to ComMe.
Searching for Com, the test org is gonna be listed.
Though, Com no results at
all.
?!?
Hi to all,
I have this full text search query
SELECT name, org_id,
address_id
FROM organization
WHERE org_active='Y' AND MATCH(name) AGAINST('ABC*' IN
BOOLEAN
MODE)
and I'm not getting any results. And there IS a org ABC, Inc.
My assumption is the ampersand sign as a part of the keyword.
Any idea?
Read this:
http://dev.mysql.com/doc/refman/5.5/en/server-system-
variables.html#sysvar_ft_boolean_syntax
Then search on Comp Me.
Let us know your results.
--
Shawn Green
MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware
and
Software, Engineered to Work Together.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Full text search and sign as a part of the keyword
Hi to all,
I have this full text search query
SELECT name, org_id,
address_id
FROM organization
WHERE org_active='Y' AND MATCH(name) AGAINST('ABC*' IN BOOLEAN
MODE)
and I'm not getting any results. And there IS a org ABC,
Inc.
My assumption is the ampersand sign as a part of the
keyword.
Any idea?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: Full text search and sign as a part of the keyword
Hello,
(my response is not top-posted)
On 7/2/2013 12:50 PM, l...@afan.net wrote:
Another correction: Searching for Com, the test org is NOT
gonna be listed but all others will.
Searching for Com no results at all.
�
�
Actually, looks like I'm wrong.
For testing purpose I made an org
CompMe
When search for Comp it's gonna
be shown on the list.
When search for Comp it's also gonna be shown.
But
Construction Company as well.
Then I changed the name of
the test org to ComMe.
Searching for Com, the test org is gonna be listed.
Though, Com no results at
all.
?!?
�
Hi to all,
I have this full text search query
SELECT name, org_id,
address_id
FROM organization
WHERE org_active='Y' AND MATCH(name) AGAINST('ABC*' IN
BOOLEAN
MODE)
and I'm not getting any results. And there IS a org ABC,
Inc.
My assumption is the ampersand sign as a part of the
keyword.
Any idea?
Read this:
http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_ft_boolean_syntax
Then search on Comp Me.
Let us know your results.
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: Full text search and sign as a part of the keyword
Another correction: Searching for Com, the test org is NOT
gonna be listed but all others will.
Searching for Com no results at all.
�
�
Actually, looks like I'm wrong.
For testing purpose I made an org
CompMe
When search for Comp it's gonna
be shown on the list.
When search for Comp it's also gonna be shown.
But
Construction Company as well.
Then I changed the name of
the test org to ComMe.
Searching for Com, the test org is gonna be listed.
Though, Com no results at
all.
?!?
�
Hi to all,
I have this full text search query
SELECT name, org_id,
address_id
FROM organization
WHERE org_active='Y' AND MATCH(name) AGAINST('ABC*' IN
BOOLEAN
MODE)
and I'm not getting any results. And there IS a org ABC,
Inc.
My assumption is the ampersand sign as a part of the
keyword.
Any idea?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
RE: Full text search and sign as a part of the keyword
FULLTEXT (at least the MyISAM version) has 3 gotchas:
ft_min_word_len=4, stopwords, and the 50% rule
-Original Message-
From: shawn green [mailto:shawn.l.gr...@oracle.com]
Sent: Tuesday, July 02, 2013 10:21 AM
To: mysql@lists.mysql.com
Subject: Re: Full text search and sign as a part of the keyword
Hello,
(my response is not top-posted)
On 7/2/2013 12:50 PM, l...@afan.net wrote:
Another correction: Searching for Com, the test org is NOT gonna be
listed but all others will.
Searching for Com no results at all.
Actually, looks like I'm wrong.
For testing purpose I made an org
CompMe
When search for Comp it's gonna
be shown on the list.
When search for Comp it's also gonna be shown.
But
Construction Company as well.
Then I changed the name of
the test org to ComMe.
Searching for Com, the test org is gonna be listed.
Though, Com no results at
all.
?!?
Hi to all,
I have this full text search query
SELECT name, org_id,
address_id
FROM organization
WHERE org_active='Y' AND MATCH(name) AGAINST('ABC*' IN
BOOLEAN
MODE)
and I'm not getting any results. And there IS a org ABC, Inc.
My assumption is the ampersand sign as a part of the keyword.
Any idea?
Read this:
http://dev.mysql.com/doc/refman/5.5/en/server-system-
variables.html#sysvar_ft_boolean_syntax
Then search on Comp Me.
Let us know your results.
--
Shawn Green
MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and
Software, Engineered to Work Together.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: Full text search and sign as a part of the keyword
Actually, looks like I'm wrong.
For testing purpose I made an org
CompMe
When search for Comp it's gonna
be shown on the list.
When search for Comp it's also gonna be shown. But
Construction Company as well.
Then I changed the name of
the test org to ComMe.
Searching for Com, the test org is gonna be listed.
Though, Com no results at
all.
?!?
�
Hi to all,
I have this full text search query
SELECT name, org_id,
address_id
FROM organization
WHERE org_active='Y' AND MATCH(name) AGAINST('ABC*' IN
BOOLEAN
MODE)
and I'm not getting any results. And there IS a org ABC,
Inc.
My assumption is the ampersand sign as a part of the
keyword.
Any idea?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
MySQL University session on December 3: Practical Full-Text Search in MySQL
Practical Full-Text Search in MySQL http://forge.mysql.com/wiki/Practical_Full-Text_Search_in_MySQL This Thursday (December 3rd, 16:00 UTC – note the different time), Bill Karwin will talk about Practical Full-Text Search in MySQL. He'll introduce and compare five different approaches of full-text search with MySQL, using built-in functionality as well as third-party tools. It's interesting to see how vastly performance can vary, depending on which tools you use for which purposes. For MySQL University sessions, point your browser to this page: http://webmeeting.dimdim.com/portal/JoinForm.action?confKey=mysqluniversity You need a browser with a working Flash plugin. You may register for a Dimdim account, but you don't have to. (Dimdim is the conferencing system we're using for MySQL University sessions. It provides integrated voice streaming, chat, whiteboard, session recording, and more.) MySQL University is a free educational online program for engineers/developers. MySQL University sessions are open to anyone, not just Sun employees. Sessions are recorded (slides and audio), so if you can't attend the live session you can look at the recording anytime after the session. This session concludes the MySQL University program for this year. We'll start again on January 28th, 2010. Merry Christmas / a great winter break, and a Happy New Year to everyone! Cheers, Stefan -- *** Sun Microsystems GmbHStefan Hinz Sonnenallee 1Manager Documentation, Database Group 85551 Kirchheim-Heimstetten Phone: +49-30-82702940 Germany Fax: +49-30-82702941 http://www.sun.de/mysql mailto: stefan.h...@sun.com Amtsgericht Muenchen: HRB161028 Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer Vorsitzender des Aufsichtsrates: Martin Haering *** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Full Text Search Problem
Hi,
I've a table, 'article' which has a cloumn 'agency'with FULLTEXT (agency).
'agency' has six (6) rows of data: 'NATIONAL OCEANIC AND ATMOSPHERIC
ADMINISTRATION (NOAA), NATIONAL OCEAN SERVICE (NOS), DEPARTMENT OF COMMERCE'.
When I did a search like the following;
SELECT COUNT(*) FROM article WHERE MATCH (agency) AGAINST ('DEPARTMENT OF
COMMERCE');
It returned count(*) = 0 instead of count(*) = 6.
Could you kindly advise me what did I do wrong?
Thanks,
Jack
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Full Text Search Problem
Hi,
I've a table, 'article' which has a cloumn 'agency'with FULLTEXT (agency).
'agency' has six (6) rows of data: 'NATIONAL OCEANIC AND ATMOSPHERIC
ADMINISTRATION (NOAA), NATIONAL OCEAN SERVICE (NOS), DEPARTMENT OF COMMERCE'.
When I did a search like the following;
SELECT COUNT(*) FROM article WHERE MATCH (agency) AGAINST ('DEPARTMENT OF
COMMERCE');
It returned count(*) = 0 instead of count(*) = 6.
Could you kindly advise me what did I do wrong?
Thanks,
Jack
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Full Text Search Problem
Hi,
I've a table, 'article' which has a cloumn 'agency'with FULLTEXT (agency).
'agency' has six (6) rows of data: 'NATIONAL OCEANIC AND ATMOSPHERIC
ADMINISTRATION (NOAA), NATIONAL OCEAN SERVICE (NOS), DEPARTMENT OF COMMERCE'.
When I did a search like the following;
SELECT COUNT(*) FROM article WHERE MATCH (agency) AGAINST ('DEPARTMENT OF
COMMERCE');
It returned count(*) = 0 instead of count(*) = 6.
Could you kindly advise me what did I do wrong?
Thanks,
Jack
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Full Text Search Problem
'Words' shows in more than 50% rows will be regards as a stop words.
2009/4/24 mysupp...@asuma.com
Hi,
I've a table, 'article' which has a cloumn 'agency'with FULLTEXT (agency).
'agency' has six (6) rows of data: 'NATIONAL OCEANIC AND ATMOSPHERIC
ADMINISTRATION (NOAA), NATIONAL OCEAN SERVICE (NOS), DEPARTMENT OF
COMMERCE'.
When I did a search like the following;
SELECT COUNT(*) FROM article WHERE MATCH (agency) AGAINST ('DEPARTMENT OF
COMMERCE');
It returned count(*) = 0 instead of count(*) = 6.
Could you kindly advise me what did I do wrong?
Thanks,
Jack
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=mysql.li...@gmail.com
Re: Full Text Search Problem
Sorry, I don't understand your answer. Could you kindly explain in more details?
Thanks,
Jack
--- On Fri, 4/24/09, zhu dingze mysql.li...@gmail.com wrote:
From: zhu dingze mysql.li...@gmail.com
Subject: Re: Full Text Search Problem
To: mysupp...@asuma.com
Cc: mysql@lists.mysql.com
Date: Friday, April 24, 2009, 5:57 PM
'Words' shows in more than 50% rows will be regards as a stop words.
2009/4/24 mysupp...@asuma.com
Hi,
I've a table, 'article' which has a cloumn 'agency'with FULLTEXT
(agency).
'agency' has six (6) rows of data: 'NATIONAL OCEANIC AND ATMOSPHERIC
ADMINISTRATION (NOAA), NATIONAL OCEAN SERVICE (NOS), DEPARTMENT OF COMMERCE'.
When I did a search like the following;
SELECT COUNT(*) FROM article WHERE MATCH (agency) AGAINST ('DEPARTMENT
OF COMMERCE');
It returned count(*) = 0 instead of count(*) = 6.
Could you kindly advise me what did I do wrong?
Thanks,
Jack
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=mysql.li...@gmail.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
full text search ft_min_word_len
Hi, When I use ft_min_word_len=3 under [mysqld] section, mysql fails to start. ft_min_word_len=3 parameter has been added under [myisamchk] only , which works. mysql Ver 14.12 Distrib 5.0.67, for redhat-linux-gnu (i686) using readline 5.1 Anyone know about this?
Full-text search and queries sensitive to accents
Hi all: I read in mysql documentation that searches are case-insensitive by default but this default behaviour can be changed using a latin1_bin collation. But, is there any way to make searches sensitive or unsensitive to accents, umlauts, etc.? I suppose that queries are accent sensitive by default. Is there any way to change this in mysql 5.1? Thanks in advance, Mario Barcala -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Full text search and highlight results
MySQL has no idea how you are presenting the data (html, rtf, etc.), so it couldn't hilight the words for you. It should really be that tricky using grep and PHP. Brent I have my data as pure text: no html, rtf or something else. That is, one table with two columns: one for the document id and another for the document text. I include, for example, the clear text of newpaper news. Several databases include a function or something to do this in some way: Oracle, Postgres, etc. It's a surprise for me that mysql has nothing about it. Thanks, Mario Barcala -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Full text search and highlight results
Hi all: I was reading documentation and searching into mail archives but I didn't find a solution to the following question: Is there any way to highligh results from a full-text search? I know some tricky methods using PHP but I want to know if mysql (5.0 or 5.1 versions) offers some methos or function to do this. I want to write the keywords in context (KWIC) boldface and some previous an following words around them normalface. Thanks in advance, Mario Barcala -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Full text search and highlight results
MySQL has no idea how you are presenting the data (html, rtf, etc.), so it couldn't hilight the words for you. It should really be that tricky using grep and PHP. Brent On Sep 8, 2008, at 10:58 AM, Fco. Mario Barcala Rodríguez wrote: Hi all: I was reading documentation and searching into mail archives but I didn't find a solution to the following question: Is there any way to highligh results from a full-text search? I know some tricky methods using PHP but I want to know if mysql (5.0 or 5.1 versions) offers some methos or function to do this. I want to write the keywords in context (KWIC) boldface and some previous an following words around them normalface. Thanks in advance, Mario Barcala -- 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]
[RFH] Full Text search
Dear Users,
I am facing a problem related to full text search. I am trying to
search non latin characters with no success :(.
I am trying the following queries for searching and only the English one works.
SELECT * FROM bangla_test WHERE MATCH(bn_test) AGAINST('নাম নাই' in
boolean mode)
SELECT * FROM bangla_test WHERE MATCH(bn_test) AGAINST('test' in boolean mode)
SELECT * FROM bangla_test WHERE MATCH(bn_test) AGAINST('日系人' in boolean mode)
The dump of the database is following this paragraph. I would be
grateful if someone could be point me what I am doing wrong.
-- MySQL dump 10.11
--
-- Host: localhostDatabase: test
-- --
-- Server version 5.0.38-Ubuntu_0ubuntu1.2-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS,
FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Current Database: `honeybase`
--
/*!4 DROP DATABASE IF EXISTS `test`*/;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT
CHARACTER SET latin1 */;
USE `honeybase`;
--
-- Table structure for table `bangla_test`
--
DROP TABLE IF EXISTS `bangla_test`;
CREATE TABLE `bangla_test` (
`id` int(11) NOT NULL auto_increment,
`bn_test` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
FULLTEXT KEY `bn_full_text` (`bn_test`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
--
-- Dumping data for table `bangla_test`
--
LOCK TABLES `bangla_test` WRITE;
/*!4 ALTER TABLE `bangla_test` DISABLE KEYS */;
set autocommit=0;
INSERT INTO `bangla_test` VALUES (1,'নাম
নাই'),(2,'test'),(3,'日系人'),(4,'another test'),(5,'dingdong');
/*!4 ALTER TABLE `bangla_test` ENABLE KEYS */;
UNLOCK TABLES;
commit;
/*!40103 SET [EMAIL PROTECTED] */;
/*!40101 SET [EMAIL PROTECTED] */;
/*!40014 SET [EMAIL PROTECTED] */;
/*!40014 SET [EMAIL PROTECTED] */;
/*!40101 SET [EMAIL PROTECTED] */;
/*!40101 SET [EMAIL PROTECTED] */;
/*!40101 SET [EMAIL PROTECTED] */;
/*!40111 SET [EMAIL PROTECTED] */;
-- Dump completed on 2008-02-27 5:41:23
Thank you,
--
Imran M Yousuf
Entrepreneur Software Engineer
Smart IT Engineering
Dhaka, Bangladesh
Email: [EMAIL PROTECTED]
Mobile: +880-1711402557
Full Text Search
hi all, i just want to know whether it is useful to alter the 50% threshold scheme that mysql uses by default ,is it beneficial in any way regarding serach(either by increasing or decreasing its value)or is it good to use default value of 50% only. which is better (altering the file to change 50% value or using default 50 %) thanx and regards -- View this message in context: http://www.nabble.com/Full-Text-Search-tp14947073p14947073.html Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fast relevance sorting of full text search results
Urms schrieb:
I'm using pretty standard approach to sorting search results by relevancy:
SELECT DISTINCT product_name,
MATCH (keywords) AGAINST ('CONSOLIDATED* 16* bearing*' IN BOOLEAN MODE) AS
rate
FROM _TT
WHERE MATCH ( keywords ) AGAINST ('CONSOLIDATED* 16* bearing*' IN BOOLEAN
MODE ) 0
ORDER BY rate DESC
It works fine as long as the quantity of results is not big. Once the
quantity is about 50,000 and more (I have a very big database) the query
starts working way too slow. Total number of records is about 4 million. It
takes about 2 sec when there are 50,000 records in the result but at the
same time it takes only about 0.006 sec without ORDER BY clause.
you should reformat your query or table structure
for a quick solution:
probably with 50.000 records it exceeds your myisam_sort_buffer_size or
sort_buffer_size, try to raise them
--
Sebastian
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
full text search on multiple tables
Hello list I have to make a full text search and I want to do it in many tables. I have deferent tables for books authors and news. Any ideas how to do it? Thank you Nikos
Re: full text search on multiple tables
nikos schrieb: Hello list I have to make a full text search and I want to do it in many tables. I have deferent tables for books authors and news. Any ideas how to do it? three separate queries or an UNION -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: full text search on multiple tables
On Jan 9, 2008 8:36 AM, Sebastian Mendel [EMAIL PROTECTED] wrote: nikos schrieb: Hello list I have to make a full text search and I want to do it in many tables. I have deferent tables for books authors and news. Any ideas how to do it? three separate queries or an UNION Or a Boolean mode search. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: full text search on multiple tables
That is a grate solution. The problem is that I must have deferent links for each response. That's the tricky thing! Thank you Sebastian Mendel wrote: nikos schrieb: Hello list I have to make a full text search and I want to do it in many tables. I have deferent tables for books authors and news. Any ideas how to do it? three separate queries or an UNION
R: full text search on multiple tables
As Sebastian Mendel wrote: you can use a union, you can mask the fact you are dealing with fields coming from three different tables renaming the fields of interest (the fields on which you make the search) with the same name. Something like this should works, it does with me: SELECT I'M A BOOK,ID_BOOK as ID_TO_RETURN FROM Books T1 WHERE TITLE like '%..whatever..%' UNION SELECT I'M AN AUTHOR,ID_AUTHOR as ID_TO_RETURN FROM Authors T1 WHERE FIRST_NAME like '%..whatever..' OR LAST_NAME like '%..whatever..' UNION SELECT I'M A NEWS,ID_NEWS as ID_TO_RETURN FROM News T1 WHERE TITLE like '%..whatever..' OR CONTENT like '%..whatever..' Aloha! Claudio Nanni -Messaggio originale- Da: nikos [mailto:[EMAIL PROTECTED] Inviato: mercoledì 9 gennaio 2008 14.21 A: mysql@lists.mysql.com Oggetto: full text search on multiple tables Hello list I have to make a full text search and I want to do it in many tables. I have deferent tables for books authors and news. Any ideas how to do it? Thank you Nikos Questo messaggio ed ogni suo allegato sono confidenziali e possono essere riservati o, comunque, protetti dall'essere diffusi. Se il ricevente non é il destinatario diretto del presente messaggio, é pregato di contattare l'originario mittente e di cancellare questo messaggio ed ogni suo allegato dal sistema di posta. Se il ricevente non é il destinatario diretto del presente messaggio, sono vietati l'uso, la riproduzione e la stampa di questo messaggio e di ogni suo allegato, nonché la diffusione del loro contenuto a qualsiasi altro soggetto * This message and any attachment are confidential and may be privileged or otherwise protected from disclosure. If you are not the intended recipient, please contact the sender and delete this message and any attachment from your system. If you are not the intended recipient you must not use, copy or print this message or attachment or disclose the contents to any other person. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fast relevance sorting of full text search results
Urms wrote:
I'm using pretty standard approach to sorting search results by relevancy:
SELECT DISTINCT product_name,
MATCH (keywords) AGAINST ('CONSOLIDATED* 16* bearing*' IN BOOLEAN MODE) AS
rate
FROM _TT
WHERE MATCH ( keywords ) AGAINST ('CONSOLIDATED* 16* bearing*' IN BOOLEAN
MODE ) 0
ORDER BY rate DESC
It works fine as long as the quantity of results is not big. Once the
quantity is about 50,000 and more (I have a very big database) the query
starts working way too slow. Total number of records is about 4 million. It
takes about 2 sec when there are 50,000 records in the result but at the
same time it takes only about 0.006 sec without ORDER BY clause.
I understand that ORDER BY is time consuming but maybe someone knows a
different way to have sorting by relevancy.
Thanks in advance!
I think it's your SELECT DISTINCT that is slowing you down. For each new
row being considered for inclusion to your result set, you are asking
the engine to compare that row against all other rows you already have
in the set. So what's happening is that you are doing a longer and
longer linear search the larger your datasets become.
One option is to cache your results in a temporary table then
de-duplicate your results from there.
Another option is to create a temporary table with a UNIQUE key on the
columns you want to remain unique and use an INSERT IGNORE. Because of
the UNIQUE key (or PRIMARY KEY if that's your choice) you will be doing
an indexed search of all values rather than a linear search through the
entire list. This would look something like:
CREATE TEMPORARY TABLE tmpFT_results (
product_name insert datatype here
, rate insert datatype here
, primary key (product_name)
) ENGINE = MEMORY;
INSERT IGNORE tmpFT_results
SELECT product_name,
MATCH (keywords) AGAINST ('CONSOLIDATED* 16* bearing*' IN BOOLEAN MODE) AS
rate
FROM _TT
WHERE MATCH ( keywords ) AGAINST ('CONSOLIDATED* 16* bearing*' IN BOOLEAN
MODE ) 0;
SELECT product_name, rate
FROM tmpFT_results
ORDER BY rate DESC;
Sure it's three separate steps but it's tuned to the process you are
trying to perform. The SELECT DISTINCT processing has no idea that you
only need to keep the values of product_name distinct as we would hope
the `rate` component may be duplicated.
If there is the possibility of different `rate` results for the same
product_name value then you may also want to use the temporary table
method to somehow weight (sum or average comes to mind) the match values
across all responses before returning the results.
I hope these ideas help your performance and search accuracy.
Best wishes,
--
Shawn Green, Support Engineer
MySQL Inc., USA, www.mysql.com
Office: Blountville, TN
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /
/ /|_/ / // /\ \/ /_/ / /__
/_/ /_/\_, /___/\___\_\___/
___/
Join the Quality Contribution Program Today!
http://dev.mysql.com/qualitycontribution.html
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fast relevance sorting of full text search results
I'm using pretty standard approach to sorting search results by relevancy:
SELECT DISTINCT product_name,
MATCH (keywords) AGAINST ('CONSOLIDATED* 16* bearing*' IN BOOLEAN MODE) AS
rate
FROM _TT
WHERE MATCH ( keywords ) AGAINST ('CONSOLIDATED* 16* bearing*' IN BOOLEAN
MODE ) 0
ORDER BY rate DESC
It works fine as long as the quantity of results is not big. Once the
quantity is about 50,000 and more (I have a very big database) the query
starts working way too slow. Total number of records is about 4 million. It
takes about 2 sec when there are 50,000 records in the result but at the
same time it takes only about 0.006 sec without ORDER BY clause.
I understand that ORDER BY is time consuming but maybe someone knows a
different way to have sorting by relevancy.
Thanks in advance!
--
View this message in context:
http://www.nabble.com/Fast-relevance-sorting-of-full-text-search-results-tp14571054p14571054.html
Sent from the MySQL - General mailing list archive at Nabble.com.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to encrypt Text and still be able to use full text search? 3rd Attempt ++
mos schrieb: I posted this message twice in the past 3 days, and it never gets on the mailing list. Why? Here it is again: I have a Text field that contains paragraph text and for security reasons I need to have it encrypted. If I do this, how can I still implement full text search on it? Also, I have a lot of Float columns that need to be protected but the user has to use comparison operators like and on them. Any recommendations? Mike, What size ist the database? Could you create some kind of (temporary) table holding the data unencrypted? As fulltext search is only possible on myisam tables, you might want to put this on a ramdisk and create it during mysql startup (just make a symlink like /var/lib/mysql/UnencryptedDatabase - /ramdisk/ and use the |--init-file-Paramter for mysqld to create and fill the table). It would at least make it more difficult to get the data for somebody who has physical access to the machine as long as you have all your partitions encrypted as well have to enter your password during startup. ||I know there is still danger: somebody at the ISP could shut down the server and modify your initrd and try to get you password when you enter it during startup, but as long as you won't host the machine yourself, there probably is no better option. Get rackspace that has doors and can be locked... a little more security, but usually the ISP has a second key in their safe :( ||Or you might set it up so you have to enter 2 Passwords, the first one to decrypt and start a small program that checksums the kernel and initrd that is in memory, and then a second one to mount the partitions...| | If the value of the data is really a million, host it on your own and install security systems etc. and a 24/7 NOC keeping an eye on your server looking for hackers and so on. If your budget is only $100/month I would do the way I described above. ||| |Jan| || -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to encrypt Text and still be able to use full text search? 3rd Attempt ++
I posted this message twice in the past 3 days, and it never gets on the mailing list. Why? Here it is again: I have a Text field that contains paragraph text and for security reasons I need to have it encrypted. If I do this, how can I still implement full text search on it? Also, I have a lot of Float columns that need to be protected but the user has to use comparison operators like and on them. Any recommendations? TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to encrypt Text and still be able to use full text search? 3rd Attempt ++
On 26 Oct 2007 at 9:17, mos wrote: I posted this message twice in the past 3 days, and it never gets on the mailing list. Why? Here it is again: I have a Text field that contains paragraph text and for security reasons I need to have it encrypted. If I do this, how can I still implement full text search on it? Also, I have a lot of Float columns that need to be protected but the user has to use comparison operators like and on them. Any recommendations? Hi, This is quite a difficult one, and as usual in the field of security depends on how valuable the data is and how difficult you want it to be for an attacker to obtain it. If you let us know what type of data this is and how well it has to be protected, maybe we can help more. security reasons is a bit vague, but I can understand that you don't want to give too much away. I can say one thing though, in order for the data to be indexed by MySQL , it has to be in an unencrypted form somewhere in the database. There is no way I know to get around this, but I hope someone can correct me :) Regards Ian -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to encrypt Text and still be able to use full text search? 3rd Attempt ++
Ian, At 09:36 AM 10/26/2007, you wrote: On 26 Oct 2007 at 9:17, mos wrote: I posted this message twice in the past 3 days, and it never gets on the mailing list. Why? Here it is again: I have a Text field that contains paragraph text and for security reasons I need to have it encrypted. If I do this, how can I still implement full text search on it? Also, I have a lot of Float columns that need to be protected but the user has to use comparison operators like and on them. Any recommendations? Hi, This is quite a difficult one, and as usual in the field of security depends on how valuable the data is and how difficult you want it to be for an attacker to obtain it. If you let us know what type of data this is and how well it has to be protected, maybe we can help more. security reasons is a bit vague, but I can understand that you don't want to give too much away. The data is quite valuable because there is a lot of competition in this particular marketplace and my competitors would like to get their hands on it. I've spent 5 years writing the software and generating the data. Let's say for the sake of argument the data is worth $1 million. How do I stop my competitor from bribing some flunky at the ISP into turning over the backup of my data or just e-mailing the MySQL password file to him? Also I don't want anyone at the ISP viewing the data or changing it because I'd be liable for any data errors. I can say one thing though, in order for the data to be indexed by MySQL , it has to be in an unencrypted form somewhere in the database. There is no way I know to get around this, but I hope someone can correct me :) I hope so too. :) There are quite a few databases out there that have transparent encryption (Blowfish, AES etc.) and I'm wondering why MySQL haven't implemented it, especially now with the new laws that make the company liable for security breaches on the web. On the other databases I've used, I haven't noticed any speed decrease if the table is encrypted. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to encrypt Text and still be able to use full text search? 3rd Attempt ++
[EMAIL PROTECTED] wrote: mos wrote: The data is quite valuable because there is a lot of competition in this particular marketplace and my competitors would like to get their hands on it. I've spent 5 years writing the software and generating the data. Let's say for the sake of argument the data is worth $1 million. How do I stop my competitor from bribing some flunky at the ISP into turning over the backup of my data or just e-mailing the MySQL password file to him? Also I don't want anyone at the ISP viewing the data or changing it because I'd be liable for any data errors. Host the machines in-house. I think that could be done for less than a million bucks for a smallish setup. Of course, I've only ever been a bystander with that sort of project, so the figures may be a lot higher than I'm guessing. For instance, you'd want a beefy connection installed, of course. And then there's the salary for someone to administer to everything. I agree. If you're using shared hosting, forget about encryption. Physical access to the machines ALWAYS trumps every other kind of security, so you can't do what you're trying to do (secure data in an insecure environment). Rent a T1 line for $500/mo and charge customers what the data is worth. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to encrypt Text and still be able to use full text search? 3rd Attempt ++
mos wrote: The data is quite valuable because there is a lot of competition in this particular marketplace and my competitors would like to get their hands on it. I've spent 5 years writing the software and generating the data. Let's say for the sake of argument the data is worth $1 million. How do I stop my competitor from bribing some flunky at the ISP into turning over the backup of my data or just e-mailing the MySQL password file to him? Also I don't want anyone at the ISP viewing the data or changing it because I'd be liable for any data errors. Host the machines in-house. I think that could be done for less than a million bucks for a smallish setup. Of course, I've only ever been a bystander with that sort of project, so the figures may be a lot higher than I'm guessing. For instance, you'd want a beefy connection installed, of course. And then there's the salary for someone to administer to everything. brian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to encrypt Text and still be able to use full text search? 3rd Attempt ++
On 10/26/07, Baron Schwartz [EMAIL PROTECTED] wrote: [EMAIL PROTECTED] wrote: mos wrote: The data is quite valuable because there is a lot of competition in this particular marketplace and my competitors would like to get their hands on it. I've spent 5 years writing the software and generating the data. Let's say for the sake of argument the data is worth $1 million. How do I stop my competitor from bribing some flunky at the ISP into turning over the backup of my data or just e-mailing the MySQL password file to him? Also I don't want anyone at the ISP viewing the data or changing it because I'd be liable for any data errors. Host the machines in-house. I think that could be done for less than a million bucks for a smallish setup. Of course, I've only ever been a bystander with that sort of project, so the figures may be a lot higher than I'm guessing. For instance, you'd want a beefy connection installed, of course. And then there's the salary for someone to administer to everything. I agree. If you're using shared hosting, forget about encryption. Physical access to the machines ALWAYS trumps every other kind of security, so you can't do what you're trying to do (secure data in an insecure environment). Rent a T1 line for $500/mo and charge customers what the data is worth. Baron I also agree, however for the sake of argument could we assume that the order of the wording in the entry probably imparts a significant amount of it's value? If that is the case, I would think creating a second column of unencrypted text (with a full text index) which would be nothing more than copy of the the text with the words in a random order might provide a bit of the protection that the user is looking for.
Re: How to encrypt Text and still be able to use full text search? 3rd Attempt ++
At 12:31 PM 10/26/2007, you wrote: [EMAIL PROTECTED] wrote: mos wrote: The data is quite valuable because there is a lot of competition in this particular marketplace and my competitors would like to get their hands on it. I've spent 5 years writing the software and generating the data. Let's say for the sake of argument the data is worth $1 million. How do I stop my competitor from bribing some flunky at the ISP into turning over the backup of my data or just e-mailing the MySQL password file to him? Also I don't want anyone at the ISP viewing the data or changing it because I'd be liable for any data errors. Host the machines in-house. I think that could be done for less than a million bucks for a smallish setup. Of course, I've only ever been a bystander with that sort of project, so the figures may be a lot higher than I'm guessing. For instance, you'd want a beefy connection installed, of course. And then there's the salary for someone to administer to everything. I agree. If you're using shared hosting, forget about encryption. Physical access to the machines ALWAYS trumps every other kind of security, so you can't do what you're trying to do (secure data in an insecure environment). Out of curiosity, why can't you use an ISP if the table is encrypted on a dedicated server (using a database other than MySQL, say SQLite)? The pw would be entered via VPN and is not stored in memory or in any file. The data on disk is always encrypted and the selected rows are only decrypted in memory on the fly. The ISP administrator never sees the pw or the unencrypted data. Rent a T1 line for $500/mo and charge customers what the data is worth. Yes, that will solve the problem. I'd have to incur more up front costs but security would be under my control. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to encrypt Text and still be able to use full text search? 3rd Attempt ++
At 01:47 PM 10/26/2007, you wrote: On 10/26/07, Baron Schwartz [EMAIL PROTECTED] wrote: [EMAIL PROTECTED] wrote: mos wrote: The data is quite valuable because there is a lot of competition in this particular marketplace and my competitors would like to get their hands on it. I've spent 5 years writing the software and generating the data. Let's say for the sake of argument the data is worth $1 million. How do I stop my competitor from bribing some flunky at the ISP into turning over the backup of my data or just e-mailing the MySQL password file to him? Also I don't want anyone at the ISP viewing the data or changing it because I'd be liable for any data errors. Host the machines in-house. I think that could be done for less than a million bucks for a smallish setup. Of course, I've only ever been a bystander with that sort of project, so the figures may be a lot higher than I'm guessing. For instance, you'd want a beefy connection installed, of course. And then there's the salary for someone to administer to everything. I agree. If you're using shared hosting, forget about encryption. Physical access to the machines ALWAYS trumps every other kind of security, so you can't do what you're trying to do (secure data in an insecure environment). Rent a T1 line for $500/mo and charge customers what the data is worth. Baron I also agree, however for the sake of argument could we assume that the order of the wording in the entry probably imparts a significant amount of it's value? If that is the case, I would think creating a second column of unencrypted text (with a full text index) which would be nothing more than copy of the the text with the words in a random order might provide a bit of the protection that the user is looking for. Good point. I hadn't thought of that. :) I also need to protect a couple dozen Float fields and thought I could obscure them a bit by adding an offset to them based on an encrypted id stored with each row. It is not going to be as good as encryption but will help to obfuscate the data. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to encrypt Text and still be able to use full text search? 3rd Attempt ++
If you are going to rely on obfuscation to protect valuable data, you might want to consider not posting the particular method you will use on a public mailing list. I think any method you implement will lower the overall security of the system. But, if you must search for encrypted text, you could have another representation of the text salted and hashed word for word. Then salt and hash each search word and search for it in the hashed text. You're still leaking information about word popularity if you do this which may help a determined attacker. - Original Message From: Baron Schwartz [EMAIL PROTECTED] To: mos [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Friday, October 26, 2007 3:54:11 PM Subject: Re: How to encrypt Text and still be able to use full text search? 3rd Attempt ++ I also need to protect a couple dozen Float fields and thought I could obscure them a bit by adding an offset to them based on an encrypted id stored with each row. It is not going to be as good as encryption but will help to obfuscate the data. How much will obfuscation save you? Are you saving nickels and dimes to protect millions of dollars? I've seen people get burned by rolling their own encryption (I could tell you a great war story about a consultant I worked with who invented encryption for SSNs in a database). An insurance policy is something else to consider. Heck, buy the insurance and do weak obfuscation, then get the insurance money and go to Mexico. ... I could put strychnine in the guacamole... Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: How to encrypt Text and still be able to use full text search? 3rd Attempt ++
I also need to protect a couple dozen Float fields and thought I could obscure them a bit by adding an offset to them based on an encrypted id stored with each row. It is not going to be as good as encryption but will help to obfuscate the data. How much will obfuscation save you? Are you saving nickels and dimes to protect millions of dollars? I've seen people get burned by rolling their own encryption (I could tell you a great war story about a consultant I worked with who invented encryption for SSNs in a database). An insurance policy is something else to consider. Heck, buy the insurance and do weak obfuscation, then get the insurance money and go to Mexico. ... I could put strychnine in the guacamole... Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Full Text Search, Storage Engine Types
a) You setup a special index (full text). b) Full text indexes can only be created on MyISAM table types. c) MyISAM does support transactions, it works by table locking. If you are not specifically using transactions, you don't need to worry about it. not transaction safe just means that there is no mechanism for rolling back actions if a series of dependent actions don't get completed. On Jul 7, 2007, at 5:47 PM, David T. Ashley wrote: I'm sending this again, because the server seems to have been down for several hours, and I'm not sure if it went out. - I'd like to do full text search on some fields of some tables, but I'm a bit confused by the documentation. Questions: a)How do I set that up (i.e. do I need to use a specific storage engine for a table)? b)What storage engine(s) are required? c)Are there any restrictions on mixing and matching tables that use different storage engines in the same database? d)Do table locking and transactions work the same (for example, some of the storage engines are described as not transaction safe--unclear what this means)? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Full Text Search, Storage Engines
I'd like to do full text search on some fields of some tables, but I'm a bit confused by the documentation. Questions: a)How do I set that up? b)What storage engines are required? c)Are there any restrictions on mixing and matching tables? d)Do table locking and transactions work the same? Thanks.
Full Text Search, Storage Engine Types
I'm sending this again, because the server seems to have been down for several hours, and I'm not sure if it went out. - I'd like to do full text search on some fields of some tables, but I'm a bit confused by the documentation. Questions: a)How do I set that up (i.e. do I need to use a specific storage engine for a table)? b)What storage engine(s) are required? c)Are there any restrictions on mixing and matching tables that use different storage engines in the same database? d)Do table locking and transactions work the same (for example, some of the storage engines are described as not transaction safe--unclear what this means)? Thanks.
Re: Full Text Search, Storage Engines
Good Evening David- Have you considered using Lucene ? http://lucene.apache.org/java/docs/ M-- This email message and any files transmitted with it contain confidential information intended only for the person(s) to whom this email message is addressed. If you have received this email message in error, please notify the sender immediately by telephone or email and destroy the original message without making a copy. Thank you. - Original Message - From: David T. Ashley [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, July 06, 2007 9:19 PM Subject: Full Text Search, Storage Engines I'd like to do full text search on some fields of some tables, but I'm a bit confused by the documentation. Questions: a)How do I set that up? b)What storage engines are required? c)Are there any restrictions on mixing and matching tables? d)Do table locking and transactions work the same? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Full Text Search, Storage Engine Types
David T. Ashley wrote: I'm sending this again, because the server seems to have been down for several hours, and I'm not sure if it went out. Yes, I've been getting messages from yesterday, too. But I did get both of your messages. I don't know what's up. I'd like to do full text search on some fields of some tables, but I'm a bit confused by the documentation. Questions: a)How do I set that up (i.e. do I need to use a specific storage engine for a table)? Yes, MyISAM only. b)What storage engine(s) are required? c)Are there any restrictions on mixing and matching tables that use different storage engines in the same database? d)Do table locking and transactions work the same (for example, some of the storage engines are described as not transaction safe--unclear what this means)? Since it only applies to MyISAM, you have no transactions, and you get table-level locks. See http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html for more details. cheers Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
2 Questions: 1. Full-text Search 2. Loading 1Gb Data
Dear list,
I'm programming a PHP-driven Search Engine for a newspaper.
Full-text Search with MyISAM Tables, MySQL 4.1.11, PHP 4.3.0
1GB of Text encoded by UTF-8
An average of 1Mbyte Data is inserted to database every day
A common SQL-Query:
SELECT COUNT(*) FROM news_archive
WHERE
MATCH(news_title, news_text) AGAINST('%s' IN BOOLEAN MODE)
1st Question:
Do you recommend any setting to improve the speed?
Is there anything which I should keep in mind?
2nd Question:
Before this project, I was using phpMyAdmin to backup/restore my database. But
today I need to load 1Gb of data into my tables. There are two problems with
the phpMyAdmin method: Time-out and Max Post-size.
Is there a better way to perfrom this task?
Thank you in advance,
Behzad
P.S.
Database Scheme:
-- Table structure for table `news_archive`
--
CREATE TABLE `news_archive` (
`news_id` int(10) unsigned NOT NULL auto_increment,
`news_type_id` int(10) unsigned NOT NULL,
`news_date` mediumint(8) unsigned NOT NULL default '0',
`news_title` text collate utf8_persian_ci NOT NULL,
`news_text` text collate utf8_persian_ci NOT NULL,
`news_file` varchar(100) character set ascii NOT NULL,
PRIMARY KEY (`news_id`),
KEY `news_date` (`news_date`),
KEY `news_type_id` (`news_type_id`),
FULLTEXT KEY `news_title` (`news_title`,`news_text`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_persian_ci COMMENT='News
Archive';
--
--
-- Table structure for table `news_subtypes`
--
CREATE TABLE `news_subtypes` (
`news_subtype_id` int(10) unsigned NOT NULL,
`news_type_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`news_subtype_id`),
KEY `news_type_id` (`news_type_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_persian_ci;
--
--
-- Table structure for table `news_types`
--
CREATE TABLE `news_types` (
`type_id` int(10) unsigned NOT NULL auto_increment,
`type_name` varchar(100) collate utf8_persian_ci NOT NULL,
PRIMARY KEY (`type_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_persian_ci COMMENT='News
Types';
-
Want to be your own boss? Learn how on Yahoo! Small Business.
FULL TEXT SEARCH ALTERNATIVES...
Hi! I'm getting a lot of pushback on using mysql for full-text searching on over 30,000,000 documents. It's starting to slow down when using more than 10-15 keywords. Is there an alternative anyone is using? I don't want to replace the database, but I do need to speed up the keyword search. Any ideas? Thanks in advance! -- Avi
Re: FULL TEXT SEARCH ALTERNATIVES...
You can tune the fulltext search in a few ways using the config, try read up on the various server variables that has to do with fulltext indexing. Other than that there is always the option of upgrading the hardware :) [EMAIL PROTECTED] wrote: Hi! I'm getting a lot of pushback on using mysql for full-text searching on over 30,000,000 documents. It's starting to slow down when using more than 10-15 keywords. Is there an alternative anyone is using? I don't want to replace the database, but I do need to speed up the keyword search. Any ideas? Thanks in advance! -- Avi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FULL TEXT SEARCH ALTERNATIVES...
At 05:27 PM 8/2/2006, you wrote: Hi! I'm getting a lot of pushback on using mysql for full-text searching on over 30,000,000 documents. It's starting to slow down when using more than 10-15 keywords. Is there an alternative anyone is using? I don't want to replace the database, but I do need to speed up the keyword search. Any ideas? Thanks in advance! -- Avi Avi, Take a look at Sphinx from http://www.shodan.ru/projects/sphinx/. It's fast and it's free. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FULL TEXT search and Thai
Best group member, I have a problem. I was going to use FULL TEXT search for my Thai client. It is working smooth with English text and wordings, the indexing and search works fine. The problem with Thai text is that words are not separated with a white space as in English and other languages. I think this screws up the indexing, and complete sentences are classed as a word. Assume Thai characters: Thisisasentenceinthai. ButIcannotsearchforsentenceinthatsearch. I want to search for sentence, but can not. How can this be done? And will the indexing ever work? Best regards, Peter Lauri
special char in full-text search
Hi All, I am using the MySQL full text search capability in the search workflow in my appplication. I found that MySQL treats special character like *./,* etc. as tokenizers if they are not specified within a phrase(inside double quotes). For ex. If the search string entered is M.B.A or 24/7 then it searches for M, B, and A sepearately. I can't include the quotes for all search strings as wild char(*) search would not work when specified within a phrase. Is there a way to avoid MySQL from tokenizing the search criteria? Any ideas? Thanks, Harini -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: special char in full-text search
MySQL doesn't index the special characters, some of which are considered word separators, like the period. MySQL indexes words, not strings (sort of). I had a similar problem on one of my projects where I needed to index resumes for searching. Typical searches would include C++, C#, .NET, etc. None of which mysql would index. What I did was created a word conversion list and added a search words field to the database. The search words field would contain the problematic search words into strings that MySQL would index (CPlusPlus, CSharp, dotNET, etc). The full text index would then be a compound index of the original text plus the search words field. The front end would handle searching the text for conversion words before inserting/updating the database. It would then convert the problematic words that were present and populate the search words field with them. The front would also do a similar conversion when a user entered a search for one of those words. It's not perfect, but I always used boolean full text searching and MySQL does support proximity searches, so it worked well enough. Although I never did figure out how to search on C, J, or any other single character programming language. Hope that gives you some ideas. - Original Message - From: Harini Raghavan [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, July 06, 2006 2:06 AM Subject: special char in full-text search Hi All, I am using the MySQL full text search capability in the search workflow in my appplication. I found that MySQL treats special character like *./,* etc. as tokenizers if they are not specified within a phrase(inside double quotes). For ex. If the search string entered is M.B.A or 24/7 then it searches for M, B, and A sepearately. I can't include the quotes for all search strings as wild char(*) search would not work when specified within a phrase. Is there a way to avoid MySQL from tokenizing the search criteria? Any ideas? Thanks, Harini -- 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]
Full Text Search across 2 tables.
I have 2 tables which have full text index on each of their columns. Table 1 - forums_topics field - topic Table 2 - forums_messages field - message Is it possible to search them both in one query and determine which table the result is being returned from in the search results? Thanks Steffan --- T E L 6 0 2 . 5 7 9 . 4 2 3 0 | F A X 6 0 2 . 9 7 1 . 1 6 9 4 Steffan A. Cline [EMAIL PROTECTED] Phoenix, Az http://www.ExecuChoice.net USA AIM : SteffanC ICQ : 57234309 Lasso Partner Alliance Member --- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Full Text Search across 2 tables.
Maybe something like this: select ft.topic, fm.message from forums_topics ft, forums_messages fm match (ft.topic, fm.message) against (...) if topic is null then the hit is from fm and vice versa... haven't tried it, so might not work :) Steffan A. Cline wrote: I have 2 tables which have full text index on each of their columns. Table 1 - forums_topics field - topic Table 2 - forums_messages field - message Is it possible to search them both in one query and determine which table the result is being returned from in the search results? Thanks Steffan --- T E L 6 0 2 . 5 7 9 . 4 2 3 0 | F A X 6 0 2 . 9 7 1 . 1 6 9 4 Steffan A. Cline [EMAIL PROTECTED] Phoenix, Az http://www.ExecuChoice.net USA AIM : SteffanC ICQ : 57234309 Lasso Partner Alliance Member --- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question on full text search scores, different content same score
Mark Steudel wrote:
I was doing a full text search and had a question on why two different
entries got the same score:
Here is my select statement
SELECT
id,
pubyear,
MATCH ( title ) AGAINST ( 'Nursing home federal requirements, guidelines to
surveyors, and survey protocols (MLM)' ) AS score,
title
FROM tblbook
WHERE MATCH ( title ) AGAINST ( 'Nursing home federal requirements,
guidelines to surveyors, and survey protocols (MLM)' )
Here are the two results that come back:
title: Nursing home federal requirements, guidelines to surveyors, and
survey protocols
score: 15.230528838754
title: Nursing home federal requirements, guidelines to surveyors, and
survey protocols (MLM)
score: 15.230528838754
I'm sure its work as expected, but I was hoping some could enlighten me as
to why these scores are the same. There are 35 entries in the database and
these are the only two titles that are similar. I also tried IN BOOLEAN MODE
as well.
Thanks, Mark
Perhaps because '(' and ')' are ignored, and 'MLM' is a three letter
word, and not indexed.
--
Gerald L. Clark
Supplier Systems Corporation
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Question on full text search scores, different content same score
Is there a way to escape the parenthesis?
-Original Message-
From: Gerald L. Clark [mailto:[EMAIL PROTECTED]
Sent: Friday, June 09, 2006 6:18 AM
To: Mark Steudel
Cc: mysql@lists.mysql.com
Subject: Re: Question on full text search scores, different content same
score
Mark Steudel wrote:
I was doing a full text search and had a question on why two different
entries got the same score:
Here is my select statement
SELECT
id,
pubyear,
MATCH ( title ) AGAINST ( 'Nursing home federal requirements, guidelines
to
surveyors, and survey protocols (MLM)' ) AS score,
title
FROM tblbook
WHERE MATCH ( title ) AGAINST ( 'Nursing home federal requirements,
guidelines to surveyors, and survey protocols (MLM)' )
Here are the two results that come back:
title: Nursing home federal requirements, guidelines to surveyors, and
survey protocols
score: 15.230528838754
title: Nursing home federal requirements, guidelines to surveyors, and
survey protocols (MLM)
score: 15.230528838754
I'm sure its work as expected, but I was hoping some could enlighten me as
to why these scores are the same. There are 35 entries in the database and
these are the only two titles that are similar. I also tried IN BOOLEAN
MODE
as well.
Thanks, Mark
Perhaps because '(' and ')' are ignored, and 'MLM' is a three letter
word, and not indexed.
--
Gerald L. Clark
Supplier Systems Corporation
--
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]
Full text search novice
MySQL 5.0.19 running in Apache 2 on Mac OS X 10.4.6 I've been dipping my newbie toe into the murky waters of full text searching, but not with a great deal of success. I have a complex search set up which searches nine tables (potentially a whole bunch more, but for the present purpose...), five of which might contain quite long lumps of text in TEXT fields (biographies, for instance), the other four with shorter stuff in VARCHAR(127) fields. All these fields have full text indices set up. Searching for the word 'olympic', if I use the full text search - match (...) against (...) - I find six people. If I use the tried and trusted like '%...%' method I find seven - the original six plus one more. The only difference between this extra one and the others is that she only has the word 'olympics' in her data, whereas all the others have 'olympic' somewhere. I thought full text searching would cater for this kind of thing, but it appears not? After I first tried it I read the thread about running myisamchk after upgrading across versions. I've just gone from 3.23.x to this version, so I thought maybe that was the answer, but having done all that I find there's no change. -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ Among the things money can't buy is what it used to. -- Max Kauffmann -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Question on full text search scores, different content same score
I was doing a full text search and had a question on why two different entries got the same score: Here is my select statement SELECT id, pubyear, MATCH ( title ) AGAINST ( 'Nursing home federal requirements, guidelines to surveyors, and survey protocols (MLM)' ) AS score, title FROM tblbook WHERE MATCH ( title ) AGAINST ( 'Nursing home federal requirements, guidelines to surveyors, and survey protocols (MLM)' ) Here are the two results that come back: title: Nursing home federal requirements, guidelines to surveyors, and survey protocols score: 15.230528838754 title: Nursing home federal requirements, guidelines to surveyors, and survey protocols (MLM) score: 15.230528838754 I'm sure its work as expected, but I was hoping some could enlighten me as to why these scores are the same. There are 35 entries in the database and these are the only two titles that are similar. I also tried IN BOOLEAN MODE as well. Thanks, Mark - Mark Steudel NetRiver Web and Application Developer 555 Dayton St. Suite A Edmonds, WA 98020 w: http://www.netriver.net p: 425.741.7014 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
full text search
Hi, I've always done conventional searches Where ( title like '%$key1%') or(isynopsis like '%$key2%') etc etc But the client has increased the complexity of the search and especially the size of his database and the search has really slowed. (particularly now that I have to search a longtext field.) So should I consider fulltext searching? Do I have to build an index first and how often is it updated etc? -- zzapper Success for Techies and Vim,Zsh tips http://SuccessTheory.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: full text search
Hello. Do I have to build an index first and how often is it updated etc? Yes, you should create FULLTEXT indexes. However I'm not sure if you will get any benefits for searching inner part of the words (*$key1*), because you can search using asterisk only on the left-most prefixes of the words. The performance of full-text searches some times is low, however it was improved in 5.1. Pluggable full-text parsers have appeared in this release as well. For a pity 5.1 is alpha quality now, and in most cases you shouldn't use it in a production environment. zzapper wrote: Hi, I've always done conventional searches Where ( title like '%$key1%') or(isynopsis like '%$key2%') etc etc But the client has increased the complexity of the search and especially the size of his database and the search has really slowed. (particularly now that I have to search a longtext field.) So should I consider fulltext searching? Do I have to build an index first and how often is it updated etc? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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]
Full text search
Hi there, I am facing problems with fulltext search on MySQL 4.0.18 Problem is, that words which are not seperated by space are not found. Example: A search for dsl will not find DSL-Modem I looked it up on mysql.com help, but despite the fact that this is not seperated by space and only 3 letter, I could not find a solution. Can anybody point me to the right way? Thank you for any help, Merlin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Full text search
Merlin wrote: Hi there, I am facing problems with fulltext search on MySQL 4.0.18 Problem is, that words which are not seperated by space are not found. Example: A search for dsl will not find DSL-Modem I looked it up on mysql.com help, but despite the fact that this is not seperated by space and only 3 letter, I could not find a solution. Can anybody point me to the right way? Thank you for any help, Minimum word length for full text index is four by default. Information about changing default behaviour: http://dev.mysql.com/doc/mysql/en/fulltext-fine-tuning.html Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Full text search
Merlin wrote: Hi there, I am facing problems with fulltext search on MySQL 4.0.18 Problem is, that words which are not seperated by space are not found. Example: A search for dsl will not find DSL-Modem I looked it up on mysql.com help, but despite the fact that this is not seperated by space and only 3 letter, I could not find a solution. Can anybody point me to the right way? Thank you for any help, Merlin Show us the query that fails, fulltext works ok here. SELECT id,txt,ch FROM fulltxt WHERE txt LIKE %modem; id,txt,ch 0,DSL-modem,DSL-Modem 1,Cable-Modem,Cable-Modem SELECT id,txt,ch FROM fulltxt WHERE txt LIKE dsl%; id,txt,ch 0,DSL-modem,DSL-Modem id is int, txt is text, ch is char. HTH -- Thanks, James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Full text search
Hello. Show us the query that fails, fulltext works ok here. LIKE doesn't use fulltext. Show us the query that fails, fulltext works ok here. SELECT id,txt,ch FROM fulltxt WHERE txt LIKE %modem; id,txt,ch 0,DSL-modem,DSL-Modem 1,Cable-Modem,Cable-Modem SELECT id,txt,ch FROM fulltxt WHERE txt LIKE dsl%; id,txt,ch 0,DSL-modem,DSL-Modem id is int, txt is text, ch is char. HTH -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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]
FULL TEXT SEARCH: XML Encoded string field
Hi everyone, I have a freeform string field in one of my tables that is xml encoded. Basically, i can create xml tags on the fly as and when i want, and just store the entire xml string in the field. This eliminates the need to create a new column everytime i have a new datatype. I can just encode with a new xml tag, and add it to the freeform string field. The problem lies in full text searching. Since everything is xml encoded, is there anyway to fulltext search this xmlencoded string field, but ignore all the xml tags (item1/item1 car/car book/book etc)? Many thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query on Full-text search
Hi, Can anybody give me the optimized query for full-text searching? User will be entering one or more words in a text box and I wanted to display the records which has atleast one word of the user input. Assume that my table has two fields USER and DESCRIPTION. I wanted to search the DESCRIPTION field for the input entered in the textbox. I use PHP. Please help me. Thanks in advance. Regards, Rakki Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments.
Re: Query on Full-text search
Rakki wrote:
Hi,
Can anybody give me the optimized query for full-text searching?
User will be entering one or more words in a text box and I wanted to
display the records which has atleast one word of the user input.
Assume that my table has two fields USER and DESCRIPTION.
I wanted to search the DESCRIPTION field for the input entered in the
textbox. I use PHP. Please help me.
Place a full-text index on the fields in question, and then use
something like:
SELECT * FROM table WHERE MATCH(user, description) AGAINST
('$escaped_query') LIMIT 25
Where $escaped_query is the query that has been run through
mysql_real_escape_string().
Jasper
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
full-text search
I have a TEXT field in my db (4.0) that has lists of IP numbers; can a full-text search be done for IP numbers? If so could you show me an example query? Thanks Rob
full-text search
I have a TEXT field in my db (4.0) that has lists of IP numbers; can a full-text search be done for IP numbers? If so could you show me an example query? Thanks Rob
Re: full-text search
Ron McKeever [EMAIL PROTECTED] wrote on 29/04/2005 14:09:38: I have a TEXT field in my db (4.0) that has lists of IP numbers; can a full-text search be done for IP numbers? Unfortunately not, because Fulltext regards the dots as terminators. The IP address 192.168.32.2 will therefore be keyed as the four words 192, 168, 32, and 2. Since some of these fall below the minimum word length, they will be ignored. However, you might look at the functions INET_NTOA() and INET_ATON() which convert between the string and 32-bit binary representations of an IP address. http://dev.mysql.com/doc/mysql/en/miscellaneous-functions.html Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Full-text search performance issues
Since MySQL stores RowIDs with the indexed words instead of the table's primary key IDs, and since it uses only one index per table in any query, performing a full-text search on a large table (several million rows) and joining it with another large table proves to be extremely slow! The bottleneck seems to be the disk, as MySQL generates an insane number of disk reads while trying to join the two tables without using common indexes between the two. Basically I have two tables, A and B. These tables cannot be merged, so there's no way around using a join. One of these tables, A, has a TEXT column that is full-text indexed. Now these tables are normally joined using the indexes on their primary keys. But if use a full-text criteria in the WHERE clause against A, then MySQL will NOT use the primary key index on A to perform the join. It instead seems to get the result from the full-text index and uses it to perform a huge number of random (unsorted) lookups on the second table to perform the join. I tried to perform a separate query on each table and store the results (primary key IDs) in a couple of temp tables, then join these tables manually. But these tables can be huge, and doing this for each web paging request is very inefficient. In addition, limiting the size of these temp tables would render the search dysfunctional because it won't be encompassing any more. With non-full-text indexes, you can just merge the indexes into a composite index to work around the single-index-per-table limitation. But you can't merge a regular primary key index and a full-text index into a composite index, and I have no idea why MySQL developers didn't just use the primary key IDs with the indexed words, as in MS SQL Server full-text index, instead of the physical row IDs. May be it's a MyISAM limitation that an index, no matter what type it is, has to use physical row IDs as table pointers instead of primary keys. Is there any way around this limitation? I'd like to know if someone has been in a such a situation and how he or she solved it. I appreciate your help! __ Do you Yahoo!? Meet the all-new My Yahoo! - Try it today! http://my.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Full text search in mulitple-table query
At 15:50 -0800 27-01-2005, cristopher pierson ewing wrote:
Shawn,
Okay, it turns out that I can solve my problem by reordering the
elements of the WHERE clause at the end of the query I sent before.
I've gotten good results with the following version (it breaks all
the fields in the Fulltext search into separate searches):
SELECT
t1.course_id,
t1.course_title,
t1.course_subtitle,
t1.course_brochure_path,
t2.course_start_date,
t2.course_end_date,
t4.location_name1,
t4.location_name2,
t4.location_city,
t4.location_state,
t5.allow_online_registration,
t6.course_keywords
FROM
cme_course_info.tblCourses t1
LEFT JOIN cme_course_info.tblCourseDates t2
ON t1.course_id = t2.course_id
LEFT JOIN cme_course_info.tblCourseLocations t3
ON t1.course_id = t3.course_id
LEFT JOIN cme_course_info.tblLocations t4
ON t3.location_record = t4.location_record
LEFT JOIN cme_course_info.tblCourseWebSwitches t5
ON t1.course_id = t5.course_id
LEFT JOIN cme_course_info.tblCourseExtraInfo t6
ON t1.course_id = t6.course_id
WHERE
t1.course_webready='1'
AND
t3.primary_location='1'
AND
MATCH (t6.course_keywords) AGAINST ('care')
OR
MATCH (t6.course_description) AGAINST ('care')
OR
MATCH (t6.course_intended_audience) AGAINST ('care')
Create a fulltext index on 3 columns and search :
MATCH (t6.course_keywords, t6.course_description,
t6.course_intended_audience) AGAINST ('care')
OR
MATCH (t1.course_title) AGAINST ('care')
AND
t2.course_start_date'2005-02-01'
AND
t2.course_end_date'2005-12-31'
AND
t1.course_type_code='MJ'
ORDER BY
t2.course_start_date, t2.course_end_date, t1.course_title;
This pretty much ends my problem, except for one interesting aside
that still has me confused. If I just slightly alter the order of
all the various sub-clauses in the WHERE portion of the query, I get
some courses that violate the requirement course_type_code='MJ'
(last part of WHERE)
Specifically, if I take the MATCH parts and move them up to right
after the WHERE, like so:
WHERE
MATCH (t6.course_keywords) AGAINST ('care')
OR
MATCH (t6.course_description) AGAINST ('care')
OR
MATCH (t6.course_intended_audience) AGAINST ('care')
OR
MATCH (t1.course_title) AGAINST ('care')
AND
t2.course_start_date'2005-02-01'
AND
t2.course_end_date'2005-12-31'
AND
t1.course_webready='1'
AND
t3.primary_location='1'
AND
t1.course_type_code='MJ'
I think you must use ():
WHERE (
MATCH (t6.course_keywords) AGAINST ('care')
OR
MATCH (t6.course_description) AGAINST ('care')
OR
MATCH (t6.course_intended_audience) AGAINST ('care')
OR
MATCH (t1.course_title) AGAINST ('care')
)
AND
t2.course_start_date'2005-02-01'
AND
t2.course_end_date'2005-12-31'
AND
t1.course_webready='1'
AND
t3.primary_location='1'
AND
t1.course_type_code='MJ'
Suddenly, I get courses showing up that violate all the later
requirements, such as the ones on course_start_date,
course_end_date, and so on to the end.
Is there a requirement as to which order sub-clauses of a WHERE
clause have to follow? I couldn't find anything that described
this, but I'm perfectly willing to admit I have a hard time finding
lots of things in the online docs.
Thanks for any lucidity anyone can lend,
Cris
Cris Ewing
CME and Telehealth Web Services
University of Washington
School of Medicine
Work Phone: (206) 685-9116
Home Phone: (206) 365-3413
E-mail: [EMAIL PROTECTED]
***
On Thu, 27 Jan 2005 [EMAIL PROTECTED] wrote:
cristopher pierson ewing [EMAIL PROTECTED] wrote on 01/27/2005
04:01:22 PM:
I'm running a query that pulls information from about six different
tables
in a DB. I'd like to be able to do a fulltext search on fields in
several
different tables. The end result should be that any row with a fulltext
match in any of the fields in any table gets returned. I've tried a
syntax that looks like this:
WHERE MATCH (table1.field1,table2.field2 table2.field3)
AGAINST ('some,nifty,words')
but I get back an error message that says:
ERROR 1210: Wrong arguments to MATCH
If all the ffields are from one table, then I get an error that says:
ERROR 1191: Can't find FULLTEXT index matching the column list
Is it possible to do a fulltext search on multiple fields in a quesry
that
references more than one table? What would be the correct syntax for
such
a query? Am I limited to doing this via a UNION-type query?
Thanks for any information that you can give me, and sorry if it seems a
trivial question, I can't seem to find an answer in the documentation
Cris Ewing
CME and Telehealth Web Services
University of Washington
School of Medicine
Work Phone: (206) 685-9116
Home Phone: (206) 365-3413
E-mail: [EMAIL PROTECTED]
***
I don't think you can define a single full-text index that spans multiple
tables. That would require the capacity to FT index a view. So I must
assume that you have created a
Re: Full text search in mulitple-table query
On Fri, 28 Jan 2005, Santino wrote:
At 15:50 -0800 27-01-2005, cristopher pierson ewing wrote:
Shawn,
Okay, it turns out that I can solve my problem by reordering the elements
of the WHERE clause at the end of the query I sent before. I've gotten good
results with the following version (it breaks all the fields in the
Fulltext search into separate searches):
SELECT
t1.course_id,
t1.course_title,
t1.course_subtitle,
t1.course_brochure_path,
t2.course_start_date,
t2.course_end_date,
t4.location_name1,
t4.location_name2,
t4.location_city,
t4.location_state,
t5.allow_online_registration,
t6.course_keywords
FROM
cme_course_info.tblCourses t1
LEFT JOIN cme_course_info.tblCourseDates t2
ON t1.course_id = t2.course_id
LEFT JOIN cme_course_info.tblCourseLocations t3
ON t1.course_id = t3.course_id
LEFT JOIN cme_course_info.tblLocations t4
ON t3.location_record = t4.location_record
LEFT JOIN cme_course_info.tblCourseWebSwitches t5
ON t1.course_id = t5.course_id
LEFT JOIN cme_course_info.tblCourseExtraInfo t6
ON t1.course_id = t6.course_id
WHERE
t1.course_webready='1'
AND
t3.primary_location='1'
AND
MATCH (t6.course_keywords) AGAINST ('care')
OR
MATCH (t6.course_description) AGAINST ('care')
OR
MATCH (t6.course_intended_audience) AGAINST ('care')
Create a fulltext index on 3 columns and search :
MATCH (t6.course_keywords, t6.course_description,
t6.course_intended_audience) AGAINST ('care')
So, does seearching on multiple columns only work if you create the
fulltext index on all of them at the same time? When I read the docs they
seemed to imply that indeces created on multiple columns wouldn't be
individually searchable. In other words, if I create a fulltext index on
col1, col2, and col3, then I will not be able to match against only col1.
I created the three as separate indeces so that I could maintain the
ability to search each separately at some point. Am I wrong to do so?
OR
MATCH (t1.course_title) AGAINST ('care')
AND
t2.course_start_date'2005-02-01'
AND
t2.course_end_date'2005-12-31'
AND
t1.course_type_code='MJ'
ORDER BY
t2.course_start_date, t2.course_end_date, t1.course_title;
This pretty much ends my problem, except for one interesting aside that
still has me confused. If I just slightly alter the order of all the
various sub-clauses in the WHERE portion of the query, I get some courses
that violate the requirement course_type_code='MJ' (last part of WHERE)
Specifically, if I take the MATCH parts and move them up to right after the
WHERE, like so:
WHERE
MATCH (t6.course_keywords) AGAINST ('care')
OR
MATCH (t6.course_description) AGAINST ('care')
OR
MATCH (t6.course_intended_audience) AGAINST ('care')
OR
MATCH (t1.course_title) AGAINST ('care')
AND
t2.course_start_date'2005-02-01'
AND
t2.course_end_date'2005-12-31'
AND
t1.course_webready='1'
AND
t3.primary_location='1'
AND
t1.course_type_code='MJ'
I think you must use ():
Yep, that fixed the problem, now I can put the ORed portion of the filter
at the front, where it logically seems to belong and all works just fine.
Thanks for the assist!
WHERE (
MATCH (t6.course_keywords) AGAINST ('care')
OR
MATCH (t6.course_description) AGAINST ('care')
OR
MATCH (t6.course_intended_audience) AGAINST ('care')
OR
MATCH (t1.course_title) AGAINST ('care')
)
AND
t2.course_start_date'2005-02-01'
AND
t2.course_end_date'2005-12-31'
AND
t1.course_webready='1'
AND
t3.primary_location='1'
AND
t1.course_type_code='MJ'
Suddenly, I get courses showing up that violate all the later requirements,
such as the ones on course_start_date, course_end_date, and so on to the
end.
Is there a requirement as to which order sub-clauses of a WHERE clause have
to follow? I couldn't find anything that described this, but I'm perfectly
willing to admit I have a hard time finding lots of things in the online
docs.
Thanks for any lucidity anyone can lend,
Cris
Cris Ewing
CME and Telehealth Web Services
University of Washington
School of Medicine
Work Phone: (206) 685-9116
Home Phone: (206) 365-3413
E-mail: [EMAIL PROTECTED]
***
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Full text search in mulitple-table query
I'm running a query that pulls information from about six different tables
in a DB. I'd like to be able to do a fulltext search on fields in several
different tables. The end result should be that any row with a fulltext
match in any of the fields in any table gets returned. I've tried a
syntax that looks like this:
WHERE MATCH (table1.field1,table2.field2 table2.field3)
AGAINST ('some,nifty,words')
but I get back an error message that says:
ERROR 1210: Wrong arguments to MATCH
If all the ffields are from one table, then I get an error that says:
ERROR 1191: Can't find FULLTEXT index matching the column list
Is it possible to do a fulltext search on multiple fields in a quesry that
references more than one table? What would be the correct syntax for such
a query? Am I limited to doing this via a UNION-type query?
Thanks for any information that you can give me, and sorry if it seems a
trivial question, I can't seem to find an answer in the documentation
Cris Ewing
CME and Telehealth Web Services
University of Washington
School of Medicine
Work Phone: (206) 685-9116
Home Phone: (206) 365-3413
E-mail: [EMAIL PROTECTED]
***
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Full text search in mulitple-table query
cristopher pierson ewing [EMAIL PROTECTED] wrote on 01/27/2005
04:01:22 PM:
I'm running a query that pulls information from about six different
tables
in a DB. I'd like to be able to do a fulltext search on fields in
several
different tables. The end result should be that any row with a fulltext
match in any of the fields in any table gets returned. I've tried a
syntax that looks like this:
WHERE MATCH (table1.field1,table2.field2 table2.field3)
AGAINST ('some,nifty,words')
but I get back an error message that says:
ERROR 1210: Wrong arguments to MATCH
If all the ffields are from one table, then I get an error that says:
ERROR 1191: Can't find FULLTEXT index matching the column list
Is it possible to do a fulltext search on multiple fields in a quesry
that
references more than one table? What would be the correct syntax for
such
a query? Am I limited to doing this via a UNION-type query?
Thanks for any information that you can give me, and sorry if it seems a
trivial question, I can't seem to find an answer in the documentation
Cris Ewing
CME and Telehealth Web Services
University of Washington
School of Medicine
Work Phone: (206) 685-9116
Home Phone: (206) 365-3413
E-mail: [EMAIL PROTECTED]
***
I don't think you can define a single full-text index that spans multiple
tables. That would require the capacity to FT index a view. So I must
assume that you have created a FT index on one or more columns on each of
table1 and table2. If not, that may be your problem (you need to create a
FT index before you can use it).
It may be possible to say
SELECT...
FROM table1
INNER JOIN table2
ON ...
WHERE MATCH (table1.field1) AGAINST (...)
OR MATCH (table2.field2, table2.field3) AGAINST (...)
and get the results you want. I can't test it because I don't have any FT
indexes, yet.
Can you describe your FT index structure? (SHOW CREATE TABLE \G
creates great output for this purpose. Just edit out the fields that
aren't important to this problem if you are worried about size/secrets.)
That would go a long way to help us understand your problems.
Thanks!
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Re: Full text search in mulitple-table query
Shawn,
Thanks for the reply.
Here's the output of SHOW CREATE TABLE for one of the tables in
question:
CREATE TABLE `tblcourseextrainfo` (
`course_id` varchar(6) NOT NULL default '',
`course_description` text,
`course_intended_audience` text,
`course_keywords` text,
PRIMARY KEY (`course_id`),
FULLTEXT KEY `keywords` (`course_keywords`),
FULLTEXT KEY `course_description` (`course_description`),
FULLTEXT KEY `course_intended_audience` (`course_intended_audience`)
) TYPE=MyISAM
As you can see, I've created individual fulltext indeces for three fields
in this table, there is another table called 'tblCourses' where I have a
field called 'course_title' that also has a fulltext index. The query in
question pulls information from these two tables and about 4 others. The
result is a list of courses with all the information our customers need to
see.
here's a sample of what the sql from one query might look like:
SELECT
t1.course_id,
t1.course_title,
t1.course_subtitle,
t1.course_brochure_path,
t2.course_start_date,
t2.course_end_date,
t4.location_name1,
t4.location_name2,
t4.location_city,
t4.location_state,
t5.allow_online_registration,
t6.course_keywords,
t6.course_description
FROM
cme_course_info.tblCourses t1
LEFT JOIN cme_course_info.tblCourseDates t2
ON t1.course_id = t2.course_id
LEFT JOIN cme_course_info.tblCourseLocations t3
ON t1.course_id = t3.course_id
LEFT JOIN cme_course_info.tblLocations t4
ON t3.location_record = t4.location_record
LEFT JOIN cme_course_info.tblCourseWebSwitches t5
ON t1.course_id = t5.course_id
LEFT JOIN cme_course_info.tblCourseExtraInfo t6
ON t1.course_id = t6.course_id
WHERE
t1.course_type_code='MJ'
AND
t1.course_webready='1'
AND
t3.primary_location='1'
AND
t2.course_start_date'2005-01-01'
AND
t2.course_end_date'2005-12-31'
AND
MATCH (t6.course_keywords,t1.course_title)
AGAINST ('kidney,rheumatic');
Can you see any problems here that I'm missing?
Thanks,
Cris
Cris Ewing
CME and Telehealth Web Services
University of Washington
School of Medicine
Work Phone: (206) 685-9116
Home Phone: (206) 365-3413
E-mail: [EMAIL PROTECTED]
***
On Thu, 27 Jan 2005 [EMAIL PROTECTED] wrote:
cristopher pierson ewing [EMAIL PROTECTED] wrote on 01/27/2005
04:01:22 PM:
I'm running a query that pulls information from about six different
tables
in a DB. I'd like to be able to do a fulltext search on fields in
several
different tables. The end result should be that any row with a fulltext
match in any of the fields in any table gets returned. I've tried a
syntax that looks like this:
WHERE MATCH (table1.field1,table2.field2 table2.field3)
AGAINST ('some,nifty,words')
but I get back an error message that says:
ERROR 1210: Wrong arguments to MATCH
If all the ffields are from one table, then I get an error that says:
ERROR 1191: Can't find FULLTEXT index matching the column list
Is it possible to do a fulltext search on multiple fields in a quesry
that
references more than one table? What would be the correct syntax for
such
a query? Am I limited to doing this via a UNION-type query?
Thanks for any information that you can give me, and sorry if it seems a
trivial question, I can't seem to find an answer in the documentation
Cris Ewing
CME and Telehealth Web Services
University of Washington
School of Medicine
Work Phone: (206) 685-9116
Home Phone: (206) 365-3413
E-mail: [EMAIL PROTECTED]
***
I don't think you can define a single full-text index that spans multiple
tables. That would require the capacity to FT index a view. So I must
assume that you have created a FT index on one or more columns on each of
table1 and table2. If not, that may be your problem (you need to create a
FT index before you can use it).
It may be possible to say
SELECT...
FROM table1
INNER JOIN table2
ON ...
WHERE MATCH (table1.field1) AGAINST (...)
OR MATCH (table2.field2, table2.field3) AGAINST (...)
and get the results you want. I can't test it because I don't have any FT
indexes, yet.
Can you describe your FT index structure? (SHOW CREATE TABLE \G
creates great output for this purpose. Just edit out the fields that
aren't important to this problem if you are worried about size/secrets.)
That would go a long way to help us understand your problems.
Thanks!
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Full text search in mulitple-table query
Shawn,
Okay, it turns out that I can solve my problem by reordering the elements
of the WHERE clause at the end of the query I sent before. I've gotten
good results with the following version (it breaks all the fields in the
Fulltext search into separate searches):
SELECT
t1.course_id,
t1.course_title,
t1.course_subtitle,
t1.course_brochure_path,
t2.course_start_date,
t2.course_end_date,
t4.location_name1,
t4.location_name2,
t4.location_city,
t4.location_state,
t5.allow_online_registration,
t6.course_keywords
FROM
cme_course_info.tblCourses t1
LEFT JOIN cme_course_info.tblCourseDates t2
ON t1.course_id = t2.course_id
LEFT JOIN cme_course_info.tblCourseLocations t3
ON t1.course_id = t3.course_id
LEFT JOIN cme_course_info.tblLocations t4
ON t3.location_record = t4.location_record
LEFT JOIN cme_course_info.tblCourseWebSwitches t5
ON t1.course_id = t5.course_id
LEFT JOIN cme_course_info.tblCourseExtraInfo t6
ON t1.course_id = t6.course_id
WHERE
t1.course_webready='1'
AND
t3.primary_location='1'
AND
MATCH (t6.course_keywords) AGAINST ('care')
OR
MATCH (t6.course_description) AGAINST ('care')
OR
MATCH (t6.course_intended_audience) AGAINST ('care')
OR
MATCH (t1.course_title) AGAINST ('care')
AND
t2.course_start_date'2005-02-01'
AND
t2.course_end_date'2005-12-31'
AND
t1.course_type_code='MJ'
ORDER BY
t2.course_start_date, t2.course_end_date, t1.course_title;
This pretty much ends my problem, except for one interesting aside that
still has me confused. If I just slightly alter the order of all the
various sub-clauses in the WHERE portion of the query, I get some courses
that violate the requirement course_type_code='MJ' (last part of WHERE)
Specifically, if I take the MATCH parts and move them up to right after
the WHERE, like so:
WHERE
MATCH (t6.course_keywords) AGAINST ('care')
OR
MATCH (t6.course_description) AGAINST ('care')
OR
MATCH (t6.course_intended_audience) AGAINST ('care')
OR
MATCH (t1.course_title) AGAINST ('care')
AND
t2.course_start_date'2005-02-01'
AND
t2.course_end_date'2005-12-31'
AND
t1.course_webready='1'
AND
t3.primary_location='1'
AND
t1.course_type_code='MJ'
Suddenly, I get courses showing up that violate all the later
requirements, such as the ones on course_start_date, course_end_date, and
so on to the end.
Is there a requirement as to which order sub-clauses of a WHERE clause
have to follow? I couldn't find anything that described this, but I'm
perfectly willing to admit I have a hard time finding lots of things in
the online docs.
Thanks for any lucidity anyone can lend,
Cris
Cris Ewing
CME and Telehealth Web Services
University of Washington
School of Medicine
Work Phone: (206) 685-9116
Home Phone: (206) 365-3413
E-mail: [EMAIL PROTECTED]
***
On Thu, 27 Jan 2005 [EMAIL PROTECTED] wrote:
cristopher pierson ewing [EMAIL PROTECTED] wrote on 01/27/2005
04:01:22 PM:
I'm running a query that pulls information from about six different
tables
in a DB. I'd like to be able to do a fulltext search on fields in
several
different tables. The end result should be that any row with a fulltext
match in any of the fields in any table gets returned. I've tried a
syntax that looks like this:
WHERE MATCH (table1.field1,table2.field2 table2.field3)
AGAINST ('some,nifty,words')
but I get back an error message that says:
ERROR 1210: Wrong arguments to MATCH
If all the ffields are from one table, then I get an error that says:
ERROR 1191: Can't find FULLTEXT index matching the column list
Is it possible to do a fulltext search on multiple fields in a quesry
that
references more than one table? What would be the correct syntax for
such
a query? Am I limited to doing this via a UNION-type query?
Thanks for any information that you can give me, and sorry if it seems a
trivial question, I can't seem to find an answer in the documentation
Cris Ewing
CME and Telehealth Web Services
University of Washington
School of Medicine
Work Phone: (206) 685-9116
Home Phone: (206) 365-3413
E-mail: [EMAIL PROTECTED]
***
I don't think you can define a single full-text index that spans multiple
tables. That would require the capacity to FT index a view. So I must
assume that you have created a FT index on one or more columns on each of
table1 and table2. If not, that may be your problem (you need to create a
FT index before you can use it).
It may be possible to say
SELECT...
FROM table1
INNER JOIN table2
ON ...
WHERE MATCH (table1.field1) AGAINST (...)
OR MATCH (table2.field2, table2.field3) AGAINST (...)
and get the results you want. I can't test it because I don't have any FT
indexes, yet.
Can you describe your FT index structure? (SHOW CREATE TABLE \G
creates great output for this purpose. Just edit out the fields that
aren't important to this problem if you are
Re: Copying DB and full-text search files from one server to another
Hello. Like MyISAM data files, InnoDB data and log files are binary-compatible on all platforms if the floating-point number format on the machines is the same. You can move your database by copying all the relevant files. If the floating-point formats on the machines are different but you have not used FLOAT or DOUBLE data types in your tables, then the procedure is the same: Just copy the relevant files. If the formats are different and your tables contain floating-point data, you have to use mysqldump to dump your tables on one machine and then import the dump files on the other machine. Don't forget to put the same values for the ft_min_word_len and ft_max_word_len variables on your another server (and I think you should at least restart your server after moving InnoDB). For more information see: http://dev.mysql.com/doc/mysql/en/Moving.html http://dev.mysql.com/doc/mysql/en/MyISAM_storage_engine.html Homam S.A. [EMAIL PROTECTED] wrote: Is it possible to copy the database files (both MyISAM and InnoDB) that contain tables, indices, and full-text indices from one MySQL server to another running/active MySQL server and start using them right away? I appreciate your help! Homam __ Do you Yahoo!? Take Yahoo! Mail with you! Get it on your mobile phone. http://mobile.yahoo.com/maildemo -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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]
Copying DB and full-text search files from one server to another
Is it possible to copy the database files (both MyISAM and InnoDB) that contain tables, indices, and full-text indices from one MySQL server to another running/active MySQL server and start using them right away? I appreciate your help! Homam __ Do you Yahoo!? Take Yahoo! Mail with you! Get it on your mobile phone. http://mobile.yahoo.com/maildemo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Full Text Search - Limits?
Hello. Some information you can find in comments at: http://dev.mysql.com/doc/mysql/en/Fulltext_Search.html EP [EMAIL PROTECTED] wrote: I've looked in the documentation but didn't see any indication of the limit= s of Full-Text Search in terms of how many characters/words it can process = per row. For example, if I have a column with 4,000 character strings in it, can I u= se it effectively in Full-Text Searching? What if the column holds gigabytes of text in each row? My mind is probably stuck in an indexing paradigm, but I'd like to know w= here the limits (of Full Text search) are, if any. Can anyone advise? [Thanks!] Eric Pederson -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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: Full Text Search - Limits?
On Wed, 15 Dec 2004, EP wrote:
Thomas Spahni [EMAIL PROTECTED] wrote:
the column type will limit the number of characters per row. A column
of type TEXT will hold up to 65,535 characters but with LONGTEXT you
can put up to 4,294,967,295 charcters into one row. I have an
application with Texts of up to 200 pages in one column. Full-Text
Search is handling this very well.
Thanks...
Really?! If I can follow-up with another question, does experience
suggest Full-Text Search handles a large number of such documents
efficiently? For example, I am expecting to have (up to) one million
documents in my database. I was considering breaking each document into
paragraphs for search efficiency, but if Full-Text Search can search
return results quickly on a large number of long (e.g. 10,000+
character) documents, my database has just become much simpler.
Eric
My average document is 16700 bytes long and I have 21'649 of them (number
growing).
I can give you an example how slow (fast) it is:
mysql select count(*) from unpublished where match (bgetxt) against
('Garten Waldbaum Gutachten');
+--+
| count(*) |
+--+
| 2841 |
+--+
1 row in set (1.97 sec)
mysql select count(*) from unpublished where match (bgetxt) against
('Willensvollstrecker');
+--+
| count(*) |
+--+
| 34 |
+--+
1 row in set (0.03 sec)
This is on modest hardware (single P4, 1GB Ram, SCSI drive). My index
still fits into RAM but was not buffered for the first query above. I
would definitely try to keep your texts in a single piece each.
Thomas Spahni
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Full Text Search - Limits?
Eric, the column type will limit the number of characters per row. A column of type TEXT will hold up to 65,535 characters but with LONGTEXT you can put up to 4,294,967,295 charcters into one row. I have an application with Texts of up to 200 pages in one column. Full-Text Search is handling this very well. Thomas Spahni On Tue, 14 Dec 2004, EP wrote: I've looked in the documentation but didn't see any indication of the limits of Full-Text Search in terms of how many characters/words it can process per row. For example, if I have a column with 4,000 character strings in it, can I use it effectively in Full-Text Searching? What if the column holds gigabytes of text in each row? My mind is probably stuck in an indexing paradigm, but I'd like to know where the limits (of Full Text search) are, if any. Can anyone advise? Eric Pederson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Full Text Search - Limits?
EP [EMAIL PROTECTED] wrote on 15/12/2004 15:44:15: Thomas Spahni [EMAIL PROTECTED] wrote: the column type will limit the number of characters per row. A column of type TEXT will hold up to 65,535 characters but with LONGTEXT you can put up to 4,294,967,295 charcters into one row. I have an application with Texts of up to 200 pages in one column. Full-Text Search is handling this very well. Thanks... Really?! If I can follow-up with another question, does experience suggest Full-Text Search handles a large number of such documents efficiently? For example, I am expecting to have (up to) one million documents in my database. I was considering breaking each document into paragraphs for search efficiency, but if Full-Text Search can search return results quickly on a large number of long (e.g. 10,000+ character) documents, my database has just become much simpler. I see no reason why not. AIUI, Full Text search breaks the documents up into words and indexes each document by avery word in the document. Breaking into paragraphs gives you an approximate position within the document for a hit, and changes the behaviour for multiple keywords (they must both be in the same pararaph) but should have little effect on the efficiency of the index. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Full Text Search - Limits?
Thomas Spahni [EMAIL PROTECTED] wrote: the column type will limit the number of characters per row. A column of type TEXT will hold up to 65,535 characters but with LONGTEXT you can put up to 4,294,967,295 charcters into one row. I have an application with Texts of up to 200 pages in one column. Full-Text Search is handling this very well. Thanks... Really?! If I can follow-up with another question, does experience suggest Full-Text Search handles a large number of such documents efficiently? For example, I am expecting to have (up to) one million documents in my database. I was considering breaking each document into paragraphs for search efficiency, but if Full-Text Search can search return results quickly on a large number of long (e.g. 10,000+ character) documents, my database has just become much simpler. Eric -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Full Text Search - Limits?
I've looked in the documentation but didn't see any indication of the limits of Full-Text Search in terms of how many characters/words it can process per row. For example, if I have a column with 4,000 character strings in it, can I use it effectively in Full-Text Searching? What if the column holds gigabytes of text in each row? My mind is probably stuck in an indexing paradigm, but I'd like to know where the limits (of Full Text search) are, if any. Can anyone advise? [Thanks!] Eric Pederson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Full-Text Search
1. The manual says there is and it gives an example, but I haven't been able to get it to work and neither have other people on this list. If you look at the planned improvements of MySQL, a few of the major items have to do with full text searching. On Oct 26, 2004, at 7:39 PM, Jalil Feghhi wrote: Brent, Thanks for the reply. 1. Is there any way to sort the boolean full-text search results in orther of relevance as in non-bolean mode? I think it is very important. 2. Yes, that is what I meant. You are right. I look into grep. Thanks, -Jalil -Original Message- From: Brent Baisley [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 26, 2004 6:30 AM To: Jalil Feghhi Cc: [EMAIL PROTECTED] Subject: Re: Full-Text Search 1. The rows are sorted in the same order as any other query that doesn't have and order by specified, which means usually in the order they were entered. 2. When you mean location of matches, do you mean something like highlighting? That's the responsibility of the interface, not the database. Remember, MySQL is really just a database engine. It will find your data fast, but it's up to you to present it in a nice format. But, highlighting is fairly easy using grep, which is available in just about any front end you may be using (Perl, Shell, PHP, etc.). On Oct 25, 2004, at 8:37 PM, Jalil Feghhi wrote: In the MySQL documentation, it says that: Boolean full-text searches have these characteristics: * They do not use the 50% threshold. * They do not automatically sort rows in order of decreasing relevance. You can see this from the preceding query result: The row with the highest relevance is the one that contains ``MySQL'' twice, but it is listed last, not first. * They can work even without a FULLTEXT index, although this would be slow. * The minimum and maximum word length full-text parameters apply. * The stopword list applies. I had two questions: 1. How are the returned rows sorted in the boolean full-text searches? 2. Is there any way to get more information (other than the score) from MySQL? For example, can we find out the location of matches? Regards, -Jalil -- 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] -- 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: Full-Text Search
1. The rows are sorted in the same order as any other query that doesn't have and order by specified, which means usually in the order they were entered. 2. When you mean location of matches, do you mean something like highlighting? That's the responsibility of the interface, not the database. Remember, MySQL is really just a database engine. It will find your data fast, but it's up to you to present it in a nice format. But, highlighting is fairly easy using grep, which is available in just about any front end you may be using (Perl, Shell, PHP, etc.). On Oct 25, 2004, at 8:37 PM, Jalil Feghhi wrote: In the MySQL documentation, it says that: Boolean full-text searches have these characteristics: * They do not use the 50% threshold. * They do not automatically sort rows in order of decreasing relevance. You can see this from the preceding query result: The row with the highest relevance is the one that contains ``MySQL'' twice, but it is listed last, not first. * They can work even without a FULLTEXT index, although this would be slow. * The minimum and maximum word length full-text parameters apply. * The stopword list applies. I had two questions: 1. How are the returned rows sorted in the boolean full-text searches? 2. Is there any way to get more information (other than the score) from MySQL? For example, can we find out the location of matches? Regards, -Jalil -- 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: Full-Text Search
Brent, Thanks for the reply. 1. Is there any way to sort the boolean full-text search results in orther of relevance as in non-bolean mode? I think it is very important. 2. Yes, that is what I meant. You are right. I look into grep. Thanks, -Jalil -Original Message- From: Brent Baisley [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 26, 2004 6:30 AM To: Jalil Feghhi Cc: [EMAIL PROTECTED] Subject: Re: Full-Text Search 1. The rows are sorted in the same order as any other query that doesn't have and order by specified, which means usually in the order they were entered. 2. When you mean location of matches, do you mean something like highlighting? That's the responsibility of the interface, not the database. Remember, MySQL is really just a database engine. It will find your data fast, but it's up to you to present it in a nice format. But, highlighting is fairly easy using grep, which is available in just about any front end you may be using (Perl, Shell, PHP, etc.). On Oct 25, 2004, at 8:37 PM, Jalil Feghhi wrote: In the MySQL documentation, it says that: Boolean full-text searches have these characteristics: * They do not use the 50% threshold. * They do not automatically sort rows in order of decreasing relevance. You can see this from the preceding query result: The row with the highest relevance is the one that contains ``MySQL'' twice, but it is listed last, not first. * They can work even without a FULLTEXT index, although this would be slow. * The minimum and maximum word length full-text parameters apply. * The stopword list applies. I had two questions: 1. How are the returned rows sorted in the boolean full-text searches? 2. Is there any way to get more information (other than the score) from MySQL? For example, can we find out the location of matches? Regards, -Jalil -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Full-Text Search
In the MySQL documentation, it says that: Boolean full-text searches have these characteristics: * They do not use the 50% threshold. * They do not automatically sort rows in order of decreasing relevance. You can see this from the preceding query result: The row with the highest relevance is the one that contains ``MySQL'' twice, but it is listed last, not first. * They can work even without a FULLTEXT index, although this would be slow. * The minimum and maximum word length full-text parameters apply. * The stopword list applies. I had two questions: 1. How are the returned rows sorted in the boolean full-text searches? 2. Is there any way to get more information (other than the score) from MySQL? For example, can we find out the location of matches? Regards, -Jalil
Re: full text search question
Laura did this work... inquiring minds want to know :)
On Wed, 29 Sep 2004 13:36:40 -0400, Wesley Furgiuele
[EMAIL PROTECTED] wrote:
Laura:
Perhaps the - is acting like a Boolean operator. What if you put
double quotes around your search phrase:
SELECT * FROM metadata WHERE MATCH( type ) AGAINST ( '+XY-11443' IN
BOOLEAN MODE );
Wes
On Wed, 29 Sep 2004 13:22:54 -0400, Laura Scott [EMAIL PROTECTED] wrote:
Hello,
I have a questions with limitations/restrictions that are around for
full text search.
I have a field with data like XY-11443;. and I need to find the
record. The original developer was using full text search and says that
all was working before the task switched hands.
The basic query is
select * from metadata where match(type) against ('+XY-11443' in boolean
mode);
This query spins through all of my records and gives no results.
However, if I remove the XY- and just do ('+11443' in boolean mode) I
get an immediate and correct result.
I believe there is something going on with the '-' in the string that is
causing trouble - like maybe a stop word or something - but can't find
exactly what is going on and more importantly HOW TO FIX IT
Any help would be awesome!
Laura
--
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: full text search question
GH schrieb: Laura did this work... inquiring minds want to know :) Laura: Perhaps the - is acting like a Boolean operator. What if you put double quotes around your search phrase: SELECT * FROM metadata WHERE MATCH( type ) AGAINST ( '+XY-11443' IN BOOLEAN MODE ); Or.. the - is possibly supposed to be escaped? Let's take a look at the documentation ;) Bye Fred -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
full text search question
Hello,
I have a questions with limitations/restrictions that are around for
full text search.
I have a field with data like XY-11443;. and I need to find the
record. The original developer was using full text search and says that
all was working before the task switched hands.
The basic query is
select * from metadata where match(type) against ('+XY-11443' in boolean
mode);
This query spins through all of my records and gives no results.
However, if I remove the XY- and just do ('+11443' in boolean mode) I
get an immediate and correct result.
I believe there is something going on with the '-' in the string that is
causing trouble - like maybe a stop word or something - but can't find
exactly what is going on and more importantly HOW TO FIX IT
Any help would be awesome!
Laura
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: full text search question
Laura:
Perhaps the - is acting like a Boolean operator. What if you put
double quotes around your search phrase:
SELECT * FROM metadata WHERE MATCH( type ) AGAINST ( '+XY-11443' IN
BOOLEAN MODE );
Wes
On Wed, 29 Sep 2004 13:22:54 -0400, Laura Scott [EMAIL PROTECTED] wrote:
Hello,
I have a questions with limitations/restrictions that are around for
full text search.
I have a field with data like XY-11443;. and I need to find the
record. The original developer was using full text search and says that
all was working before the task switched hands.
The basic query is
select * from metadata where match(type) against ('+XY-11443' in boolean
mode);
This query spins through all of my records and gives no results.
However, if I remove the XY- and just do ('+11443' in boolean mode) I
get an immediate and correct result.
I believe there is something going on with the '-' in the string that is
causing trouble - like maybe a stop word or something - but can't find
exactly what is going on and more importantly HOW TO FIX IT
Any help would be awesome!
Laura
--
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]
Full Text Search Score calculations.
Hello everyone, I recently decided to perform an experiment. I was using Java to do string comparison on sometimes large data sets (this would sometimes take days). A friend of mine at work suggested that I use MySQL's full text searching. I quickly made the change over and I've found that it is great, extremely fast and I can see that the scoring is working perfectly. Well, almost perfectly. My problem is that I find the scores hard to read. When comparing these two strings 5600 10th Ave and 5600 10th Ave I get a score of 5.40898323059082. When I compare these two strings Greenacres Grand Slam and Greenacres Grand Slam, I get a score of 9.09278202056885, which I assume to be a perfect match. However, these scores are hard to program for. I need the scores to be in a percentage form (0-100, 0-1.0, etc...). I read through the documentation and I found Zero relevance means no similarity. Relevance is computed based on the number of words in the row, the number of unique words in that row, the total number of words in the collection, and the number of documents (rows) that contain a particular word. I figure I can use this to take the returned score and calculate a percentage, but I don't know how. Does anyone know of a simple way to do this? Is there a function provided for full text searching that I am missing? If not, does anyone know of a formula I can use to get the percentage of the matches? Thanks for any help anyone can provide. Sincerely, Eric Berry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Full text search problem
Hi
I have a fulltext search on a dbase for lost pets.
My problem is the following:
I have dog in the database called May May which doesnt show up in the
search results. A dog called Doggy Doggy does show up however. I guess
the problem is that MySql sees May May as being a date or something
and doesnt do a text compare.
Here is my query, from php.
$query_results = sprintf(SELECT * FROM dogslost WHERE MATCH
(`name`,`colour`,`gender`,`breed`,`location`,`description`) AGAINST
('%s' IN BOOLEAN MODE), $crit_results);
any ideas?
Regards
Pieter
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Full text search problem
Pieter,
I think FTS minimum WORD size is 4 characters - you may to be searching
with 3 on 'May May'.
Not having ever used FTS; I believe you can adjust it to count 3-character
words by changing the configuration, but I'm not sure where - and it would
then need re-indexing, if I'm not mistaken.
Hope that helps
Terry
--Original Message-
Hi
I have a fulltext search on a dbase for lost pets.
My problem is the following:
I have dog in the database called May May which doesnt show up in the
search results. A dog called Doggy Doggy does show up however. I
guess the problem is that MySql sees May May as being a date or
something and doesnt do a text compare.
Here is my query, from php.
$query_results = sprintf(SELECT * FROM dogslost WHERE MATCH
(`name`,`colour`,`gender`,`breed`,`location`,`description`) AGAINST
('%s' IN BOOLEAN MODE), $crit_results);
any ideas?
Regards
Pieter
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Full text search problem
Hi Pieter,
That's because may is a stopword in MySQL's full-text indexing, by
default (like can, the, etc). You can define your own stopword file
with the ft_stopword_file variable. And you can find the default, built-in
list of stopwords in the file myisam/ft_static.c of the source
distribution.
Hope that helps.
(Oh, also what Terry said in his reply!)
Matt
- Original Message -
From: Pieter Botha
Sent: Monday, June 21, 2004 3:54 AM
Subject: Full text search problem
Hi
I have a fulltext search on a dbase for lost pets.
My problem is the following:
I have dog in the database called May May which doesnt show up in the
search results. A dog called Doggy Doggy does show up however. I guess
the problem is that MySql sees May May as being a date or something
and doesnt do a text compare.
Here is my query, from php.
$query_results = sprintf(SELECT * FROM dogslost WHERE MATCH
(`name`,`colour`,`gender`,`breed`,`location`,`description`) AGAINST
('%s' IN BOOLEAN MODE), $crit_results);
any ideas?
Regards
Pieter
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Full text search problem
Good morning. Not knowing too much about PHP it looks like you are
searching for
`name`,`colour`,`gender`,`breed`,`location`,`description`
Where there is a whitespace in the name.
Could you use,
$query_results = sprintf(SELECT * FROM dogslost WHERE
`name` LIKE '% %', $crit_results);
God Bless
Paul C. McNeil
Developer in Java, MS-SQL, MySQL, and web technologies.
GOD BLESS AMERICA!
To God Be The Glory!
-Original Message-
From: Terry Riley [mailto:[EMAIL PROTECTED]
Sent: Monday, June 21, 2004 5:08 AM
To: [EMAIL PROTECTED]
Subject: Re: Full text search problem
Pieter,
I think FTS minimum WORD size is 4 characters - you may to be searching
with 3 on 'May May'.
Not having ever used FTS; I believe you can adjust it to count 3-character
words by changing the configuration, but I'm not sure where - and it would
then need re-indexing, if I'm not mistaken.
Hope that helps
Terry
--Original Message-
Hi
I have a fulltext search on a dbase for lost pets.
My problem is the following:
I have dog in the database called May May which doesnt show up in the
search results. A dog called Doggy Doggy does show up however. I
guess the problem is that MySql sees May May as being a date or
something and doesnt do a text compare.
Here is my query, from php.
$query_results = sprintf(SELECT * FROM dogslost WHERE MATCH
(`name`,`colour`,`gender`,`breed`,`location`,`description`) AGAINST
('%s' IN BOOLEAN MODE), $crit_results);
any ideas?
Regards
Pieter
--
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: Full-Text Search on MERGE Tables
Lorderon [EMAIL PROTECTED] wrote: Is it possible to define MERGE table on several tables with full-text indexes? Yes, but without specification of FULLTEXT index in the MERGE table. And to make a select on the MERGE table with MATCH AGAINST? You can permorm boolean full-text search that can work without FULLTEXT index. -- 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Full-Text Search on MERGE Tables
Hello All, Is it possible to define MERGE table on several tables with full-text indexes? And to make a select on the MERGE table with MATCH AGAINST? thanks, -Lorderon. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How boolean full-text search finds matches?
Hi Sergei! Thanks for replying again. I hope I'm not wasting too much of your time with my questions! :-) More below... - Original Message - From: Sergei Golubchik Sent: Thursday, December 18, 2003 7:17 AM Subject: Re: How boolean full-text search finds matches? Hi! On Dec 17, Matt W wrote: Hi, Just have a couple more full-text search inquiries here. :-) I'm not exactly clear on how matching rows are found when searching for 2 or more required words: '+word1 +word2'. I understand that it can't currently know which word occurs less, so that it can be searched first -- this optimization will come with 4.1's 2-level indexes. :-) I just want to know, when it finds a match for whichever word is tried first, how does it check if the other required word(s) are present in the same row? Say that word1 and word2 are each present in 100,000 rows. 1) Surely it doesn't check the 100,000 entries for word2 for EACH word1 match to see if they're in the same row, does it? No it does not :) Good! 2) It *seems* the best way would be to do a lookup for (word2 + rowid for word1) and see if there's a match. Is this what's done? I'm not sure it's possible though with the way the index is structured... it is possible, but it is only sensible if word1 is much more rare than word1. This could be done with 2-level indexes :) I assume that should say if word2 is much more rare than word1. I guess that's because it would need too many [random?] index lookups otherwise? 3) Or, and I'm thinking *maybe* this is how it's done from what I've heard, does it get all the matches for word1, then for word2, and then intersect them to find ones which are present in the same row? If so, how will the 2-level index optimization change things? Will it do #2? Yes to both questions, without the word then. First, one match is found for each word. Then read_next is called for the word with the lowest rowid, etc. Not completely clear on this. :-) I get that one match is found for each word... then whichever word occured first in the table (lowest rowid) is... what? :-/ Oh, wait, I get what you mean! ;-) You're saying that read_next is called for the word with the lowest rowid until you see if the rowid matches the rowid from the other word(s)? Then if the rowid gets greater than what you're looking for, you know that there's no matching row? (Since you say that each word is sorted by rowid -- see below about that, though.) Then I'm not sure what happens to find the next matching row. Find a match again for each word starting after the last found rowid? I'm not familiar enough with the MySQL code (or C) to understand what's going on in ft_boolean_search.c. :-( The advantage is that matches are found and returned earlier - a user don't have to wait for the index scan to complete. Also LIMIT, if used, cuts off more work, that is LIMIT is more effective. Right, I know that LIMIT helps -- as long as there's no ORDER BY, etc. that needs to find all rows to sort. :-) That brings me to the main reason for asking these questions: for searching on my forum system. There could be 5-10+ million posts, which would put upwards of 250+ million entries in the full-text index. I'll probably use a LIMIT of 10-20,000 (w/o ORDER BY) to prevent searches from taking too long (and returning way too many matches!). However, with that many posts, I think it's possible that a search could match 100k+ posts. Then the search would have to be narrowed down to a particular forum or date range. If I add AND forumid=123 etc. to the WHERE clause, that will make the search hit the data file for who-knows-how-many posts that aren't in the desired forum -- thus scanning more data rows than specified by LIMIT. But you told list member Steven Roussey one time that he could add the forum id to a character column and include it in the full-text index. I thought I'd try that too -- along with some text for the month and year for date range searches. Although now I'm not sure that's a good idea, because what if there's 1 million posts in a single forum (is it bad to have the same word for a forum id in 1 million rows??) and a search is done that would actually find LIMIT rows *across all forums*? If I include the forum id in the search, the current full-text code will look at all 1M words for that forum id. Much slower than just doing the search and manually checking forum id. :-( Of course, if the situation is reversed (common search in a *small* forum), this method would be faster than manually checking. Any other ideas for improving performance when searching a small subset of the full-text index? Too bad we can't include numeric columns in the full-text index (which would work as usual in the WHERE, not in MATCH ()). But when one word is much more common than the second one, it is better to do #2, and it's what I'll probably do. Yeah, like for my example. :-) Next
