MySQL - database table design, structure and optimisation.
GENERAL TIPS ON DATABASE 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 database table, make the first column an ID for the records. That's 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 further 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 the EXPLAIN function and use additional indexes as required.
| 
