Speeding Up MySQL (instead of using sqlite)
Many people use sqlite for testing purposes because it's fast... and it's fast because it uses memory for many operations instead of the disk... the down side is that you need to use another module and any custom SQL needs re-writing... so what's the solution?
Mount a drive using memory and switch the data and temp folders for MySQL to use that drive... here is a script for windows to do just that... (this needs to be run as an administrator)...
imdisk -a -t vm -s 2G -m G: format G: /fs:ntfs pause G: mkdir mysql cd mysql mkdir temp mkdir data cd data robocopy C:\xampp\mysql\data G:\mysql\data /e pause
You can also add the following to compress the new disk to save space if you don't have much RAM to allocate to this.
compact /I /Q /F /S G:
Then modify these two settings in my.ini
datadir = "G:/mysql/data" tmpdir = "G:/mysql/temp"
Where "G" is the drive built with the previous script.
This might not suit everyone as the goal is to use small databases and it is volatile which is why there is a script each time the machine is powered off it loses all the data and the script needs re-running.
ImDisk can be found here and also has UNIX versions.
I welcome a script for UNIX either comment here or email us and it will be added :)