SELECT MATCH AGAINST asterisk

[raduenea]

Hello,

I have the following code that should select all strings that start with "rog" (like rogers, etc).

							SELECT 
								user_id, 
								first_name AS name,
								gender
							FROM 
								user_details 
							WHERE 
								MATCH (first_name) AGAINST('rog*') 

The problme it's that  shows me zero results. In first_name column I have a lot of words with "rogers". 

first_name column have Fulltext assign. 

If I use just "...AGAINST ('rogers')" it return corect data.

It's something wrong with my sentence? 

Thank you

[raduenea]

Meanwhile I found the solution.

I used 

"...AGAINST ('rog*' IN BOOLEAN MODE)" 

I just wonder why I need to add "IN BOOLEAN MODE"  to search with (*) asterisk ?

[requinix]

Why use MATCH AGAINST when you could just use LIKE?

[raduenea]

2 hours ago, requinix said:

Why use MATCH AGAINST when you could just use LIKE?

I just understand that is faster than LIKE. At least that I've heard.

Have you tested ?

[Barand]

These are the results I get (wordlist contains 351,100 records)

 

$t1 = microtime(1);
$res = $db->query("SELECT word FROM wordlist WHERE MATCH (word) AGAINST ('sang*' IN BOOLEAN MODE)");
$t2 = microtime(1);
printf('Query 1 :  %0.4f seconds<br>', $t2 - $t1);  

$t1 = microtime(1);
$res = $db->query("SELECT word FROM wordlist WHERE word LIKE 'sang%'");
$t2 = microtime(1);
printf('Query 2 :  %0.4f seconds<br>', $t2 - $t1);  

results (74 words found)

Query 1 : 0.0026 seconds
Query 2 : 0.0005 seconds

 

[raduenea]

On 9/6/2021 at 10:46 AM, Barand said:

Thanks for this analysis.

Certanly I will use LIKE in the sentence.

All the best.