My server is: mysql Ver 14.7 Distrib 4.1.21, for pc-linux-gnu (i686) using readline 4.3)
There are several fields in one table and 2 of the fields have fulltext indexes applied. Both fields are defined as text datatype but one (field_a) contains data that is fewer than 100 characters while the other field (field_b) contains data consisting of 3,000 to 15,000 characters. There are about 10,000 rows in the table.
If I run a fulltext query against both fields, the performance is ~5 times slower than if I only include the field_a in the query. Any idea how the server/index/query can be tweaked so that when larger fields are included in fulltext search performance does not degrade that drastically?
I'd be happy to provide more details if necessary (I did not include the query at this point as I suspect it's the amount of field data rather than the query that's causing slow performance).
FIY: I did have an order by clause in the query and the column in the order by is not field_a or field_b. I tried creating an index on it now and the performance is much better although not as good as if I only use field_a in the query. This may be my only way to optimize it... What do you think?