Search function does not work (s9y 0.7-beta3)

[Thomas]

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

[garvinhicking]

Which database type (mysql, pgsql) are you using and which version?

Regards,
Garvin

[Thomas]

The version is s9y 0.7-beta3 and the database is mysqlin version is 4.0.17-log (according to the output of phpMyAdmin).

Regards, Thomas

[garvinhicking]

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:

Code: Select all

return serendipity_db_query($querystring);

with this:

Code: Select all

echo $querystring; return serendipity_db_query($querystring);

You may want to execute the query shown there in phpMyAdmin and see if you get a resultset there?

Regards,
Garvin.

[Thomas]

Hi,

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 

Regards, Thomas

[Thomas]

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:

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 result is the expected one.

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

Regards, Thomas

[garvinhicking]

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.

[Thomas]

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(...)
)
)?

[garvinhicking]

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.

[Thomas]

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

[garvinhicking]

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.

[jasonr]

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

[jhermanns]

i thought that more than 3 characters are needed for mysql's fulltext search. hmmmm....

[garvinhicking]

i thought that more than 3 characters are needed for mysql's fulltext search. hmmmm....

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?

[Thomas]

In myisam/ftdefs.h is the definition of the minimal word length defined.

Code: Select all

#define MIN_WORD_LEN 4

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