cathie — 2010-01-21T03:51:24-05:00 — #1
$q1 = $db->GetAll("SELECT id, title,path FROM ".T_CATEGORIES." WHERE title LIKE '%".$PMDR->get('Cleaner')->clean_db($_GET['keyword'])."%' ");
$q2 = $db->GetAll("SELECT * FROM ".T_CATEGORIES." WHERE MATCH(title) AGAINST ('".$PMDR->get('Cleaner')->clean_db($_GET['keyword'])."')");
Hi, I have 2 queries here for my search. Both are expected to return the same result.
The 1st query ($q1) will return result correctly.
The 2nd query return empty array. I have set the title field as fulltext.
Do you know where is the problem?
sjh — 2010-01-21T04:05:31-05:00 — #2
The first thing to do is echo the generated query in your PHP application and paste it into a MySQL command line client or into phpMyAdmin to see if it returns any results.
cathie — 2010-01-21T04:15:44-05:00 — #3
Hi SJH, thanks for your reply.
I tested my search again.
I found that if I search for keyword "rack" with $q2, it return 0 result. When I search keyword "racks" with $q2 again, it will return 2 results.
It seems full text search will not match the keyword in the %like% way.
Is there any solution I can do cause my search causing my site down so I thinking to convert all the search using %like% method to full text search.
I tried adding "WITH QUERY EXPANSION" in the query, but the result is zero.
SELECT * FROM ".T_CATEGORIES."
WHERE MATCH (
WITH QUERY EXPANSION
sjh — 2010-01-21T06:22:21-05:00 — #4
Does the word "rack" appear in more than 50% of the records that the search query would return? If so, full text will ignore those results when you search for that keyword.
Using %like% unfortunately is not a viable alternative because MySQL will have to carry out a full table scan, which will cause wildcard search queries to perform very badly as soon as you've got a large number of rows.
cathie — 2010-01-21T08:59:42-05:00 — #5
There are 1600 rows in the category table, 2 categories contain the keyword "racks".
So in this case (searching for "rack"), the full text search will not return the two results?
When I search racks, it will show 2 matched result.
cathie — 2010-01-21T09:58:45-05:00 — #6
I found the solution!!!
All I need to do is to use full text search "IN BOOLEAN MODE", then put the keyword in between *.
beeleg — 2010-02-15T17:03:08-05:00 — #7
So just a question....
When you search for "rack" with Full Text Indexing using $q2 in Boolean Mode it will return like words such as "racks", "blackracks" and "rackona"?
Basiclly with your $q2 your able to return LIKE matches '%rack%'?
swto — 2010-02-16T11:43:43-05:00 — #8
interesting -- I thought that fulltext searching could only do wildcards at the *end of the word. Has something been updated in MySQL?
beeleg — 2010-02-16T12:55:36-05:00 — #9
Yes, so did I from my understanding. Cathie is showing here that keyword with IN BOOLEAN MODE but does this allow the functionality of %like%? I think maybe a typo has happened here... Or is the syntax suppose to surround the keyword?
swto — 2010-02-16T19:52:05-05:00 — #10
yes the form is supposed to be
... ('$word*' IN BOOLEAN MODE)
Unless something has changed in MySQL...
Cathie - do you mind posting your code for this?