MySQL index building performance

Question

I tried to build an index over a two columns of a 30,000,000 entry database. I canceled the process after ~60hr as it didn't seem to work.

For some reason MySQL takes only 22 mb ram instead of using the RAM fully. Is index building an operation that needs no Ram or is there some way to tell MySQL to use more RAM to be faster?

Answer 1

Read this article about MySQL server tuning: http://mysqldatabaseadministration.blogspot.com/2005/11/mysql-5-optimization-and-tuning-guide.html It's a tad outdated but still mostly relevant.

Long story short, yes, building an index requires ram and yes you have to tell MySQL to use more if you want it fast.

Try running this script to get a few quick tips: http://mysqltuner.pl/mysqltuner.pl

Answer 2

You should increase your innodb_buffer_pool_size if your table is InnoDB and sort_buffer_size if your table is MyISAM.