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:
After adding more indexes:
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:
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:
So, back to the drawing board. Try another fulltext indexing option. This sure doesn't cut it.
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.