MySQL Optimizing Tips

MySQL Optimizing Tips
If you are working with a MySQL database, it is important to optimize the database so it runs as quickly as possible. Here are some tips to help out with this task.

Disk Drive
Pretty much every MySQL query involves doing disk drive searches, i.e. roaming the disk drive looking at data. If your disk drive is busy doing other things - writing log files, handling other applications - this is going to be slow and involve a lot of jumping around. If you get a fast hard drive just for the MySQL data, then that hard drive's head can be very efficient at getting that data for you.

Naturally, if you write awful queries, they will take eons to run. Don't do a select against a giant table on a field that has no index. Definitely don't do "like" queries against large text fields! Tune your queries so that each one is as efficient as possible.

Server Memory Settings
There are a lot of settings in the my.ini file that you can edit. If you are running a relatively large database, here are four to look at:


The values there are ones I've used, but you can tune them based on the memory your system has. The table cache is the actual # of tables that can be open, while the other numbers are meg of memory available.

If you're running InnoDB for solid transactions - which I recommend - here are some settings to set -


Editor's Picks Articles
Top Ten Articles
Previous Features
Site Map

Content copyright © 2022 by Lisa Shea. All rights reserved.
This content was written by Lisa Shea. If you wish to use this content in any manner, you need written permission. Contact Lisa Shea for details.