FULLTEXT redux

In my post from yesterday, I ended up resorting to using multiple-column fulltext indexes to make it so I was actually using the indexes on the columns specified in the query. Well, that worked. But it also resulted in HUGE indexes!

Before change:

-rw-rw---- 1 mysql mysql 1177773692 2008-06-05 12:37 items_text.MYD
-rw-rw---- 1 mysql mysql 1136713728 2008-06-05 12:37 items_text.MYI

After adding more indexes:

-rw-rw---- 1 mysql mysql 1156516200 2008-06-04 17:14 items_text.MYD
-rw-rw---- 1 mysql mysql 1978787840 2008-06-04 17:14 items_text.MYI

Furthermore, this made the table much harder to update. Replication kept lagging last night (nagios was complaining loudly).

I've since reverted back to the way I had it, not using the index, which is the least worse of my options currently. Someone asked me to try using this query:

select count(*) from items_text where MATCH (content)  AGAINST ('+iron +man' IN BOOLEAN MODE) or  MATCH (title) AGAINST ('+iron +man' IN BOOLEAN MODE)

This doesn't use indexes the way you would hope either! As soon as you add one more index to the query, even with "OR", you get this:

mysql> explain select count(*) from items_text where MATCH (content)  AGAINST ('+iron +man' IN BOOLEAN MODE)\G*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: items_text
         type: fulltext
possible_keys: content
          key: content
      key_len: 0
          ref: 
         rows: 1
        Extra: Using where
1 row in set (0.00 sec)

mysql> explain select count(*) from items_text where MATCH (content)  AGAINST ('+iron +man' IN BOOLEAN MODE) or  MATCH (title) AGAINST ('+iron +man' IN BOOLEAN MODE)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: items_text
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1325662
        Extra: Using where
1 row in set (0.00 sec)

So, back to the drawing board. Try another fulltext indexing option. This sure doesn't cut it.