I have to do some updates on myisam tables which have around 20 M records. Our server has 24GB of ram and 2 quad core xeon with 24 MB of cache. I want to know how I can change the my.ini file so I get the best performance. I have windows 7 64 bit.


You will need to check out the sum total of your indexes for MyISAM. Please run this query:

SUBSTR(' KMG',IF(PowerOf1024<0,0, 
recommended_key_buffer_size FROM 
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 PowerOf1024) B; 

Whatever number comes out, use either that number or 8G, whichever is smaller.

MyISAM only caches index pages.

If you plan to migrate your data to InnoDB, use these settings:


You wiil need to optimize the InnoDB infrastructure

| improve this answer | |
  • Thanx, I got a littel more than 1000. One question, I had innodb before and I set innodb_buffer_pool_size to 16GB and it really used 16GB of my ram. Which one is faster if I only want to update these tables? – AliBZ Dec 21 '11 at 23:40
  • If all tables were InnoDB, you could update data faster. You can also engage more CPUs using InnoDB. – RolandoMySQLDBA Dec 21 '11 at 23:45
  • Does removing the FK s from my innodb tables make updating faster? – AliBZ Dec 21 '11 at 23:47
  • Definitely faster without Foreign Keys. – RolandoMySQLDBA Dec 21 '11 at 23:50
  • 1
    If this wasn't MySQL I'd say don't remove FKs for performance because you sacrifice data integrity. If it wasn't MySQL I'd downvote too... – gbn Dec 22 '11 at 6:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.