MySQL won't use my fulltext index

[Kennel]

I'm developing a forum/guestbook thingy and I'm having some problems getting MySQL to use the appropriate indices in the following query:

SELECT t.*, SUM(MATCH(message) AGAINST('test')) AS score
FROM topics AS t
INNER JOIN posts AS p
ON p.topicid = t.id
WHERE t.ghost IS NULL AND MATCH (message) AGAINST ('test') GROUP BY t.id
ORDER BY score DESC
LIMIT 0, 35

The indices are
topics: PRIMARY( id )
posts: PRIMARY( id ), KEY( topicid ), FULLTEXT( message )

I guess the best thing would be for it to use the fulltext index in the posts table (message) for "the search" and then the topicid index in topics table for the join. It doesn't appear to be doing that though. The query takes forever.

EXPLAIN gives me this:

+-------+--------+---------------+---------+---------+-----------+--------+---------------------------------------------+
| table | type   | possible_keys | key     | key_len | ref       | rows   | Extra                                       |
+-------+--------+---------------+---------+---------+-----------+--------+---------------------------------------------+
| p     | ALL    | topicid       | NULL    |    NULL | NULL      | 201455 | where used; Using temporary; Using filesort |
| t     | eq_ref | PRIMARY       | PRIMARY |       4 | p.topicid |      1 | where used                                  |
+-------+--------+---------------+---------+---------+-----------+--------+---------------------------------------------+

Does anyone have any idea?

/Kennel

[[deleted]]

MySQL's fulltext index is a search method, not really an index like the PK.

Hence it can never be used like a normal index and thus it doesn't appear in the explain output.

[Kennel]

Aha, I had no idea. Does the manual mention that anywhere? In that case, I must have missed it.

Anyway. The query takes around 7 seconds on a Pentium2 450 with 192 ram (running linux and latest mysql). Doesn't that seem a little too much? The posts table contains around 200K rows and the topics table around 18K. Do you have any suggestions as to how I can improve the query?

[[deleted]]

It doesn't actually mention it, but it stands to reason: The 'index' just offers some extra functionality on-top of what is basically a 'LIKE' operator. It compares two strings and calculates the likeness, which you can then use to decide which records to select. But obviously the likeness depends on what you put into the 'MATCH' clause, so it cannot be pre-calculated and stored in an index they way you'd store the auto_increment value in an index. That means the database must re-calculate for every row, even if it turns out you only need one row out of 30 million.

If you have 200k records, the fulltext will calculate the likeness for all 200k rows, which will take time. IN your case: 7 seconds.

This is what makes the fulltext as it is quite useless. A 'LIKE' can do just about the same, and a levenshtein() or soundex() can take it even further.

You may want to read the article entitles "slapping together a search-engine" on this very site, it may give you a few fresh ideas about searching in texts.

[Kennel]

I actually read that article a few months ago, but then discovered the fulltext capability in MySQL and believed that it would solve the problem for me. I guess I was wrong 🙁

BTW. I haven't gotten around to testing a similar search with PostgreSQL and fulltext indexing (fti). Do you believe it would be faster there?

Thanks for the help!

[[deleted]]

Actually I haven't really tried the fulltext index in PG, never had the need to really...