MySQL optimization tips specific to a completely static database?

I have a database that is about 20 GB in size. I want to know if there are any optimization tips specific to working with a database that is static. When I mean static, I don't mean changes infrequently, I mean won't change at all. Are there any extreme settings for values or other things that normally you stay away from with a volatile database, that can benefit a truly static database; especially considering there will only be SELECT statements and absolutely n开发者_JS百科o INSERT statements? I'm using MyISAM tables.

-- roschler

Since everything is MyISAM, you need to focus on two major things:

KEY CACHE

The main mechanism used for caching is the key cache. It only caches index pages from .MYI files. To size your key cache, run the following 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 will give the Recommended Setting for MyISAM Key Cache (key_buffer_size) given your current data set (the query will cap the recommendation at 4G (4096M)).For 32-bit OS, 4GB is the limit. For 64-bit, 8GB.

FULLTEXT Indexes

You should change the stopword list. You may want to change the stop words because MySQL will not index this list of 643 words. Try creating your own stopword list and changing the min word length.

Step 1) Create a stop word list of your own. You could add '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 2) Add these options to /etc/my.cnf

ft_min_word_len=2
ft_stopword_file=/var/lib/mysql/custom_stopwords.txt

Step 3) service mysql restart

Step 4) Create new FULLTEXT indexes. Any existing FULLTEXT indexes before restart of mysql should be reindexed.

Give These Ideas a Try !!!