g
Printer Friendly Version

editor  
BellaOnline's ASP Editor
 

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.

Queries
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:

table_cache=1250
key_buffer_size=64M
sort_buffer_size=4M
read_buffer_size=1M

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 -

innodb_buffer_pool_size=300M
innodb_log_file_size=100M

This site needs an editor - click to learn more!

ASP Site @ BellaOnline
View This Article in Regular Layout

Content copyright © 2013 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 Editor Wanted for details.



| About BellaOnline | Privacy Policy | Advertising | Become an Editor |
Website copyright © 2014 Minerva WebWorks LLC. All rights reserved.


BellaOnline Editor