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.
