You probably ask yourself sometimes how you can boost MySQL indexes when you are working with BIG databases/tables.
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 a 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.
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 mysql server for the changes take effect.
3. Finally you can run the following query to alter 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 rebuild and you are ready to work with your faster database!
You are welcome to publish your benchmarks.