mysql, full text search

[srisa]

Hello,
How can I perform full text search on two different fields for two different values. I mean search field1 for value 'value1' and field2 for 'value2'.
Is it something like

SELECT title, author FROM books WHERE MATCH(title) AGAINST('art of war') AND MATCH(author) AGAINST ('sun tzu');

Thank you.

[mrstuff68]

How about giving the following sql a try.

SELECT title, author FROM books WHERE title = 'art of war' AND author = 'sun tzu';

But I dont know why you are trying to get title and author returned to you since they are going to be art of war and sun tzu for everyone.

[srisa]

Well, the code posted was just as an example. Moreover, sql statement given by you wiill search for all the words in that particular sequence. What I want is to search for any of the words in any sequence.

[MaximilianMayrhofer]

You would have to pull the data from the field, split it into an array and then compare all the elements of the array with your keyword. You can't do that in one statement.

[srisa]

I thought full text search feature was to avoid all that hassle.

[si_the_geek]

It should.. and a query using Like (and wildcards) rather than = would do it.

The full text search is apparently faster if you have set it all up appropriately, but very few people seem to use it. I can only remember one thread, and in that case I don't think it worked out (edit: here it is, including a link to the MySQL documentation).

[srisa]

Well, I might as well state the problem at hand. I have a table books with the fields - id, title, author, isbn, price. I want the table to be searchable on title and author fields. We have to take into consideration that different people spell words differently, so that should be factored into the solution. What would be best way to go about this?
I have the manual with me, but my knowledge about databases and optimization etc is very very limited. I am not sure I will get it right.
Right now there is only test data in the table with about 20 records. So, testing for performance might not be feasible.
Thanks for all your inputs.

[si_the_geek]

If performance matters to you, I think that Match (but as I don't use MySQL, am not sure) would be the best way to go.

A good thing about Match is that it will return records in order of relevance, so that will certainly help (that step alone can take 4+ queries, which would be a bit slower!).

As far as people spelling words differently, that may not be something that you can achieve with a Match (or Like), you will probably need to use Soundex instead - but as it will hit performance hard I'd recommend against using it within the search (but you could do what popular search engines do, and have "did you mean" underneath), unless you re-design the database to have a separate keywords table (and triggers to fill/update it when the original data changes). edit: perhaps fulltext with query expansion might work?


Your database design does not seem to be ideal - as presumably each author (on average) will have written more than one book. As that is likely to be the case, I would recommend adding a separate Authors table (for help on how to set that up and use it, see the Normalisation article in the Design section of our Database Development FAQs). Simply doing that will make this kind of query noticeably faster - assuming an average of just 2 books per author, it will be nearly twice as fast for searching the authors (but an author&title search will still search each title, so would only be about 25% faster).

[srisa]

I will give the match thing a try.
About design: The data - author, title, isbn etc will be filled in by the users who are putting up their books for sale. So, to have an author's table, when the fields are being filled in, all existing authors have to be listed and if the required one doesn't exist, a new entry should be made into the author's table. I am not sure if that is a good idea. Or is it a good idea? Is there a better way of dealing with this situation?

[si_the_geek]

It is definitely a good idea.

It will take a little more work for you to check/add authors, but will be quicker for your queries, and to do things like showing a list of existing authors - which I would always do, as it saves the user some typing, and also ensures fewer data errors caused by bad spelling or typos.