Creating A Quick MySQL Relational Database

This articles explains how to create a MySQL database schema using a process known as normalisation. I utilise the three different relationship types found in relational databases which are one-to-one,one-to-many and many-to-many relationships then build SQL queries using table joins to retrieve our information. I also go on to talk about some really handy basic optimisation techniques which includes the use of indexes and de-normalisation techniques using triggers for a lightning fast database 

Creating A Database Schema

When you hear the words normalisation or normal form we are referring to the structure of database, the schema or blue print if you will. We create tables to help break up all our data as this builds a structure and makes it a lot easier to manage and understand what is going on.

Normalisation

The process of creating a database schema is know as normalisation. The idea is that you think up all the possible types of data you would like to collect for your database.

For example lets say we wanted to create a database for dogs and their breeds plus a number of recommended breeders. We'd start off with a whole list of fields that we require for our database in one big list:

dogName
dogDescr
dogBreed
dogBreedDescr
dogRfidMicroChipBarCode
dogRfidDate
dogBreederName
dogBreederAddress
dogBreederPhone

.etc..

Then you follow steps of normalisation to try and eliminate any repeating information and break up your data into tables. There are three forms/steps to this process.

First Normal Form

Firstly we eliminate repeating information and create separate tables for related data. In this instance it would be:

CREATE TABLE dog
(
name
descr
.etc..
);

CREATE TABLE breed
(
name
descr
.etc..
);

CREATE TABLE rfid_dog
(
barcode
date
.etc..
);


CREATE TABLE breeder
(
name
address,
phone
.etc..
);

Second Normal Form

No non-key attributes depend on a portion of the primary key.

In plain English, this means that if fields in your table are not entirely related to a primary key, you have more work to do.

Third Normal Form

No attributes depend on other non-key attributes.

This rule simply means that you need to look at your tables and see if more fields exist that can be broken down further and that aren't dependent on a key.

MySQL Database Schema

Ok once you've normalised your database by creating tables and defining keys .etc you need to look into forming relationships between the tables so you can then perform table joins to know things such as:

• what type of breed is that dog?
• what breeds of dogs can a particular breeder sell?

And now that we are going to break up all our data into separate tables the only way will be able get this information is by joining record(s) from one table to another, forming relationships between them, hence the term a relational database.

Quick Database Import

If you would prefer to dump the whole database onto your system instead following the steps below be download it here dump.sql (the SQL dump excludes any optimisation techniques discussed below, simply cut & paste the below SQL commands to implement).

Log onto MySQL and create the database:

CREATE database dogs;
quit;

Then import the downloaded file dump into your newly created database dogs:

machine:path user$ mysql dogs < dump.sql -u root -p

SQL Joins/Table Relationships

Types of relationships include one-to-one, one-to-many and many-to-many.

Will now create our database with examples of the three different types of table joins.

Important: please note any one who expects referential integrity to work in MySQL should NOT use MyISAM tables.

One To One

Each dog has a RFID (radio-frequency identification) microchip so it can by uniquely identified, only one microchip belongs to one dog an vice versa, hence the one to one relationship between the tables.

So will first create a table for our dogs, insert some data, then create a separate table for RFID using the field dog_id within the RFID table to insert the primary dog id and form the relationship between the two tables:

CREATE TABLE dog
(
  id int(11) NOT NULL auto_increment,
  name varchar(255),
  descr text,
  size enum('small','medium','large'),
  date timestamp(14),
  PRIMARY KEY (id)
);

INSERT INTO dog (name,descr,size,date) VALUES('Max','Its distinctive appearance and deep foghorn voice make it stand out in a crowd.','medium',NOW());
INSERT INTO dog (name,descr,size,date) VALUES('Jake','It loves human companionship and being part of the group.','medium',NOW());
INSERT INTO dog (name,descr,size,date) VALUES('Buster','Short-legged but surprisingly strong and agile.','small',NOW());


CREATE TABLE rfid_dog
(
  dog_id int(11) NOT NULL,
  bar_code varchar(128) NOT NULL,
  notes text,
  iso_compliant enum('y','n') DEFAULT 'n',
  date timestamp(14),
  PRIMARY KEY (dog_id)
);

