Adding Fulltext Index - A possible pleasant side effect

[pastvne]

Hi:
I have a Fabrik list that contains about a dozen elements. One of the elements holds XML. This was originally setup as a text field. After exceeding the MySQL size limit, I modified the column at the MySQL level to medium Text. However, as the database grew over 100K records, I really noticed query performance going down dramatically. I tried repairing and explaining the queries but nothing really stood out. Unfiltered queries were taking upwards of 30 seconds before the first page 10 records would display.

I will be upgrading my server with more memory and CPU but tonight after reading up on medium text, I decided to try to apply a fulltext index on my medium text field. Of course, now my 'search all' is dramatically faster going from about 86 seconds to about 3 seconds due to indexing... that I expected.

But I think I'm also seeing a dramatic increase in list display speed. I believe I'm seeing the query go from 25+ seconds about 6 seconds. I realize that repeated queries will benefit from Caching so I've tried to factor that in and tried to flush the database out of memory.

But I think I'm still seeing this performance boost and I'm not really sure why. I read in some texts on this topic that medium-text is stored outside of the main table and that when you query and the column is not indexed, it must go outside the table space to get the mediumtext which slows performance. Is it possible that with an index set, that MySQL is somehow able to avoid the fetch of the text outside of table space and uses the index instead? This is the only explanation I can think of for what I think I'm seeing. Are there any MySQL gurus out there willing to chime in to explain this pleasant surprise?

Thanks,

Bruce Decker
720.733.0459

 

[cheesegrits]

Was the original field type a VARCHAR?

Your analysis is essentially correct. Although I'm surprised there wasn't already an index on that field if you have it selected for including in a J! search, as I thought we applied indexes to anything you select for searching. Although we wouldn't have changed that to a 'full text' index if you changed the field type from VARCHAR to TEXT, as we wouldn't notice the change.

Just be aware that by changing the field type, you may have subtly changed search behavior, with respect to things like case sensitivity and collation.

-- hugh

 

[pastvne]

Thanks Hugh. I believe the field was originally a text type which I moved to medium text at the MySQL level. Honestly, I made the change to medium text some time ago and I'll have to try to restore an early copy of the application to see what is was originally configured to be. But, the database did't have any index of any type on that field. Thanks for the heads up on the search behavior. I'll keep that in mind. We're testing now with fingers crossed.