I'm implementing an ingredient text search, for adding ingredients to a recipe. I've currently got a full text index on the ingredient name, which is stored in a single text field, like so:
"Sauce, tomato, lite, Heinz"
I've found that because there are a lot of ingredients with very similar names in the database, simply sorting by relevance doesn't work that well a lot of the time. So, I've found myself sorting by a bunch of my own rules of thumb, which probably duplicates a lot of the full-text search algorithm which spits out a numerical relevance. For instance (abridged):
ORDER BY
[ingredient name is exactly search term],
[ingredient name starts with search term],
[ingredient name starts with any word from the search and contains all search terms in some order],
[ingredient name contains all search terms in some order],
...and so on. Each of these is defined in the SELECT specification as an expression returning either 1 or 0, and so I order by those in sequential order.
I would love to hear suggestions for:
A better way to define complicated order-by criteria in one place, say perhaps in a view or stored procedure that you can pass just the search term to and get back a set of results without having to worry about how they're ordered?
A better tool for this than MySQL's fulltext engine -- perhaps if I was using Sphinx or something [which I've heard of but not used before], would I find some sort of complicated config option designed to solve problems like this?
Some google search terms which might turn up discussion on how to order text items within a specific domain like this? I haven't found much that's of use.
Thanks for reading!
