I've got a database that's about 20 GB in dimensions. I wish to determine if you will find any optimisation tips specific to using a database that's static. After I mean static, I do not mean changes rarely, I am talking about will not change whatsoever. What are the extreme configurations for values or any other stuff that normally you avoid having a volatile database, that may benefit a really static database especially thinking about there are only Choose claims and simply no Place claims? I am using MyISAM tables.
Since things are MyISAM, you have to concentrate on two major things:
The primary mechanism employed for caching is paramount cache. It only caches index pages from .MYI files. To size your key cache, run the next query:
SELECT CONCAT(ROUND(KBS/POWER(1024,IF(PowerOfTwo<0,0,IF(PowerOfTwo>3,0,PowerOfTwo)))+0.4999), SUBSTR(' KMG',IF(PowerOfTwo<0,0,IF(PowerOfTwo>3,0,PowerOfTwo))+1,1)) recommended_key_buffer_size FROM (SELECT LEAST(POWER(2,32),KBS1) KBS FROM (SELECT SUM(index_length) KBS1 FROM information_schema.tables WHERE engine='MyISAM' AND table_schema NOT IN ('information_schema','mysql')) AA) A, (SELECT 2 PowerOfTwo) B;
This can provide the Suggested Setting for MyISAM Key Cache (key_buffer_size) given your present data set (the query will cap the recommendation at 4G (4096M)).For 32-bit OS, 4GB may be the limit. For 64-bit, 8GB.
You need to alter the stopword list. You might want to alter the stop words because MySQL won't index this list of 643 words. Try creating your personal stopword list and altering the min word length.
Step One) Produce a stop word list of your. You could include 'a','an', and 'the'.
echo "a" > /var/lib/mysql/custom_stopwords.txt<BR> echo "an" >> /var/lib/mysql/custom_stopwords.txt<BR> echo "the" >> /var/lib/mysql/custom_stopwords.txt
Step Two) Add these choices to /etc/my.cnf
Step Three) service mysql restart
Step Four) Create new FULLTEXT indexes. Any existing FULLTEXT indexes before restart of mysql ought to be reindexed.
Give These Ideas an attempt !!!