GENERAL TIPS ON TABLE DESIGN ::

Terminology --

A 'field' in MySQL is equivalent to a 'column' in Excel.

A 'record' is equivalent to a 'row' in Excel.

Note that MySQL is case sensitive regarding table names and fields - 'City' is not the same as 'city'

MySQL uses database.tablename as a way of referencing a database and a table. For this reason, *don't* use a dot(.) when naming databases or tables, and always use lowercase names.

---    ---   ---

When creating a new table, make the first column an ID for the records. That's much easier than trying to alter things around later on ;-)

Example - for a database table with an ID column:

INT
UNSIGNED
NOT NULL
PRIMARY KEY
AUTO_INCREMENT

INT = Integer
UNSIGNED = Can only be zero or a positive number
NOT NULL = Should contain a value.
PRIMARY KEY = All entries will have to be unique (see next)
AUTO INCREMENT = MySQL will take care of this for you, together with ensuring that the incremental number is unique.

The PRIMARY KEY also means that MySQL will use this column as an index. An index is used to speed up queries.

Without an index, MySQL will start searching at the beginning of the table and scan the entire contents in order to deliver your query.

A table with 1000 rows and an index, will deliver results faster than the same table WITHOUT an index. If the same table grew to 300,000 rows ... that would be considerably more to scan (especially if you only need to return a few results from your query).

OK - so why not just index all columns?

Indexes on any furthur columns should only be installed when they are to be used in your queries. MySQL has a built in feature called EXPLAIN that allows you to test and optimise your table design.

The EXPLAIN function details which indexes are being used in any particular query. If an additional index is not being used in any of your queries, then you don't need it.

Insufficient indexes = database not optimised
Excessive  indexes = database not optimised

That all said, and to keep things in perspective, installing extra indexes is unlikely to be crucial on small tables.

Best practice of course though, is to become familiar with EXPLAIN and use indexes as required.

Things to watch out for: