You probably ask yourself sometimes how you can boost MySQL indexes when you are working with BIG databases/tables.
The original article was published on codeboost.com website.
Really the solution is here for a long time already. You can use SSD disks (also known as flash disks). These disks are not that big that traditional SATA hard drives but they provide superior performance. If your database is rather big to be placed on SSD disk you can still place your database indexes onto it. So, your data will be saved on an old disk and the index will be placed in faster SSD disk.
If can be easily done.
1. You need to create a directory on your SSD disk that will be used for MySQL indexes. In addition, you need to give to MySQL service write permission to that directory.
mkdir /ssd/mysql-indexes
mkdir /ssd/mysql-indexes/my-big-db-name
chmod 700 /ssd/mysql-indexes
chown –R mysql:mysql /ssd/mysql-indexes
2. OPTIONAL STEP:
Here is another tip shared with me by another MySQL expert. Add the following line to [mysqld] configuration section in MySQL. It will speed up the index rebuild operation in a number of times if you work with MyISAM tables. You need to restart the MySQL server for the changes take effect.
[mysqld]
myisam_repair_threads=10
3. Finally, you can run the following query to alter the location of the index files.
ALTER TABLE my-table INDEX DIRECTORY= '/ssd/mysql-indexes/my-big-db-name';
After that, you will have to wait some time till MySQL indexes will be rebuilt and you are ready to work with your faster database!
You are welcome to publish your benchmarks.
Comments from Ammnon Sutherland:
Oddly, I was reminded of this just last night and planning to put it into practice this next week. You can also put heavily used or massive range-scan prone tables (including InnoDB tables) on disks by specifying the 'datadir' as part of the create table or alter table syntax.
Comment from Jan Steinman
Rather than rebuild the indices -- which could take some time -- couldn't you simply shut down the database, copy them to the SSD, then symlink them back to the data directory? I guess that would only work with MyISAM, since InnoDB doesn't have separate index files.
Comment from Egor Shevtsov
Very nice. I do try to use MyISAM engine as least as possible specifically with Big tables as it's not crash safe and takes long to repair. But I learned some nice tricks today. Many thanks. mkdir -p /ssd/mysql-indexes/my-big-db-name # in one go