Query Optimization on Searching

Question

I am trying to create a search query for developing a school library that will retrieve data from the database with a low cost. Inside my table are callNumber,author,title and year. I want to search from callNumber, author and title.

$sqlCommand = "SELECT book_id, author, title 
               FROM book 
               WHERE MATCH(callNumber, author, title) 
               AGAINST('$search' IN BOOLEAN MODE)";

I used the query above and the return data is not reliable. Any suggestion or recommendation in optimizing this query, whatever. Thanks

Answer 1

Results of MATCH(...) AGAINST queries are listed in order of relevance by default.

To list the relevance of the matches, try the following SQL:

SELECT book_id, author, title, MATCH(callNumber, author, title) 
               AGAINST('$search' IN BOOLEAN MODE) as relevance
               FROM book 
               WHERE MATCH(callNumber, author, title) 
               AGAINST('$search' IN BOOLEAN MODE)

If the relevance numbers are not what you expected you should consider whether your test data is relevant. There are some caveats, like:

• Short words are ignored (I think the cut-off is at 4 characters).

• Certain words are ignored (after, other etc).

• Common words are ignored (present in at least half the records)

I found the following page useful.