INSERT INTO rfid_dog (dog_id,bar_code,notes,iso_compliant,date) VALUES('1','234k34340ll2342323022','This is a RFID tag for the Max','y',NOW());
INSERT INTO rfid_dog (dog_id,bar_code,notes,iso_compliant,date) VALUES('2','09383638920290397d829','This is a RFID tag for the Jake','y',NOW());
INSERT INTO rfid_dog (dog_id,bar_code,notes,iso_compliant,date) VALUES('3','30id8383837210jndal20','This is a RFID tag for the Buster','y',NOW());

So to get a list of dogs along with their corresponding RFID microchip you'll need to perform a table join on dog.id = rfid_dog.dog_id for the two tables using a one-to-one relationship like so:

SELECT dog.id, dog.name, rfid_dog.bar_code AS rfid
FROM dog,rfid_dog
WHERE dog.id = rfid_dog.dog_id
ORDER BY dog.name ASC;

+----+--------+-----------------------+
| id | name   | rfid                  |
+----+--------+-----------------------+
|  3 | Buster | 30id8383837210jndal20 | 
|  2 | Jake   | 09383638920290397d829 | 
|  1 | Max    | 234k34340ll2342323022 | 
+----+--------+-----------------------+
3 rows in set (0.00 sec)

One To Many

In this type of relationship you identify the table representing the many side of the relationship and add the primary key of the one side table to it.

So in this instance many dogs belong to one breed therefore we add the field breed_id to the dog table and perform a table join on dog.breed_id = breed.id to retrieve dogs and their corresponding breed.

So lets create the breed table, insert some records, alter the dog table adding the breed_id then update all the dogs with their corresponding breed id - I have purposely left out an INDEX on breed_id (see Using Indexes below example):

CREATE TABLE breed
(
  id int(11) NOT NULL auto_increment,
  name varchar(255),
  descr text,
  date timestamp(14),
  PRIMARY KEY (id)
);

INSERT INTO breed (name,descr,date) VALUES('Hounds','One of the oldest groups of dog originating thousands of years ago.',NOW());
INSERT INTO breed (name,descr,date) VALUES('Terrier','A tough, no-nonsense, rabbiting and badgering dog.',NOW());

ALTER TABLE dog
ADD breed_id int(11) AFTER date;

EXPLAIN dog;

+----------+--------------------------------+------+-----+-------------------+----------------+
| Field    | Type                           | Null | Key | Default           | Extra          |
+----------+--------------------------------+------+-----+-------------------+----------------+
| id       | int(11)                        | NO   | PRI | NULL              | auto_increment | 
| name     | varchar(255)                   | YES  |     | NULL              |                | 
| descr    | text                           | YES  |     | NULL              |                | 
| size     | enum('small','medium','large') | YES  |     | NULL              |                | 
| date     | timestamp                      | NO   |     | CURRENT_TIMESTAMP |                | 
| breed_id | int(11)                        | YES  |     | NULL              |                | 
+----------+--------------------------------+------+-----+-------------------+----------------+
6 rows in set (0.00 sec)

UPDATE dog SET breed_id = '1' WHERE id = '1';
UPDATE dog SET breed_id = '1' WHERE id = '2';
UPDATE dog SET breed_id = '2' WHERE id = '3';

To get a list of dogs and their breed we perform a table join on dog.breed_id = breed.id for the two tables using a one-to-many relationship like so:

SELECT dog.id,dog.name,breed.name AS breed
FROM dog,breed
WHERE dog.breed_id = breed.id
ORDER BY dog.name ASC;

+----+--------+---------+
| id | name   | breed   |
+----+--------+---------+
|  3 | Buster | Terrier | 
|  2 | Jake   | Hounds  | 
|  1 | Max    | Hounds  | 
+----+--------+---------+
3 rows in set (0.00 sec)

Many To Many

A relationship that is multi-valued in both directions is a many-to-many relationship. An breeder can have more than one dog, and a dog can have more than one breeder.

This type of relationship is helped by the use of a linking table breed__breeder.

