Fixing MySQL Performance with Indexing

As you already know, using MySQL indexes can really improve performance dramatically. This is just a record of how dramatic that difference can be.

I had two tables that were used PHP program that someone else developed as seen below:

+---------------------------------+--------------+------+-----+---------------------+----------------+
| Field                           | Type         | Null | Key | Default             | Extra          |
+---------------------------------+--------------+------+-----+---------------------+----------------+
| customers_id                    | int(11)      | NO   | PRI | NULL                | auto_increment |
| customers_gender                | char(1)      | NO   |     |                     |                |
| customers_firstname             | varchar(32)  | NO   |     |                     |                |
| customers_lastname              | varchar(32)  | NO   |     |                     |                |
| customers_dob                   | datetime     | NO   |     | 0000-00-00 00:00:00 |                |
| customers_email_address         | varchar(96)  | NO   |     |                     |                |
| customers_default_address_id    | int(11)      | NO   |     | 0                   |                |
| customers_telephone             | varchar(32)  | NO   |     |                     |                |
| customers_fax                   | varchar(32)  | YES  |     | NULL                |                |
| customers_password              | varchar(40)  | NO   |     |                     |                |
| customers_newsletter            | char(1)      | YES  |     | NULL                |                |
| customers_group_id ...
more ...