Full Text Searches In MySQL

[Fat Lotus 0]

Before I begin, you must remember that the columns that you will be searching in must be a fulltext index

 

Let's start with the basics:

 

Just the relavant results:

(Searching for "Ducks"):

SELECT * FROM [i]tablename[/i] WHERE MATCH([i]coloumnname[/i],[i]columnname2[/i]) AGAINST ('Ducks');

But that just won't work. Will it? Let's try again, this time ordering by the relavancy.

SELECT * FROM [i]tablename[/i] WHERE MATCH([i]coloumnname[/i],[i]columnname2[/i]) AGAINST ('Ducks') ORDER BY MATCH([i]columnname[/i],[i]columnname2[/i]) AGAINST ('Ducks');

That's better!

[vizskywalker 0]

I'm a little unclear as to the difference that the

ORDER BY MATCH(columnname, columnname2) AGAINST('Ducks')

makes to the query.

First, if I understand this correctly, this query searches two columns, columnname and columnname2 for Ducks, for which they must be full text.

So does this order the results by appearance order in the database? And does the second part change it to order by how closely it matches Ducks? And how many times it matches Ducks?

~Viz

[Hercco 0]

It matches 'Ducks' infitite number of times and the results are sorted by relevance. Someone else has to help here with the order direction, is it decreasing relevance or increasing? Anyways basically this means that if you have a full text field with the word 'Ducks' appearing twice, it is of higher relevance than fields with only one occurance.

[black shadow 0]

I don't get it. what kind of search is this ? Search the mysql database or a tipe of search for a site /:P

[vizskywalker 0]

This searches the MySQL database for the word ducks in the two columns specified in all the text of the column so long as the columns are of type fulltext.~Viz