Closed Thread Icon

Topic awaiting preservation: Sorting a MySQL multi-table SELECT query by relevancy (Page 1 of 1) Pages that link to <a href="http://ozoneasylum.com/backlink?for=24538" title="Pages that link to Topic awaiting preservation: Sorting a MySQL multi-table SELECT query by relevancy (Page 1 of 1)" rel="nofollow" >Topic awaiting preservation: Sorting a MySQL multi-table SELECT query by relevancy <span class="small">(Page 1 of 1)</span>\

 
zavaboy
Bipolar (III) Inmate

From: f(x)
Insane since: Jun 2004

posted posted 12-31-2004 04:13

How can I get this MySQL query to sort by relevancy?

code:
SELECT items . * , stores.name AS storen
FROM items, stores
WHERE items.store = stores.id
AND MATCH (
items.name
)
AGAINST (
'+blox'
IN BOOLEAN
MODE
)
AND UCASE( items.name ) LIKE UCASE( '%ent%' )
ORDER BY name ASC
LIMIT 0 , 25



zavaboy
Bipolar (III) Inmate

From: f(x)
Insane since: Jun 2004

posted posted 12-31-2004 22:06

Is it even possible with that MySQL query?

hyperbole
Paranoid (IV) Inmate

From: Madison, Indiana, USA
Insane since: Aug 2000

posted posted 01-01-2005 17:22

How are you ranking relevancy? Unless you have a column named 'relevancy' I don't think you can do it with SQL.


.

-- not necessarily stoned... just beautiful.

zavaboy
Bipolar (III) Inmate

From: f(x)
Insane since: Jun 2004

posted posted 01-02-2005 01:50

Well, I almost have what I want.
For example, someone searches for red shoes:

code:
SELECT _test_items . * , _test_stores.name AS storen,
MATCH (
_test_items.name
)
AGAINST (
'red shoes'
) AS relevancy
FROM _test_items, _test_stores
WHERE _test_items.store = _test_stores.id
AND MATCH (
_test_items.name
)
AGAINST (
'+shoes'
IN BOOLEAN
MODE
)
AND UCASE( _test_items.name ) LIKE UCASE( '%red%' )
ORDER BY relevancy DESC
LIMIT 0 , 25


The only problem is that it's sorting only by the relevancy of shoes because it's more than 3 characters long. How can I get it to sort by both the relevancy of red and shoes?

poi
Paranoid (IV) Inmate

From: France
Insane since: Jun 2002

posted posted 01-02-2005 02:10

I've never used the MATCH clause in SQL, by my first guess is that if the MATCH clause fed by a single word, whatever its size, works then you can build a query like :

SELECT *, MATCH( name ) AGAINST( word1 ) as relevancy1, MATCH( name ) AGAINST( word2 ) as relevancy2 FROM table ORDER BY relevancy1+relevancy2 DESC LIMIT 0,25

zavaboy
Bipolar (III) Inmate

From: f(x)
Insane since: Jun 2004

posted posted 01-02-2005 05:58

I don't know if you're seeing what I'm getting at. MATCH ignores words 3 characters or less, this leaves a issue with words like red. For those words, I use LIKE which recognizes the small words.

If I use the method poi suggested with a LIKE rather than a MATCH for relevancy2, it returns this error:

quote:
#1054 - Unknown column 'relevancy1' in 'order clause'



Tyberius Prime
Paranoid (IV) Mad Scientist with Finglongers

From: Germany
Insane since: Sep 2001

posted posted 01-02-2005 17:24

you'll have no luck with 3 word search terms and mysql - unless you change the source and compile yourself.

There is, of course, reason to this, and it's mostly efficency. A full text search with like %something% is very much slower
than a match() search against a full text index (d'oh, it has to search through much more text). Easily compared,
try the backlink to any page here, and then try the search. The backlink has to use the like, since it's
looking for strings containing characters mysql breaks up (=,/).
Mysql ignores the <3 letter words for efficencys sake - there are, last time I looked, only a few of these words in the english language that were 'meaningful' (ie. meaninful to search for) ('god' springs to mind. You'de never search for 'red' alone in most contexts), but plenty of 'filling' words
like and, or, the, my or of.

So long,
->Tyberius Prime

PS: You can sort against a match() clause by repeating it in the order by clause, I believe. Look it up at mysql->match.

zavaboy
Bipolar (III) Inmate

From: f(x)
Insane since: Jun 2004

posted posted 01-02-2005 19:34

Well, since there's no way around this problem I'm having, I'll have PHP look at the search query given and sort be relevancy whenever possible. The word red is a heavily used word in searches preformed. About 73 (5.7%) of all 1270 searches thus far contained the word red, 27 (2.1%) searches were red alone. So it's not a rare sight for what I need it for.

Thanks for all the help!

Tyberius Prime
Paranoid (IV) Mad Scientist with Finglongers

From: Germany
Insane since: Sep 2001

posted posted 01-02-2005 20:42

well, I just found out, you can actually reconfigure mysql (4.0.0 +) to consider words <3 letters in your searches.

see http://dev.mysql.com/doc/mysql/en/Fulltext_Fine-tuning.html for the details if you're running your own mysql server.

so long,

->Tyberius Prime

zavaboy
Bipolar (III) Inmate

From: f(x)
Insane since: Jun 2004

posted posted 01-03-2005 11:29

Unfortunately, I don't have my own MySQL server, but that information may come in value when/if I get a dedicated server. Thanks TP!

« BackwardsOnwards »

Show Forum Drop Down Menu