1

I have a table which contains around a million record of vehicles. I want to implement search functionality so that user can make search on vehicle, unfortunately FULLTEXT column contains data like...

"Motor Car Fortuner-GJ00XX1234-Toyota"
"Motor Car Fortuner-GJ00XX04567-Toyota"
etc.....

This means the particular record is for "motor car" model "fortuner" with registration number "GJ00XX1234" and company name "toyota".

Users trying to search "fortuner 1234" OR "1234" as generally user doesn't remember full registration number. As a result MySql returns all records with "fortuner" but it cannot search "1234". However searching "GJ00XX1234" works.

I know using "LIKE '%1234%'" query works but there's a performance issue so I have to achieve this using FULLTEXT only.

Can someone guide me how to achieve it without modifying data? Thanks.

Mohit Mehta
  • 1,283
  • 12
  • 21

2 Answers2

1

You cannot achieve this by using FULLTEXT search without separating / adding to the index. There is a boolean full-text search mode that allows to append a joker * but afaik it's not possible to prepend the joker due to the way indexes work in MySQL.

For maintaining the performance benefits of the fulltext search, probably the best idea would be to include a second column that contains the numbers in the fulltext index or set a new column only for searching, that contains something like motor car fortuner gj00xx1234 toyota 1234.

If you prepend % eg. LIKE %1234% or REGEXP it will hit your performance, because indexes can not be utilized - resulting in full table scans. Depends on data size how intense.

Community
  • 1
  • 1
bobble bubble
  • 16,888
  • 3
  • 27
  • 46
0

Split the search string by spaces and add in query like this

WHERE data LIKE '%fortuner%' OR data LIKE '%1234%'

or simpler terms

WHERE data REGEXP 'fortuner|1234'

in the latter case, your PHP code can be:

$searchString = "fortuner 1234";
$search = str_replace(' ','|','fortuner 1234');

UPDATE:

For FULLTEXT:

WHERE          
MATCH (data) 
AGAINST ('+fortuner +1234' IN BOOLEAN MODE)

Here is the manual

Note

In implementing this feature, MySQL uses what is sometimes referred to as implied Boolean logic, in which

  • stands for AND

  • stands for NOT

[no operator] implies OR

Thamilhan
  • 13,040
  • 5
  • 37
  • 59