Full text index and other complex indexes together
by Harun Yayli on Monday, April 30th, 2007 at 11:21 pm under Programming
Always remember, if you’re using MyIsam tables, KEEP THE INDEX AS SMALL AS POSSIBLE.
That means, your full text index will be huge and you don’t want to keep them together with other types of index for faster queries.
Simply create another table to have the text fields and full text indexes and left join. It works way faster for me on a table with 230K rows.
Data take about 500MB and the indexes take about 700MB. I don’t think I over indexed the table because of the delicate business logic. Even for a simple 1 field query (with normal index on it) it was taking more than 30secs. I removed the table with out the full text indexes and now it takes 1 sec.
Wow, a good thing to remember. A mental note to myself again.
Thoughts Aside:
The process of data security is the best computer backup solution and it is the way of ensuring that backup computer files are kept protected from corruption with free software and that access to it is properly controlled on a back up server. It also facilitates the process of online file sharing. The globalization of data recovery hardware networking is a facility with the help of which your stock can be sourced from anywhere in the world by doing up with your online storage.
Recent Entries
- Looking for 2 PHP Developers in Montreal (Senior and Intermediate)
- The difference between a bug and a feature
- Designing for Social Traction
- How to make Facebook shared links appear nice
- new pecl/memcache is out with memcache.php
- memcache.php flushes servers
- memcache.php can delete keys now
- memcache.php is now part of pecl/memcache
- memcache.php goes PECL
- memcache.php stats like apc.php
Try moving your fulltext indexes to Sphinx, http://sphinxsearch.com/
That way you could still have fulltext searches on that table without hurting other queries.
September 18th, 2007 at 9:53 am