1

I have been struggling with full-text search with MySQL but always have some issues, I have 2 columns first_name and last_name,

my app is built on php codeigniter framework, at first i was searching name with mysql LIKE.

$this->db->like("CONCAT(TRIM(co.first_name),' ',(co.last_name))", trim($search_firstname.' '.$search_lastname));

problem with searching like this is when someone enter last name first and then enter half of first name then the search return 0 results, for example if the name is Arthur Peglow and when i enter last name 'peglow' and then enter Art the search return 0 results.

i decided to create full text index

ALTER TABLE contacts ADD FULLTEXT INDEX `FullText` (`first_name` ASC, `last_name` ASC);

$this->db->where('MATCH (co.first_name, co.last_name) AGAINST ("'.$search_name.'")', NULL, FALSE);

the first problem i faced was, it returned 0 results with less than 3 characters, i fixed this by changing ft_min_word_len to 2, i still notice sometimes it doesnt return any results with 2 characters entered the problem is still not solved, it shows results when entered min 3 characters, when entered name 'arth' it doesnt return the record with name 'Arthur Peglow' but just return the result with name 'Arth' only

adding another character 'Arthu' returned 0 results, entering full name 'Arthur' returned the expected result, it is clearly not returning the expected results

my question is clear, what am i doing wrong? any suggestions?

Abdulla Nilam
  • 36,589
  • 17
  • 64
  • 85
Wasif Khalil
  • 2,217
  • 9
  • 33
  • 58
  • This is in fact a very complex question because you want a full search engine, not just a simple full text search. MySQL's built-in fulltext search engine has quite a few limitations and can't really be used to implement a flexible search engine. – Shadow Jan 22 '18 at 17:15
  • can u suggest some links to explain this with possible fix? – Wasif Khalil Jan 22 '18 at 17:18
  • There is no easy fix - unless you want to have series of inefficient `fieldname like '%...%'` search conditions. These will give you the expected results, but will bring your MySQL server down on the long run because no indexes can be used. You either have to compromise on the flexibility of your search or you have to start thinking about third party fulltext search providers, such as sphinx. – Shadow Jan 22 '18 at 17:24
  • thanks for suggesting sphinx, im gonna look at it, i would'nt mind using 3rd party search engine – Wasif Khalil Jan 22 '18 at 17:52
  • **WARNING**: Your second query has `$search_name` inlined directly without any apparent escaping and this is probably a severe SQL injection bug. Whenever possible use the database layer provided by CodeIgniter. Don't bypass it arbitrarily. – tadman Jan 22 '18 at 17:59
  • Im using google cloud sql, can u suggest how i can setup sphinx? – Wasif Khalil Jan 23 '18 at 06:25

0 Answers0