So first let's create a table for our breeders, insert some records, then create a linking table called breed__breeder which will help us join relationships between the two tables breed and breeders, then insert existing primary id's of both breeds and their breeders so we can make the relationship work:

CREATE TABLE breeder
(
  id int(11) NOT NULL auto_increment,
  name varchar(255),
  address text,
  state varchar(10),
  city varchar(40),
  phone varchar(28),
  email varchar(28),
  date timestamp(14),
  PRIMARY KEY (id)
);

INSERT INTO breeder (name,address,state,city,phone,email,date) VALUES('Joe Bloggs','23 Smith St','NSW','Sydney','02 7875 4545','joe@email.com',NOW());
INSERT INTO breeder (name,address,state,city,phone,email,date) VALUES('Tom Smith','11 Tucker St','QLD','Brisbane','07 023 2343','tom@email.com',NOW());


CREATE TABLE breed__breeder 
(
  breed_id int(11) DEFAULT '0' NOT NULL,
  breeder_id int(11) DEFAULT '0' NOT NULL,
  PRIMARY KEY (breed_id, breeder_id)
);

INSERT INTO breed__breeder (breed_id,breeder_id) VALUES('1','1');
INSERT INTO breed__breeder (breed_id,breeder_id) VALUES('2','1');
INSERT INTO breed__breeder (breed_id,breeder_id) VALUES('1','2');

To obtain a list of breeders and what breed of dogs they breed we perform a table join on breed__breeder.breed_id = breed.id AND breed__breeder.breeder_id = breeder.id for the three tables using a many-to-many relationship like so:

SELECT breeder.name,breeder.address,breeder.phone,breeder.email,breed.name AS breedName
FROM breed,breeder,breed__breeder
WHERE breed__breeder.breed_id = breed.id
AND breed__breeder.breeder_id = breeder.id
ORDER BY breeder.name ASC;

+------------+--------------+--------------+---------------+-----------+
| name       | address      | phone        | email         | breedName |
+------------+--------------+--------------+---------------+-----------+
| Joe Bloggs | 23 Smith St  | 02 7875 4545 | joe@email.com | Hounds    | 
| Joe Bloggs | 23 Smith St  | 02 7875 4545 | joe@email.com | Terrier   | 
| Tom Smith  | 11 Tucker St | 07 023 2343  | tom@email.com | Hounds    | 
+------------+--------------+--------------+---------------+-----------+
3 rows in set (0.02 sec)

And there you have you've created a MySQL Relational database which uses a one to one, one to many and many to many relationships between tables.

General Optimisation Techniques

Using Indexes And Getting Familiar With EXPLAIN

Theses two points are the most important aspects to optimising your database.

Indexes

When talking about defining keys for a table it's very much like talking about an index in a book. As with an index in a book you simply look to the index to quickly find what information you after instead of scanning through the whole book page at a time. The same can be said for when you create indexes on a table in your database.

Using EXPLAIN

In order to retrieve any useful information about your queries you'll need to throw EXPLAIN in front of it which will provide you with a whole bundle of useful information:

• Table - Which table(s) are used in the query
• Type - JOIN type. Values can be system, const, eq_ref, ref, range, index, all
• Possible Keys - All keys which can be used for indexes
• Key - The key actually used for index
• Key Length - Shorter keys are better
• Ref - Other Columns used with key to get the results
• Rows - Number of rows from where data will come
• Extra - Extra information. Some possible values can be using index, using where, using temporary, using filesort

Let test this on our existing database and see what useful information it gives us. Take for instance the following SELECT query:

SELECT dog.id,dog.name,breed.name AS breed
FROM dog,breed
WHERE dog.breed_id = breed.id
AND breed.id = 1;

+----+------+--------+
| id | name | breed  |
+----+------+--------+
|  1 | Max  | Hounds | 
|  2 | Jake | Hounds | 
+----+------+--------+
2 rows in set (0.00 sec)

By using EXPLAIN in front of our SELECT query we can review a lot of useful information that will help us optimise it. Take note for one on the 'type' field, on the dog table is has returned 'ALL', that means it has had to perform a complete table scan on all rows, no indexes found! This is reflected in the number of rows returned too which total's 4:

