Using MATCH across multiple tables....

[nerhael]

I have a book table, and an author table.

When people search for something, I need it to intelligently look at the concatenation of the book name, and the author name to determine which are the best matches, so would like to use match....is there a way to do this? Could I create a view prior to searching...would that take forever? Really not sure what to do here.

Pete.

[raf]

Not sure i understood all that.

You wanna concatenate author and title. (Why ?) and then you wanna use match --> full text search.
So that would be something like

SELECT * FROM view WHERE MATCH (concat(author,title)) AGAINST ('searchstring')

where this 'view' is a (temporarely)table with info from the two tables you got now.
The full text search on a temporarely table will perform badly. Because you'd need to create the table and then create the fulltext index, before you can run the search.

I suppose you wanna do this because you think it's more userfriendly. But personally, i'm more for these searchform where you get these 'search by author', 'search by title', 'searh by keyword' option. I think its faster and easier. More intuitive.
+ Then you only need to search on 1 table.

[nerhael]

That would certainly be fine with 'me', however, pretty sure my employer will want the one field for all kind of approach for the opening page.

Is it possible to do a search on one field in a table, and get a relevance result with it? I could maybe just do two searches, one vs author, one vs book title, and merge and sort the results.

Are there more intelligent ways of matching something to account for little typos like doing 'ph' instead of 'f' or something like that. Don't want to always have to have an exact match.

[raf]

everything you ask is possible.

But there's a big misunderstanding about userfriendlyness. (I prefer filling in two fields to get one record back --> the book i need, instead of filling in one fields and get 20 records back where i need to sift through) But Dilbert rules, so ...


Is it possible to do a search on one field in a table, and get a relevance result with it? I could maybe just do two searches, one vs author, one vs book title, and merge and sort the results.

You could run two searches, yes --> but how will you know what's the author and whats the title ?
And merging the two two results isn't right, cause they don't have anything to do with eachother (the author's score will probably always be user cause there are fewer wordes and fewer records will be omitt. But on the other hand, authors with lot of entrys will probably be rated lower) I mean, there's almost no logical algorithm to compute such a score.
I think you could just as well just run a search with the LIKE operator to look up each word like a keyword.


Are there more intelligent ways of matching something to account for little typos like doing 'ph' instead of 'f' or something like that.

You could use regex
http://www.mysql.com/doc/en/Regexp.html
and try something with frequent typo's etc
But don't expect to much. I don't think you can do better then a spellingscheck (which isn't much for searches cause it mainly just looks up the wordt with the same first letters)

But the more flexability, the more records will be returned, and the more time the client wastes cause he needs to filter out his records.

There are better searchalorithms, but that fairly complex, and in most cases involves neural networks. So in any case, it would take a while before you get good searchresults.

Just my opinion.

[nerhael]

I'll prolly do a basic catchall like for now, and then try sneaking in the specific one later. And see what kind of upcry it causes.

To me, two fields with two different fields being searched makes sense, but to the user on the other side, it's one more thing to not read and misuse, and complain about.

Blarg.

Thanks again Raf.