Does MySql have any built in search functionality

[lawrence]

I've spent some time going over this page:

http://dev.mysql.com/doc/mysql/en/Da...ipulation.html

And so far it seems the answer is no. So I thought I'd ask here. Does
anyone know of any built-in search algorithms with MySql?

I'm looking for a way to return the results of my last SELECT
statement (which uses LIKE) based on the number of hits the LIKE came
up with. In other words, I want something like this:

SELECT * FROM mainContent WHERE mainText LIKE '%$matchThis%' ORDER BY
number of matches per entry DESC

[Jerry Gitomer]

On Thu, 29 Apr 2004 19:29:50 -0700, lawrence wrote:

I've spent some time going over this page:

http://dev.mysql.com/doc/mysql/en/Da...ipulation.html

And so far it seems the answer is no. So I thought I'd ask here. Does
anyone know of any built-in search algorithms with MySql?

I'm looking for a way to return the results of my last SELECT statement
(which uses LIKE) based on the number of hits the LIKE came up with. In
other words, I want something like this:

SELECT * FROM mainContent WHERE mainText LIKE '%$matchThis%' FROM
mainContent ORDER BY number of matches per entry DESC

You are trying to marry two mutually exclusive conditions: The use of an
RDBMS and "record" sequences. The order in which data is retrieved is
random and inconsistent. In other words if you run a query more than once
there is no guarantee that the results will be in the same order each
time you run it or that the behavior will be consistent from release to
release of an RDBMS.

Now, if you want to know how many rows in the table match your like use
count. e.g. SELECT COUNT(*) FROM mainContent WHERE mainText LIKE
'%$matchThis%';

HTH
Jerry

[Jerry Gitomer]

On Thu, 29 Apr 2004 23:44:27 -0400, Jerry Gitomer wrote:

On Thu, 29 Apr 2004 19:29:50 -0700, lawrence wrote:

I've spent some time going over this page:

http://dev.mysql.com/doc/mysql/en/Da...ipulation.html

And so far it seems the answer is no. So I thought I'd ask here. Does
anyone know of any built-in search algorithms with MySql?

I'm looking for a way to return the results of my last SELECT statement
(which uses LIKE) based on the number of hits the LIKE came up with. In
other words, I want something like this:

SELECT * FROM mainContent WHERE mainText LIKE '%$matchThis%' FROM
mainContent ORDER BY number of matches per entry DESC

You are trying to marry two mutually exclusive conditions: The use of an
RDBMS and "record" sequences. The order in which data is retrieved is
random and inconsistent. In other words if you run a query more than once
there is no guarantee that the results will be in the same order each time
you run it or that the behavior will be consistent from release to release
of an RDBMS.

Now, if you want to know how many rows in the table match your like use
count. e.g. SELECT COUNT(*) FROM mainContent WHERE mainText LIKE
'%$matchThis%';

HTH
Jerry

I had another thought about your problem. If you have sufficient memory
available you could read the query result into an array and then use PHP
and/or Pear DB to manipulate the array.

HTH
Jerry

[Nikolai Chuvakhin]

lk******@geocit ies.com (lawrence) wrote in message
news:<da******* *************** ****@posting.go ogle.com>...

Does anyone know of any built-in search algorithms with MySql?

Yes:

http://dev.mysql.com/doc/mysql/en/Fulltext_Search.html

Cheers,
NC

[Ian Taylor]

lawrence wrote:

I've spent some time going over this page:

http://dev.mysql.com/doc/mysql/en/Da...ipulation.html

And so far it seems the answer is no. So I thought I'd ask here. Does
anyone know of any built-in search algorithms with MySql?

I'm looking for a way to return the results of my last SELECT
statement (which uses LIKE) based on the number of hits the LIKE came
up with. In other words, I want something like this:

SELECT * FROM mainContent WHERE mainText LIKE '%$matchThis%' ORDER BY
number of matches per entry DESC

Have you seen the full text search functions page?

http://dev.mysql.com/doc/mysql/en/Fulltext_Search.html

[lawrence]

Jerry Gitomer <jg******@erols .com> wrote in message

I had another thought about your problem. If you have sufficient memory
available you could read the query result into an array and then use PHP
and/or Pear DB to manipulate the array.

This is exactly what I'm trying to avoid. I already have written the
PHP code to this, but it is cumbersome. I was hoping to elimnate it
and replace it with some built-in MySql function.

I do take your point about inconsistent results across RDBMS, but I
don't see that as a huge problem - merely something the end user needs
to live with when they choose their RDBMS.

[lawrence]

[e-mail] (Nikolai Chuvakhin) wrote in message

Does anyone know of any built-in search algorithms with MySql?

http://dev.mysql.com/doc/mysql/en/Fulltext_Search.html

Fantastic, thank you. Do you know under what conditions I can expect
to be hit worst with the slow down they warn about due to FULL TEXT
indexing? On a large site how would this be done? Should I create a
temporary table for the searches? A site serving up a large number of
web pages might grind to a slow pace if I hit too much of it with FULL
TEXT indexing. Any advice?

Also, the example they give doesn't show how to get hold of the
relevance number. They describe is as a non-negative floating point
number. If I want to show it to the user, how do I get it? I mean, if
I want to get it into my PHP code, by what index or handle to I get
it? mysql_fetch_arr ay set to MYSQL_ASSOC will return what I specify of
table fields, but the relevance is not a table field. How do I get it?