EXPLAIN SELECT dog.id,dog.name,breed.name AS breed
FROM dog,breed
WHERE dog.breed_id = breed.id
AND breed.id = 1;

+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | breed | const | PRIMARY       | PRIMARY | 4       | const |    1 |             | 
|  1 | SIMPLE      | dog   | ALL   | NULL          | NULL    | NULL    | NULL  |    3 | Using where | 
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
2 rows in set (0.00 sec)

Given that we know there are no INDEXES available on the dog table but an obvious join (dog.breed_id = breed.id) between the two tables lets create one on breed_id:

SHOW INDEXES FROM dog;

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| dog   |          0 | PRIMARY  |            1 | id          | A         |           3 |     NULL | NULL   |      | BTREE      |         | 
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)

CREATE INDEX breed_id ON dog (breed_id);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

SHOW INDEXES FROM dog;

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| dog   |          0 | PRIMARY  |            1 | id          | A         |           3 |     NULL | NULL   |      | BTREE      |         | 
| dog   |          1 | breed_id |            1 | breed_id    | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         | 
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.00 sec)

Now run the query again and see what optimisation has occurred, -2 rows returned and no full table scan on the dog table :):

EXPLAIN SELECT dog.id,dog.name,breed.name AS breed
FROM dog,breed
WHERE dog.breed_id = breed.id
AND breed.id = 1;

+----+-------------+-------+-------+---------------+----------+---------+-------+------+-------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref   | rows | Extra       |
+----+-------------+-------+-------+---------------+----------+---------+-------+------+-------------+
|  1 | SIMPLE      | breed | const | PRIMARY       | PRIMARY  | 4       | const |    1 |             | 
|  1 | SIMPLE      | dog   | ref   | breed_id      | breed_id | 5       | const |    1 | Using where | 
+----+-------------+-------+-------+---------------+----------+---------+-------+------+-------------+
2 rows in set (0.00 sec)

I know small numbers but this database only contains a small handful of records, imagine if we had 1000's or 100 000's of records and our website was receiving 1000's of hits every day, that query would be running overtime. But now that it is optimised you will be saving vast amounts of valuable system resources.

Stored Procedures

The difference between stored procedures and other sets of SQL statements is that stored procedures reside on the server and are pre-compiled.

Ok this may be a little overkill for the following query but it's an example of how you can use a stored procedure to further optimise your database:

• Stored procedures are faster because they are pre-compiled SQL code. This reduces the compile and execute step to just execute in most cases.
• However, the load on the server is another point to consider since most of the processing will now be done on the server.

Lets run with the same query we optimised above and stick it into a stored procedure:

DELIMITER //
CREATE PROCEDURE dog_breed(IN the_breed_id int(11))
COMMENT 'Display a list of dogs and their breed' 
BEGIN
SELECT dog.id,dog.name,breed.name AS breed
FROM dog,breed
WHERE dog.breed_id = breed.id
AND breed.id = the_breed_id;
END //
DELIMITER ;

It's been added. Now to view all your available stored procedures please run the following query:

SHOW PROCEDURE STATUS;
+------+-----------+-----------+----------------+---------------------+---------------------+---------------+----------------------------------------+
| Db   | Name      | Type      | Definer        | Modified            | Created             | Security_type | Comment                                |
+------+-----------+-----------+----------------+---------------------+---------------------+---------------+----------------------------------------+
| dogs | dog_breed | PROCEDURE | root@localhost | 2009-05-11 16:04:17 | 2009-05-11 16:04:17 | DEFINER       | Display a list of dogs and their breed | 
+------+-----------+-----------+----------------+---------------------+---------------------+---------------+----------------------------------------+
1 row in set (0.00 sec)

Calling your stored procedure will execute the SQL statements and you pass it the_breed_id parameter, in this case the breed hounds (ID 1):

CALL dog_breed('1');

De-normalisation Using Triggers

Keeping your database normalised helps us make more sense of the data but this encroaches on performance as we now have to retrieve data from different tables (relational database) - the key to a high performance database access is sticking to single-table SELECT queries with short indexes.

