Search function does not work (s9y 0.7-beta3)
Search function does not work (s9y 0.7-beta3)
Hi,
I encountered a new problem - the search function does not work.
Take a look at http://tomscorner.at. Type 'idiot' or 'IDIOT' in the search box and press enter. Actually is should show at least 1 result (the word is used in the most last posting from September 24th).
The result is 1 hit - no entries found.
Regards, Thomas
I encountered a new problem - the search function does not work.
Take a look at http://tomscorner.at. Type 'idiot' or 'IDIOT' in the search box and press enter. Actually is should show at least 1 result (the word is used in the most last posting from September 24th).
The result is 1 hit - no entries found.
Regards, Thomas
-
- Core Developer
- Posts: 30022
- Joined: Tue Sep 16, 2003 9:45 pm
- Location: Cologne, Germany
- Contact:
Re: Search function does not work (s9y 0.7-beta3)
Which database type (mysql, pgsql) are you using and which version?
Regards,
Garvin
Regards,
Garvin
# Garvin Hicking (s9y Developer)
# Did I help you? Consider making me happy: http://wishes.garv.in/
# or use my PayPal account "paypal {at} supergarv (dot) de"
# My "other" hobby: http://flickr.garv.in/
# Did I help you? Consider making me happy: http://wishes.garv.in/
# or use my PayPal account "paypal {at} supergarv (dot) de"
# My "other" hobby: http://flickr.garv.in/
-
- Core Developer
- Posts: 30022
- Joined: Tue Sep 16, 2003 9:45 pm
- Location: Cologne, Germany
- Contact:
For debugging, you could open the serendipity_functions.inc.php file and search for 'function serendipity_searchEntries($term) {'
This is about line 922 in my installation. There you replace this:
with this:
You may want to execute the query shown there in phpMyAdmin and see if you get a resultset there?
Regards,
Garvin.
This is about line 922 in my installation. There you replace this:
Code: Select all
return serendipity_db_query($querystring);
Code: Select all
echo $querystring; return serendipity_db_query($querystring);
Regards,
Garvin.
# Garvin Hicking (s9y Developer)
# Did I help you? Consider making me happy: http://wishes.garv.in/
# or use my PayPal account "paypal {at} supergarv (dot) de"
# My "other" hobby: http://flickr.garv.in/
# Did I help you? Consider making me happy: http://wishes.garv.in/
# or use my PayPal account "paypal {at} supergarv (dot) de"
# My "other" hobby: http://flickr.garv.in/
Hi,
I did as you suggested and executed the SQL query inside phpMyAdmin.
This is the result:
Regards, Thomas
I did as you suggested and executed the SQL query inside phpMyAdmin.
This is the result:
Code: Select all
#1191 - Can't find FULLTEXT index matching the column list
Hi,
actually I troubleshooted the query a bit and came along the conclusion that:
1. there were no fulltext indexes created. I created them by hand with phpMyAdmin on title, body, author and extended
2. for some reason you cannot combine more than two columns with the match() function. If I take only extended, for instance, it works in SQL.
The SQL query which finally worked then is:
The result is the expected one.
The correct creation of that table might be something like:
Regards, Thomas
actually I troubleshooted the query a bit and came along the conclusion that:
1. there were no fulltext indexes created. I created them by hand with phpMyAdmin on title, body, author and extended
2. for some reason you cannot combine more than two columns with the match() function. If I take only extended, for instance, it works in SQL.
The SQL query which finally worked then is:
Code: Select all
SELECT e.id, e.author, a.username, a.email, ec.categoryid, e.timestamp, e.comments, e.title, e.body, e.extended, e.trackbacks, e.exflag
FROM serendipity_entries e, serendipity_authors a, serendipity_entrycat ec
WHERE a.authorid = e.authorid AND e.id = ec.entryid AND (
MATCH ( e.EXTENDED )
AGAINST ( 'idiot' ) OR
MATCH ( e.title )
AGAINST ( 'idiot' ) OR
MATCH ( e.body )
AGAINST ( 'idiot' ) ) AND isdraft = 'false' AND timestamp <=1096043469
GROUP BY e.id
ORDER BY timestamp DESC
The correct creation of that table might be something like:
Code: Select all
CREATE TABLE `serendipity_entries` (
`id` int(11) NOT NULL auto_increment,
`title` varchar(200) default NULL,
`timestamp` int(10) unsigned default NULL,
`body` text,
`comments` int(4) unsigned default '0',
`trackbacks` int(4) unsigned default '0',
`extended` text,
`exflag` int(1) default NULL,
`author` varchar(20) default NULL,
`authorid` int(11) default NULL,
`isdraft` enum('true','false') NOT NULL default 'true',
`allow_comments` enum('true','false') NOT NULL default 'true',
`last_modified` int(10) unsigned default NULL,
`moderate_comments` enum('true','false') NOT NULL default 'true',
PRIMARY KEY (`id`),
FULLTEXT KEY `extended` (`extended`),
FULLTEXT KEY `title` (`title`),
FULLTEXT KEY `body` (`body`)
) TYPE=MyISAM
-
- Core Developer
- Posts: 30022
- Joined: Tue Sep 16, 2003 9:45 pm
- Location: Cologne, Germany
- Contact:
Hi Thomas!
Strange, we do set that fulltext index for the entries inside 'db.sql'. And it does exist on my installations.
I wonder which MySQL version you are running, because matching 3 (or more) columns is no problem for mine...?
Regards,
Garvin.
Strange, we do set that fulltext index for the entries inside 'db.sql'. And it does exist on my installations.
I wonder which MySQL version you are running, because matching 3 (or more) columns is no problem for mine...?
Regards,
Garvin.
# Garvin Hicking (s9y Developer)
# Did I help you? Consider making me happy: http://wishes.garv.in/
# or use my PayPal account "paypal {at} supergarv (dot) de"
# My "other" hobby: http://flickr.garv.in/
# Did I help you? Consider making me happy: http://wishes.garv.in/
# or use my PayPal account "paypal {at} supergarv (dot) de"
# My "other" hobby: http://flickr.garv.in/
Well, it seems to depend on the MySQL versions you're using I think.
It worked that way for me on the MySQL version 4.0.17 provided by my hoster. And for some reasons I think sometimes it is better to split things rather than combining them.
I don't know but how would MySQL 3.2x handle such a query? Does matching in three columns work or does it support grouped queries (like:
AND ....
AND (
MATCH(...) AGAINST(...) OR
MATCH(...) AGAINST(...)
)
)?
It worked that way for me on the MySQL version 4.0.17 provided by my hoster. And for some reasons I think sometimes it is better to split things rather than combining them.
I don't know but how would MySQL 3.2x handle such a query? Does matching in three columns work or does it support grouped queries (like:
AND ....
AND (
MATCH(...) AGAINST(...) OR
MATCH(...) AGAINST(...)
)
)?
-
- Core Developer
- Posts: 30022
- Joined: Tue Sep 16, 2003 9:45 pm
- Location: Cologne, Germany
- Contact:
Hi Thomas!
MySQL 3.x doesn't handle MATCH queries at all, as it doesn't support it.
And splitting the matches to three clauses will spawn a MATCH-interpreter from MySQL three times instead of only once. And splitting those three will not order the clause by the best overall search-result factor, but instead by only the first match method...
Regards,
Garvin.
MySQL 3.x doesn't handle MATCH queries at all, as it doesn't support it.
And splitting the matches to three clauses will spawn a MATCH-interpreter from MySQL three times instead of only once. And splitting those three will not order the clause by the best overall search-result factor, but instead by only the first match method...
Regards,
Garvin.
# Garvin Hicking (s9y Developer)
# Did I help you? Consider making me happy: http://wishes.garv.in/
# or use my PayPal account "paypal {at} supergarv (dot) de"
# My "other" hobby: http://flickr.garv.in/
# Did I help you? Consider making me happy: http://wishes.garv.in/
# or use my PayPal account "paypal {at} supergarv (dot) de"
# My "other" hobby: http://flickr.garv.in/
Hi,
actually I found out why it didn't work on my site.
My hoster created my a database but forgot to grant me the right to create indexes inside the database. So the FULLTEXT index on (title, body, extended) wasn't created.
I created them manually but per column means I created von FULLTEXT index for body, one for extended and one for title.
And because of the seperated indexes the search function does not work when the index on those columns is seperated.
I had than to alter the table and add the rest of the columns, set back the old search function and - voilá - it works again.
Sorry for the inconveniences I caused.
Regards, Thomas
actually I found out why it didn't work on my site.
My hoster created my a database but forgot to grant me the right to create indexes inside the database. So the FULLTEXT index on (title, body, extended) wasn't created.
I created them manually but per column means I created von FULLTEXT index for body, one for extended and one for title.
And because of the seperated indexes the search function does not work when the index on those columns is seperated.
I had than to alter the table and add the rest of the columns, set back the old search function and - voilá - it works again.
Sorry for the inconveniences I caused.
Regards, Thomas
-
- Core Developer
- Posts: 30022
- Joined: Tue Sep 16, 2003 9:45 pm
- Location: Cologne, Germany
- Contact:
Hi Thomas!
Actually, this is a great hint to what may be causing trouble on other blogs as well, where the search didn't work. I also forgot about that option, so thanks a lot for reminding. I am sure this will help users in the future, so not any inconvenience at all!
Thanks a lot,
Garvin.
Actually, this is a great hint to what may be causing trouble on other blogs as well, where the search didn't work. I also forgot about that option, so thanks a lot for reminding. I am sure this will help users in the future, so not any inconvenience at all!
Thanks a lot,
Garvin.
# Garvin Hicking (s9y Developer)
# Did I help you? Consider making me happy: http://wishes.garv.in/
# or use my PayPal account "paypal {at} supergarv (dot) de"
# My "other" hobby: http://flickr.garv.in/
# Did I help you? Consider making me happy: http://wishes.garv.in/
# or use my PayPal account "paypal {at} supergarv (dot) de"
# My "other" hobby: http://flickr.garv.in/
Minimum search length?
Searching for a four letter word returns "No Entries Found for Query" searching for a 5 letter word returns the proper response.
http://www.i40.com/
search for Tivo (used often) returns "No Entries Found for Query Tivo".
search for poker, and you get the proper results.
Is this specific to my mySQL installation? a quirk?
any ideas?
-Jason
http://www.i40.com/
search for Tivo (used often) returns "No Entries Found for Query Tivo".
search for poker, and you get the proper results.
Is this specific to my mySQL installation? a quirk?
any ideas?
-Jason
-
- Core Developer
- Posts: 30022
- Joined: Tue Sep 16, 2003 9:45 pm
- Location: Cologne, Germany
- Contact:
Hm, I think I remember to have seen a MySQL directive which restricts the amount of needed characters. Possibly Jason's server is setup to need at least 5 chars instead of the default 4 characters?jhermanns wrote:i thought that more than 3 characters are needed for mysql's fulltext search. hmmmm....
# Garvin Hicking (s9y Developer)
# Did I help you? Consider making me happy: http://wishes.garv.in/
# or use my PayPal account "paypal {at} supergarv (dot) de"
# My "other" hobby: http://flickr.garv.in/
# Did I help you? Consider making me happy: http://wishes.garv.in/
# or use my PayPal account "paypal {at} supergarv (dot) de"
# My "other" hobby: http://flickr.garv.in/
In myisam/ftdefs.h is the definition of the minimal word length defined.
This might differ from installation to installation.
I also recognized that partial words are not searched. It has to be the full word then a result is shown.
Regards, Thomas
Code: Select all
#define MIN_WORD_LEN 4
I also recognized that partial words are not searched. It has to be the full word then a result is shown.
Regards, Thomas