Topic awaiting preservation: Sorting a MySQL multi-table SELECT query by relevancy (Page 1 of 1)

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

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

Is it even possible with that MySQL query?

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.

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

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?

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

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'

---

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.

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!

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

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!