Master MySQL Field Types, Indexes, and EXPLAIN for Faster Queries

Field Type Selection

Use the same data type for related tables to enable joins. Prefer integer identifiers over strings, choose smaller and simpler types, and avoid NULL columns because they are excluded from indexes.

Choosing Specific Types

VARCHAR : variable‑length string, length stored in 1‑2 bytes, max 65 535 bytes, trailing spaces preserved after MySQL 5.0, excess data is truncated silently.

CHAR : fixed‑length string, trailing spaces removed on write, no fragmentation.

Use VARCHAR when average length is far smaller than the maximum; use CHAR for fixed or very short lengths.

Integer types: TINYINT (8 bit), SMALLINT (16 bit), MEDIUMINT (24 bit), INT (32 bit), BIGINT (64 bit). Signed and unsigned occupy the same space; unsigned doubles the positive range.

Width specifications (e.g., INT(10) UNSIGNED) affect display only, not storage.

Binary and VARBINARY store raw bytes, padded with \0 not spaces.

BLOB and TEXT cannot be fully indexed or used for sorting; sorting uses max_sort_length bytes or SUBSTRING.

ENUM : defined with CREATE TABLE … ENUM('a','b','c') NOT NULL, stored in 1‑2 bytes, values appear as strings but can be cast to numbers; adding values requires ALTER TABLE, not suitable for uncertain categories.

Prefer ENUM for limited, known sets to save space, though joins on strings may be slower.

Date and Time Types

YEAR , DATE

DATETIME : 8 bytes, timezone‑independent, range 1001‑9999, second precision.

TIMESTAMP : 4 bytes, timezone‑aware, stores seconds since 1970‑01‑01; usually more space‑efficient than DATETIME.

Indexes

B‑Tree : supported by all engines except Archive.

Hash : Memory engine; can be built on top of B‑Tree by adding a hashed column (e.g., CRC32) – avoid cryptographic hashes.

R‑Tree : MyISAM (TODO).

FULLTEXT : MyISAM (TODO).

High‑performance strategy: isolate indexed columns from expressions and functions.

EXPLAIN Output

Key columns explained:

id : execution order (higher id runs later).

select_type : SIMPLE, PRIMARY, SUBQUERY, DEPENDENT SUBQUERY, DERIVED, UNION RESULT.

table : table referenced.

type : access method, from best to worst – const, system, eq_ref, ref, ref_or_null, range, index, ALL.

possible_keys : indexes MySQL could use.

key : index actually used (NULL if none).

key_len : bytes MySQL would use for the index; includes extra bytes for nullable and variable‑length columns and depends on character set.

ref : columns or constants compared with the key.

rows : estimated rows examined.

extra : additional info such as Using index (covering index), Using where, Using temporary, Using filesort, Not exists.

Limitations of MySQL EXPLAIN

Does not show effects of triggers, stored procedures, or user‑defined functions.

Ignores cache usage.

Cannot display optimizer transformations performed during execution.

Statistics are estimates, not exact counts.

Only explains SELECT statements; other statements must be rewritten as SELECT to view a plan.