I’m working on a project with a huge MySQL database and for that reason, I was looking out for some mysql performance tips.
One information I found in nearly every blog was using the slow_query function from mysql.
Put that in your my.cnf:
log-slow-queries=/tmp/slow_queries.log
long_query_time=10
After that you will see all querys that take longer than 10 sec. in the /tmp/slow_queries.log.
To see what you can optimize at this point, you can use EXPLAIN.
So that’s something you find everywhere. Something else you can see everywhere is to activate the query cache. If you are using mysql 5 > this should be enabled by default.
But there are these simple and little optimization points you should use everyday to bring you database on the speedway.
- Use only the datatypes you really need. Numbers should be saved as int. And text should be saved in the right type.
- If you’re working with numbers always use only the number no quotes around it.
select * from bla where id = 123. Otherwise the server will have to cast the number and will work with a string. - Don’t use rand()