MySQL tweaks
April 28th, 2006 by aeI’ve spend the last half of the day trying to optimise (thats how you spell it in Australia) a MySQL script that was grabbing a heap of data to build a excel spreadsheet.
The script included four left joins and was selecting from about 20,000 rows. It had gradually become slower and slower over the last month as the database grew, until now it was taking over 30 seconds to run the script and make the excel file.
The first thing I did was add some indexes I had previously overlooked as the database changed. This brought down the time to 5 seconds.
I then changed the table types from MyISAM to Innodb. This brought it down to 0.2 seconds. This also fixed fragmentation in the database, so its not just the table type change that caused the speed increase.
Its amazing how easy it is to make databases unusable 
I’ve never enjoyed DB administration – in fact I dont recall ever meeting a DB administrator I’ve liked either Strange creatures….
Anyway:
- Be mindful of indexes within tables – they can dramatically effect query times
- Consider changing from MyISAM to Innodb if your database supports it
- De-frag your database at regular intevals
technorati tags: MySQL
No Comments » Comments to this post
Leave a Reply