CODEWITHSUNDEEP
phpmysqlfull-text-searchmyisamfull-text-indexing
Zen
Zen

Reputation: 7385

MySQL FULLTEXT not matching string with wildcard

I'm trying to do a basic full text search on my MySQL database - here's the table I'm using:

tables removed

Any input would be appreciated, as I've not used full text searching before (or indexing for that matter!)

Thanks

EDIT:

I've chosen to use FULLTEXT because of the supposed slowness of LIKE searches - there will be a whole lot of these running, as it's for a javascript autocomplete type thing. Is using LIKE is a viable option for this? I have been advised against it. (I bring this up because all of the answers have said to use LIKE rather than addressing my actual question)

Upvotes: 1

Views: 1579

Answers (4)

Cups
Cups

Reputation: 6896

You are mixing your metaphors here. You want to use FULLTEXT for good reasons, then you use the syntax from LIKE.

Try searching for just plain "savo" without the wildcards, and see if that is acceptable.

BTW 2 big gotchas with Mysql FULLTEXT search:

  1. It won't react to less than 4 letters being entered without going back and messing with the cnf ( I think its 4, you'd need to check on this )

  2. If the result set count is greater than 50% of the number of total rows available, then it figures the search has failed, and returns nothing. Worth remembering when only testing vs 12 rows, and say they all have the word test in them. Ouch ;)

Upvotes: 1

vfedorkov
vfedorkov

Reputation: 847

MySQL Full text search has limited functionality and relatively slow search. I would suggest to use external full-text search engine like Solr or Sphinx for this type of queries. I'm not familiar with Solr, but in case of Sphinx you could hire prefix indexing that supported along with enable_star option in config file.

Here is an example on how to replace MySQL FT with Sphinx. You could also use Sphinx for faceted search, related document search and other useful stuff.

Hope this helps.

Upvotes: 2

Russell Gutierrez
Russell Gutierrez

Reputation: 1385

I think you are trying to do something like this:

 select * from users where (
username like ('Sav%') 
or email like ('Sav%') 
or first_name like ('Sav%') 
or last_name like ('Sav%') );

Upvotes: -1

Teddy
Teddy

Reputation: 18572

Use the LIKE command and use the percent symbol ( % ) as the wildcard:

SELECT user_id, username, first_name, last_name 
FROM users 
WHERE (username LIKE 'Sav%') 
   OR (email LIKE 'Sav%') 
   OR (first_name LIKE 'Sav%') 
   OR (last_name LIKE 'Sav%');

Upvotes: 1

Related Questions