CODEWITHSUNDEEP
mysqlfull-text-search
dell
dell

Reputation: 172

Searching 5 million records using full text search

I have a dataset of about 5.3 million records. I have a very trivial issue with this query:

select 
  SystemId, 
  Id, 
  FirstName, 
  LastName,
  AptUnitNo, 
  Zip, 
  StreetName,
  streetnumber,
  (case 
     when LastName like '%robe%' 
       then -8 
     else 
       0 
    end ) as Ordervalue 
 From registeredusers 
 Where LastName like '%robe%'
 Order by Ordervalue, LastName, FirstName, StreetName, StreetNumber ASC 
 limit 0,50

This query gives the records based on the best match found in the lastname. I added the full text index on lastname.

I need some help how to convert the above query to work based on the full text index.

I tried to use match against instead of the like operator but it's giving me an error.

Upvotes: 2

Views: 1286

Answers (1)

vfedorkov
vfedorkov

Reputation: 847

please note that constraint "LastName like '%robe%'" because of beginning "%" will enforce MySQL to perform full table scan though all 5 million rows. This is very slow and most of the time is not acceptable for production systems like websites. Problem is you can't create efficient index to help this query. MySQL Full-Text index is a bit better but will not perform well.

In this case external search engine like Sphinx or Solr is way out for both performance and features.

Upvotes: 2

Related Questions