OpenEuphoria

2. Re: Forum search kaput?

• Posted by jimcbrown (admin) Aug 24, 2010
• 1690 views

I did some tests, and this is either a MySQL bug or a problem with the following SQL query:

mysql> SELECT 'forum', id, created_at, subject FROM messages WHERE MATCH(subject, body) AGAINST("arm" IN BOOLEAN MODE); 
Empty set (0.00 sec) 
 
mysql> SELECT 'forum', id, created_at, subject FROM messages WHERE MATCH(subject, body) AGAINST("kaput" IN BOOLEAN MODE); 
+-------+--------+---------------------+---------------------+ 
| forum | id     | created_at          | subject             | 
+-------+--------+---------------------+---------------------+ 
| forum |  69267 | 1997-07-07 12:39:53 | New Address         | 
| forum | 112159 | 2010-08-24 05:46:54 | Forum search kaput? | 
+-------+--------+---------------------+---------------------+ 
2 rows in set (0.00 sec) 

The results between the search function on the forum and the raw mysql query are identical.

A slightly different query returns plenty of results:

mysql> SELECT 'forum', id, created_at, subject FROM messages WHERE subject like '%ARM%'; 
+-------+--------+---------------------+---------------------+ 
| forum | id     | created_at          | subject             | 
+-------+--------+---------------------+---------------------+ 
...snip snip snip... 
| forum |  32460 | 2005-08-03 00:36:00 | Euphoria for StrongARM Processors                               | 
...snip snip snip... 
| forum | 103168 | 2008-12-08 12:26:47 | cross compiling for ARM                                         | 
| forum | 106681 | 2009-06-14 21:20:17 | Euphoria on the ARM processor?                                  | 
...snip snip snip... 
| forum | 110636 | 2010-01-06 20:27:14 | Euphoria on ARM (and other processors)                          | 
...snip snip snip... 
| forum | 110757 | 2010-01-21 12:00:46 | Re: Euphoria on ARM (and other processors)                      | 
| forum | 112140 | 2010-08-21 12:48:22 | Euphoria 4.0b3 on ARM                                           | 
...snip snip snip... 
113 rows in set (0.41 sec) 

Anyways, here are some links I found that might interest you:

http://oe.cowgar.com/forum/110636.wc?last_id=110757

http://oe.cowgar.com/forum/106681.wc?last_id=107054

http://oe.cowgar.com/forum/24382.wc?last_id=24457

4. Re: Forum search kaput?

• Posted by CoJaBo Aug 26, 2010
• 1519 views

My sql full text search does not index words shorter than four letters. You would have to recompile my sql to fix this, which wouldnt be doable anyway if eu forum ever moved back to shared hosting. You could use LIKE assuming its not hideously slow (probably would be on a forum this big), recompile my sql (which would mean never being able to use shared hosting), create your own indexing system (php scripts exist already to do this), or simply make the forum search refuse short words.

6. Re: Forum search kaput?

• Posted by jimcbrown (admin) Nov 23, 2010
• 1462 views

This turns out to be incorrect. From Jeremy: http://dev.mysql.com/doc/refman/5.1/en/fulltext-fine-tuning.html

Lowering this value to index even 1-char words turns out to be a config option in /etc/mysql/my.cnf followed by restarting mysqld

Then, manually reindex each table (REPAIR TABLE table_name QUICK) and it's done.

So, this has now been fixed.