A de-normalised database is accomplished by keeping redundant frequently-accessed data, duplicated across several tables, into one single table making it possible to full fill a user request without having to look at more than a single table.

For example to get the total number breeds of dog for a particular breeder say Joe Bloggs we currently have to perform a table join on the table breed__breeder (many-to-many) and use the primary id on the breeders table breeder.id = 1 to find Joe Bloggs:

SELECT COUNT(breed.id) AS total
FROM breed, breeder, breed__breeder
WHERE breed__breeder.breed_id = breed.id
AND breed__breeder.breeder_id = breeder.id
AND breeder.id = 1;

If this type of query which performs table joins on three different tables is regularly run then a performance option would be to put it into a single table 

So we can alter the breeder table by adding the field breed_total in which will keep the total amount of breeds for each breeder, this way we only have to index the one table:

ALTER TABLE breeder
ADD breed_total int(11) default 0
AFTER date;

Now firstly lets manually update the current breed_total value for each breeder into the newly created breed_total field for the existing entries:

UPDATE breeder SET breed_total = 2 WHERE id = 1;
UPDATE breeder SET breed_total = 1 WHERE id = 2; 

I know what your thinking, then how to we keep the breed_total values current for each breeder? Triggers are your answer! I'll explain...

A trigger is defined to activate when a particular kind of event occurs for a given table. The events for which triggers can be defined are INSERT, DELETE, and UPDATE. A given trigger is defined for only one of these events, but you can define multiple triggers for a table, one trigger per type of event.

Given that there is a direct relationship between breed and breeder using the table breed__breeder we can create some triggers to populate the breed_total field like so...

We need to create a trigger for every time a record is inserted, deleted or updated on the breed__breeder table and update the breed_total field on the breeder table:

# on insert + 1 with NEW breeder record
DELIMITER |

CREATE TRIGGER breed__breeds_insert AFTER INSERT ON breed__breeder 
FOR EACH ROW 
BEGIN
UPDATE breeder SET breed_total = breed_total + 1 WHERE id = NEW.breeder_id;
END;
|

DELIMITER ;

# on delete - 1 with OLD breeder record
DELIMITER |

CREATE TRIGGER breed__breeds_delete AFTER DELETE ON breed__breeder 
FOR EACH ROW 
BEGIN
UPDATE breeder SET breed_total = breed_total - 1 WHERE id = OLD.breeder_id;
END;
|

DELIMITER ;


# on update only if breed_id has changed, - 1 OLD & + 1 NEW
DELIMITER |

CREATE TRIGGER breed__breeds_update AFTER UPDATE ON breed__breeder 
FOR EACH ROW 
BEGIN

IF OLD.breeder_id != NEW.breeder_id
THEN
UPDATE breeder SET breed_total = breed_total - 1 WHERE id = OLD.breeder_id;
UPDATE breeder SET breed_total = breed_total + 1 WHERE id = NEW.breeder_id;
END IF;

END;
|

DELIMITER ;

To see your newly created triggers run:

SHOW TRIGGERS;

+----------------------+--------+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+---------+----------+----------------+
| Trigger              | Event  | Table          | Statement                                                                                                                                                                                                        | Timing | Created | sql_mode | Definer        |
+----------------------+--------+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+---------+----------+----------------+
| breed__breeds_insert | INSERT | breed__breeder | BEGIN
UPDATE breeder SET breed_total = breed_total + 1 WHERE id = NEW.breeder_id;
END                                                                                                                            | AFTER  | NULL    |          | root@localhost | 
| breed__breeds_update | UPDATE | breed__breeder | BEGIN
IF OLD.breeder_id != NEW.breeder_id
THEN
UPDATE breeder SET breed_total = breed_total - 1 WHERE id = OLD.breeder_id;
UPDATE breeder SET breed_total = breed_total + 1 WHERE id = NEW.breeder_id;
END IF;
END | AFTER  | NULL    |          | root@localhost | 
| breed__breeds_delete | DELETE | breed__breeder | BEGIN
UPDATE breeder SET breed_total = breed_total - 1 WHERE id = OLD.breeder_id;
END                                                                                                                            | AFTER  | NULL    |          | root@localhost | 
+----------------------+--------+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+---------+----------+----------------+
3 rows in set (0.01 sec)

