469,282 Members | 1,905 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,282 developers. It's quick & easy.

Does MySql have any built in search functionality

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
Jul 17 '05 #1
6 1430
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
Jul 17 '05 #2
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
Jul 17 '05 #3
lk******@geocities.com (lawrence) wrote in message
news:<da**************************@posting.google. 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
Jul 17 '05 #4
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
Jul 17 '05 #5
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.
Jul 17 '05 #6
nc@iname.com (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_array 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?
Jul 17 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by fabriZio | last post: by
reply views Thread by W i l l | last post: by
reply views Thread by Henry Hank | last post: by
13 posts views Thread by wideangle | last post: by
113 posts views Thread by John Nagle | last post: by
6 posts views Thread by Cirene | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.