Searching with words one character long (MySQL)

Question

I have a table Books in my MySQL database which has the columns Title (varchar(255)) and Edition (varchar(20)). Example values for these are "Introduction to Microeconomics" and "4".

I want to let users search for Books based on Title and Edition. So, for example they could enter "Microeconomics 4" and it would get the proper result. My question is how I should set this up on the database side.

I've been told that FULLTEXT search is generally a good way to do things like this. However, because the edition is sometimes just a single character ("4"), full text search would have to be setup to look at individual characters (ft_min_word_len = 1).. This, I've heard, is very inefficient.

So, how should I setup searches of this database?

UPDATE: I'm aware the CONCAT/LIKE could be used here.. My question is whether it would be too slow. My Books database has hundreds of thousands of books and a lot of users are going to be searching it..

Answer 1

here are the steps for solution

1) read the search string from user.

2) make the string in to parts according to space(" ") between the words.

3) use following query for getting the result

SELECT * FROM books WHERE Title LIKE '%part[0]%' AND Edition LIKE '%part[1]%';

here part[0] and part[1] are separated words from the given word

the PHP code for the above could be

<?php 
     $string_array=explode(" ",$string); //$string is the value we are searching
     $select_query="SELECT * FROM books WHERE Title LIKE '%".$string_array[0]."%' AND Edition LIKE '%".$string_array[1]."%';";
     $result=mysql_fetch_array(mysql_query($select_query));
?>

for $string_array[0] it could be extended to get all the parts except last one which can be applied for the case "Introduction to Microeconomics 4"