Now lets lets look at the performance gain with selecting the breed_total for a particular breeder by only having to index the one table against the old select query which indexes multiple tables:

EXPLAIN SELECT breed_total AS total
FROM breeder
WHERE id = 1
ORDER BY name ASC;

+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table   | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | breeder | const | PRIMARY       | PRIMARY | 4       | const |    1 |       | 
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)


EXPLAIN SELECT COUNT(breed.id) AS total
FROM breed, breeder, breed__breeder
WHERE breed__breeder.breed_id = breed.id
AND breed__breeder.breeder_id = breeder.id
AND breeder.id = 1;

+----+-------------+----------------+--------+---------------+---------+---------+---------------------+------+-------------+
| id | select_type | table          | type   | possible_keys | key     | key_len | ref                 | rows | Extra       |
+----+-------------+----------------+--------+---------------+---------+---------+---------------------+------+-------------+
|  1 | SIMPLE      | breeder        | const  | PRIMARY       | PRIMARY | 4       | const               |    1 | Using index | 
|  1 | SIMPLE      | breed          | index  | PRIMARY       | PRIMARY | 4       | NULL                |    2 | Using index | 
|  1 | SIMPLE      | breed__breeder | eq_ref | PRIMARY       | PRIMARY | 8       | dogs.breed.id,const |    1 | Using index | 
+----+-------------+----------------+--------+---------------+---------+---------+---------------------+------+-------------+
3 rows in set (0.00 sec)

The difference in performance can been seen by the amount of rows returned with only 1 row in the first query against 4 rows in the second query. Now this figure will grow considerably as more data is populated into our database, the performance again is MASSIVE!

Show Processlist

This will show you the current running processes (queries) in order to get a snap shot of the queries the server is busy doing right now! It's important to make sure you specify to show full processlist in order to see the full query. If the MySQL user you are logged in as has process privileges then you'll see all threads or else only your own threads, that is, threads associated with the MySQL account you are using.

If you've ever run into too many connections with MySQL viewing the 'processlist' is a good way to find out what's going on. You can then run EXPLAIN in front of any queries to dig a little deeper.

SHOW FULL PROCESSLIST;

+----------+-----------+-----------------+-----------------+---------+-------+----------------------+------------------------+
| Id       | User      | Host            | db              | Command | Time  | State                | Info                   |                                                                                                                                             
+----------+-----------+-----------------+-----------------+---------+-------+----------------------+------------------------+
| 11262963 | root      | localhost       | mysql           | Query   |     0 | NULL                 | show full processlist  |
| 11263781 | example   | localhost       | example_db      | Query   |     2 | statistics           | SELECT * FROM table    |
+----------+-----------+-----------------+-----------------+---------+-------+----------------------+------------------------+

Slow Query Log

The slow query log consists of all SQL statements that took more than long_query_time seconds to execute.

This setting is disabled by default, to see if it's enabled run:

mysqladmin var |grep log_slow_queries
| log_slow_queries                | OFF                          |

If log_slow_queries is ON then we are good to go.

To enable the general query log, start mysqld with the --log[=file_name] or -l [file_name] option.

Surrogate Keys

Don't use surrogate keys when naturally occurring primary key already exists - basically don't use auto increment id field if an natural id already exists

Query Cache

If you are using read intensive applications turn the query cache on.. Why, if it's the exact same query and your data set hasn't changed it'll just take it from the cache.

The query cache contains the actual rows (the result set) and not the SQL query itself, so if it's turned on it'll just give the rows straight back without having to do any optimisation or parsing.

It is not turned on by default so the size is 0 by default. Use 'SHOW STATUS' to find out, if it says query type 'on' if there is no size to it won't be used.

Physical Memory

RAM (physical computer memory) is the fastest cheapest way to improve performance with MySQL.

Conclusion

You now have a fair understanding of how to construct a MySQL relational database using the three well known table relationships, one-to-one, one-to-many and many-to-many. And you also have a list of optimisation techniques to use to help speed things up.