Search using single-character words (MySQL)

Question

I have a Books table in my MySQL database that has the columns Title (varchar (255)) and Edition (varchar (20)). Examples of such values ​​are “Introduction to Microeconomics” and “4”.

I want users to search for books based on Title and Edition. So, for example, they can enter Microeconomics 4, and this will bring the correct result. My question is how should I install this on the database side.

I was told that a FULLTEXT search is usually a good way to do such things. However, since the edition sometimes represents only one character ("4"), a full text search must be configured to view individual characters (ft_min_word_len = 1). This, I heard, is very inefficient.

So how do I set up a search for this database?

UPDATE: I know CONCAT / LIKE can be used here. My question is: will it be too slow. There are hundreds of thousands of books in my book database, and many users will search for it.

Answer 1

For your application, where you are only interested in the title and release, I suspect that using index FULLTEXTc MATCH/AGAINSTand reducing it ft_min_word_lento 1 will not have much impact on performance (if you were data more detailed or written by the user, then I could hesitate).

- , REPAIR ft_min_word_len , .

, Sphinx. , , ( Sphinx) . , wordforms exceptions, , 4/four/4th/IV, .

Answer 2

1) .

2) ("") .

3)

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

[0] [1]

PHP-

<?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));
?>

$string_array [0] , , " 4"