MySQL Best Tips for Web Development

Please visit  mysql site for more..

Good database design begins, naturally, with normalizing the database.
Normalization and performance mean that you also:
■ Use the smallest possible column types.
■ Avoid storing NULL values as much as possible.
■ Use fixed-length columns when you can.
■ Provide default values for columns, if applicable.




Performance is also greatly affected by using indexes properly. Declaring
indexes is somewhat of an art, but some general rules are:
■ Index columns that will be involved in WHERE and ORDER BY clauses.
■ Avoid indexing columns that allow NULL values.
■ Apply length restrictions to indexes on variable-length columns, such as
indexing only the first 10 characters of a person’s last name.
■ Use EXPLAIN queries to confirm that indexes are being used.
■ Revisit your indexes after some period of site activity to ensure they are still
appropriate to the real-world data.



A final consideration in your database design, which gets less attention, is the
storage engine (or table type) in use. One of MySQL’s strengths is its support
for multiple storage engines, meaning you can select the one whose features
best match your needs. For example, you can create MySQL tables in memory,
which will perform exceptionally well but provide no data permanence. The
two most common MySQL storage engines are InnoDB and MyISAM. The
former is the default type for Windows computers and the latter is the default
for all other operating systems. MyISAM is an excellent, all-purpose storage
engine that also supports FULLTEXT indexes, useful in searches. The InnoDB
engine doesn’t support FULLTEXT but can handle transactions, an excellent
fail-safe in sensitive situations.



If you have administrative-level control over your database, there are a number
of configurations that impact MySQL’s performance. To start, there is back_log,
key_buffer_size, max_connections, and thread_cache_size. You can use a
configuration file to change these settings from their defaults to values more
appropriate to your server and site. See the MySQL manual for more information
for the version of MySQL that your server is running—assuming that you
have that kind of control over your server, of course.



Should you get to a point where your site is so active that multiple servers are
appropriate, you can consider replicating the database. Database replication
stores the same data on more than one server. By doing so you’ll get improved
security, reliability (should one server fail, the data still lives on elsewhere),
and performance.