3. Look at your table design. Is it solid? Is it in third normal form? Are the column types
   the smallest they can be? Are there any variable length columns, if so, do you need
   them?


4. Look at the queries that are accessing your database. Are they using indexes? Are
   the WHERE clauses succinct?


5. Are your tables being maintained. When was the last time myisamchk was used? Do
   you have a cron job automatically checking your tables?


6. If everything else fails, look at compiling the source with a better compiler. This may
   help. Also look at purchasing the myisampack tool—this could also help increase
   performance.

There are many ways to increase performance. Unfortunately, it may take some time before you finally

figure out what does the trick. Hopefully, this day has provided you with some insights into the black art

of database optimization.

Q&A

Q: How can I set up a cron job to automatically maintain my tables?

A:

Normally, to use a cron job, an entry must be made in the crontab file.

This file tells the cron job what to do. The following entry should appear in a

crontab file to check and repair your database tables every week:

45 0 * * 0 /usr/local/mysql/bin/myisamchk –r

/usr/local/mysql/data/*/*.myi

Consult your operating systems documentation to see exactly what is

required for a crontab entry.

Q: What does the OPTIMIZE statement do?

A:

The OPTIMIZE statement is a lot like the myisamchk utility, but it is slower

and does not have as much functionality as the myisamchk utility.

However, it can be performed using an ODBC interface or any other

interface for that matter because MySQL uses it the same way as an SQL

statement. The syntax for the OPTIMIZE statement is as follows:

OPTIMIZE table_name

Where table_name is the name of the table you want to optimize.

Optimizing reclaims dead space. It does not repair damaged tables.

Exercises

1. Perform the recovery option of the myisamchk utility on all of the Meet-A-Geek
   tables.


2. Gather the specifications for a server to use for the Meet-A-Geek database,
   knowing that this database will be accessed around 10,000 times a day and is
   accessible via the internet. Keep in mind that MySQL runs best on Unix-based
   systems.

Day 21: Putting It All Together

Overview

You have learned a lot in these past three weeks. Today, you are going to review the steps you took to arrive
at your current location. Every application or project can be broken down into a series of steps that need to
be performed to accomplish the task at hand. That task could be creating a new database application to a
Web site. The series of steps that need to be taken to help ensure success are

1. Problem definition


2. Requirements analysis


3. Structure/Architecture design


4. Building/Coding


5. Testing


6. Implementation
   This lesson is going to take each step of the creation process and apply it to the Meet_A_Geek
   database. Hopefully, you will see how everything comes together by day's end.