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
[email protected]


[email protected] 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
> ----------------
> [email protected]
> ----------------
> 
> -----------------------------
> ** END OF ORIGINAL MESSAGE **
> *****************************
> 
> 
> 

-- 
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe:    http://lists.mysql.com/[email protected]