Master MySQL Data Types and Index Optimization for Faster Queries

Data Type Optimization

MySQL provides several integer types— tinyint (8 bits), smallint (16 bits), mediumint (24 bits), int (32 bits) and bigint (64 bits). The signed range of tinyint is –128 to 127; tinyint unsigned stores 0 to 255. The display width (e.g., int(11)) only affects formatting, not storage.

For real numbers, float (4 bytes) and double (8 bytes) use IEEE‑754 approximations, while decimal(p,s) stores exact values. decimal(18,9) occupies 9 bytes: 4 bytes for the integer part, 1 byte for the sign/scale, and 4 bytes for the fractional part. MySQL 5.0+ can store nine‑digit numbers in four bytes.

String storage differs between varchar and char. varchar stores variable‑length data with a 1‑ or 2‑byte length prefix and trims trailing spaces. char is fixed‑length; MySQL removes trailing spaces on storage but pads them on comparison. For short strings, char can be more efficient (e.g., char(1) uses 1 byte vs. varchar(1) using 2 bytes).

Binary large objects ( blob) and text differ only in that blob stores raw bytes without a character set or collation.

Choosing optimized types

Prefer the smallest type that safely holds the required range.

Use integers for identifiers, IP addresses (store as unsigned 32‑bit int) and timestamps.

Avoid NULL when possible; replace with sentinel values (e.g., 0 or empty string) to simplify indexing.

Replace frequently queried strings with enum columns; MySQL stores the enum position internally, reducing storage and improving join performance.

Example: an enum column category with four fruit values stores only the position index, while the visible value remains a string.

Date and time types: datetime stores values from year 1001 to 9999 with second precision; timestamp stores Unix epoch seconds (1970‑01‑01 to 2038‑01‑19) in 4 bytes and is NOT NULL by default. Conversion functions from_unixtime() and unix_timestamp() bridge the two formats. For sub‑second precision, store epoch milliseconds in a bigint or fractional seconds in a double.

Identifiers should be integer primary keys with auto_increment to benefit from fast lookups and compact storage.

Index Optimization

Indexes (called "keys" in MySQL) enable fast row retrieval. A simple equality query such as SELECT fruit_name FROM fruit WHERE id = 5 uses the index on id to locate the matching row.

Storage Engines

MyISAM : table‑level locking, no transaction support, indexes cached in memory, stores rows sequentially.

InnoDB : ACID transactions, row‑level locking, foreign key support, clustered primary key, all indexes include the primary key, adaptive hash index, uses a buffer pool for caching.

Index Types

B‑tree : default index type; stores keys in sorted order, supports equality, range, and left‑most prefix queries. Limitations arise when the leftmost column is not used.

Hash index : only available with the Memory engine; not covered further.

R‑tree : spatial index for geometry types (MyISAM only).

Full‑text : MyISAM‑only, used with MATCH ... AGAINST for keyword search.

Prefix index : indexes only the first N characters of long varchar, text or blob columns to save space; reduces selectivity.

Example of a prefix index on a name column (5‑character prefix) achieving a selectivity of ~0.935:

Covering indexes store all columns needed by a query within the index, allowing MySQL to satisfy the query without reading the table rows. The EXPLAIN output shows Using index in the Extra column when this occurs.

Sorting can be performed by scanning an ordered index instead of performing a filesort. When type in EXPLAIN is "index", MySQL uses an index scan for ordering.

Avoid duplicate or redundant indexes; identical definitions or indexes that are left‑prefixes of a broader composite index waste space and can degrade performance.

Maintenance

CHECK TABLE

– detects corruption. REPAIR TABLE – fixes MyISAM tables. myisamchk – offline repair tool. ANALYZE TABLE – updates index statistics for the optimizer. SHOW INDEX – inspects index cardinality. OPTIMIZE TABLE (MyISAM) or ALTER TABLE … ENGINE=InnoDB – rebuilds the table to remove fragmentation.

Normalization vs. Denormalization

Normalized schemas store each fact once (e.g., separate teacher and school tables). Denormalization duplicates data to reduce join overhead (e.g., storing school_name in teacher). A hybrid approach can store pre‑aggregated counters (e.g., num_message in a user table) to avoid expensive counting queries.

Periodic table recreation can produce fresh, compact tables without fragmentation, useful for summary or cache tables that are refreshed on a schedule.

Overall, careful selection of data types, thoughtful index design, and regular maintenance are essential for high‑performance MySQL databases.