mySQL FULL-TEXT search

Hello experts,

I have a problem with full text search. Here is my situation:

In DB I have LOG records in "maillog" table,
with FULLTEXT index on "log" row.

When I do a search, I do:

SELECT *
FROM maillog
WHERE MATCH (log) AGAINST ('some_string' IN BOOLEAN MODE)

This is OK. Query is fast (0.15 sec on 500.000 records).
BUT when I want to narrow the search and include additional search
string, I have problems:

SELECT *
FROM maillog
WHERE MATCH (log) AGAINST ('some_string +another_string' IN BOOLEAN MODE)

The number of returned results are the same in both queries. Only
relevance changes.
If I do double query, I mean, first search for one string, and then
for another string inside this string, the query takes 20 seconds,
which is too long.

Any idea how to do full-text search for 2 or more search strings with
AND as boolean operator?



--
Best regards,
Andrej
----------------
labsy@seznam.org
----------------



--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: mySQL FULL-TEXT search

Additionally,

I thought that I solved my problem with the following sollution:

SELECT *
FROM maillog
WHERE MATCH (log) AGAINST ('some_string')
AND log LIKE '%another_string%'

BUT somehow this query returns results which include ANY of strings,
like there was "OR" operator.

How to do query with
MATCH (log) AGAINST (...)
having in mind that I do NOT search only for whole words, but also
within PART of words (like, search for "user" in "name.user@domain")




***************************
*** MY ORIGINAL MESSAGE ***
---------------------------
Hello experts,

I have a problem with full text search. Here is my situation:

In DB I have LOG records in "maillog" table,
with FULLTEXT index on "log" row.

When I do a search, I do:

SELECT *
FROM maillog
WHERE MATCH (log) AGAINST ('some_string' IN BOOLEAN MODE)

This is OK. Query is fast (0.15 sec on 500.000 records).
BUT when I want to narrow the search and include additional search
string, I have problems:

SELECT *
FROM maillog
WHERE MATCH (log) AGAINST ('some_string +another_string' IN BOOLEAN MODE)

The number of returned results are the same in both queries. Only
relevance changes.
If I do double query, I mean, first search for one string, and then
for another string inside this string, the query takes 20 seconds,
which is too long.

Any idea how to do full-text search for 2 or more search strings with
AND as boolean operator?



--
Best regards,
Andrej
----------------
labsy@seznam.org
----------------

-----------------------------
** END OF ORIGINAL MESSAGE **
*****************************



--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: mySQL FULL-TEXT search

Andrej,

Try this:

SELECT *
FROM maillog
WHERE MATCH (log) AGAINST ('some_string')
AND MATCH (log) AGAINST 'another_string'

I have a table with about 10,000 rows and got a surprising result when I
tried this. Using only the first MATCH, the query returned 166 rows in
2.53 seconds. Adding the new word to the AGAINST part of the query took
8 seconds to return over 100 rows (I did't count). Using a query as
above return 70 rows in .53 seconds.

Randy Clamons
Systems Programming
randy@novaspace.com


labsy@seznam.org wrote:
> Additionally,
>
> I thought that I solved my problem with the following sollution:
>
> SELECT *
> FROM maillog
> WHERE MATCH (log) AGAINST ('some_string')
> AND log LIKE '%another_string%'
>
> BUT somehow this query returns results which include ANY of strings,
> like there was "OR" operator.
>
> How to do query with
> MATCH (log) AGAINST (...)
> having in mind that I do NOT search only for whole words, but also
> within PART of words (like, search for "user" in "name.user@domain")
>
>
>
>
> ***************************
> *** MY ORIGINAL MESSAGE ***
> ---------------------------
> Hello experts,
>
> I have a problem with full text search. Here is my situation:
>
> In DB I have LOG records in "maillog" table,
> with FULLTEXT index on "log" row.
>
> When I do a search, I do:
>
> SELECT *
> FROM maillog
> WHERE MATCH (log) AGAINST ('some_string' IN BOOLEAN MODE)
>
> This is OK. Query is fast (0.15 sec on 500.000 records).
> BUT when I want to narrow the search and include additional search
> string, I have problems:
>
> SELECT *
> FROM maillog
> WHERE MATCH (log) AGAINST ('some_string +another_string' IN BOOLEAN MODE)
>
> The number of returned results are the same in both queries. Only
> relevance changes.
> If I do double query, I mean, first search for one string, and then
> for another string inside this string, the query takes 20 seconds,
> which is too long.
>
> Any idea how to do full-text search for 2 or more search strings with
> AND as boolean operator?
>
>
>
> --
> Best regards,
> Andrej
> ----------------
> labsy@seznam.org
> ----------------
>
> -----------------------------
> ** END OF ORIGINAL MESSAGE **
> *****************************
>
>
>

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org