logo
g Text Version
Beauty & Self
Books & Music
Career
Computers
Education
Family
Food & Wine
Health & Fitness
Hobbies & Crafts
Home & Garden
Money
News & Politics
Relationships
Religion & Spirituality
Sports
Travel & Culture
TV & Movies

dailyclick
Bored? Games!
Nutrition
Postcards
Take a Quiz
Rate My Photo

new
Creativity
Family Travel
Southwest USA
Irish Culture
Home Finance
Comedy Movies
Romance Novels


dailyclick
All times in EST

Full Schedule
g
g ASP Site

BellaOnline's ASP Editor

g

MySQL Optimizing Tips

Guest Author - Lisa Shea

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!

Add MySQL+Optimizing+Tips to Twitter Add MySQL+Optimizing+Tips to Facebook Add MySQL+Optimizing+Tips to MySpace Add MySQL+Optimizing+Tips to Del.icio.us Digg MySQL+Optimizing+Tips Add MySQL+Optimizing+Tips to Yahoo My Web Add MySQL+Optimizing+Tips to Google Bookmarks Add MySQL+Optimizing+Tips to Stumbleupon Add MySQL+Optimizing+Tips to Reddit




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


For FREE email updates, subscribe to the ASP Newsletter


Past Issues


print
Printer Friendly
bookmark
Bookmark
tell friend
Tell a Friend
forum
Forum
email
Email Editor


Content copyright © 2014 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 BellaOnline Administration for details.

g


g features
Best uses for RSS

RSS and its future.

Force Download Dialog box with ASP

Archives | Site Map

forum
Forum
email
Contact

Past Issues
memberscenter


vote
Poetry
Daily
Weekly
Monthly
Less than Monthly



BellaOnline on Facebook
g


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


BellaOnline Editor