Essential MySQL Tips: Data Types, Indexes, and Query Optimization

MySQL Characteristics

MySQL separates storage and processing via storage engines; the default engine InnoDB provides transaction support, row‑level locking, tablespace storage, MVCC concurrency, clustered primary‑key indexes, hot backup, and more. Other engines such as MyISAM, Archive, CSV, and Memory have distinct trade‑offs.

Data Type Optimization

Guiding principles: choose the smallest space‑efficient type, prefer simple types, and avoid unnecessary nullable columns because they increase storage and index size.

Integer Types

Supported sizes are tinyint (8 bits), smallint (16 bits), mediumint (24 bits), int (32 bits), and bigint (64 bits). They store values from –2ⁿ⁻¹ to 2ⁿ⁻¹‑1; adding unsigned doubles the positive range. Specifying display length has no effect.

Decimal Types

decimal

stores exact numbers using 4 bytes per 9 digits (e.g., decimal(18,9) occupies 9 bytes). Because it is space‑heavy, consider using bigint with scaling for large volumes.

String Types

varchar

: length + 1/2 bytes for length prefix; ideal when column values vary widely. char: fixed‑length, best for uniformly short strings. varbinary / binary: binary equivalents of varchar / char, case‑sensitive and encoding‑agnostic. blob / text: stored off‑page with a pointer; avoid indexing large columns. enum: stores a numeric index for a predefined set, saving space for low‑cardinality strings.

Time Types

year

, date, time, datetime (1001‑9999, second precision), timestamp (seconds since 1970‑01‑01, 4 bytes, auto‑updates on INSERT/UPDATE).

Primary‑key recommendation: use an integer (auto‑increment) rather than GUID or MD5 strings, because InnoDB clusters data on the primary key.

Special type tip: store IPv4 addresses as an unsigned 32‑bit integer and convert with INET_ATON() / INET_NTOA().

Index Optimization

InnoDB implements indexes with B‑Tree structures. Example table:

CREATE TABLE `people` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(5) NOT NULL,
  `Age` tinyint(4) NOT NULL,
  `Number` char(5) NOT NULL COMMENT '编号',
  PRIMARY KEY (`Id`),
  KEY `i_name_age_number` (`Name`,`Age`,`Number`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;

Index column order matters: MySQL uses the leftmost prefix, so queries should match the index order from left to right.

Good SQL examples (use leftmost columns, avoid leading wildcards):

SELECT * FROM people WHERE Name='Abel' AND Age=2 AND Number=12312;
SELECT * FROM people WHERE Name='Abel';
SELECT * FROM people WHERE Name LIKE 'Abel%';
SELECT * FROM people WHERE Name='Andy' AND Age BETWEEN 11 AND 20;
SELECT * FROM people ORDER BY Name;
SELECT * FROM people ORDER BY Name, Age;
SELECT * FROM people GROUP BY Name;

Bad SQL examples (skip leftmost column, leading wildcard, mismatched order):

SELECT * FROM people WHERE Age=2;
SELECT * FROM people WHERE NAME LIKE '%B';
SELECT * FROM people WHERE NAME='ABC' AND number=3;
SELECT * FROM people WHERE NAME LIKE 'B%' AND age=22;

Techniques

Hash index : create a numeric hash column (e.g., URL_CRC) for long string columns and index the hash.

Prefix index : index only the first N characters of long string columns; choose N to balance selectivity and size.

Multi‑column index : combine columns that are frequently queried together; place the most selective column first unless query patterns dictate otherwise.

Clustered index : InnoDB’s primary key is clustered; only one per table.

Covering index : include all columns needed by a query in the index to avoid table lookups.

Duplicate / redundant index : avoid creating both (A,B) and (A); the former can serve the latter.

Unused index : drop indexes that are never used, as they hurt INSERT/UPDATE/DELETE performance.

Query Optimization

Common causes of slowness:

Fetching more rows than needed.

Fetching more columns than needed (e.g., SELECT *).

Repeated execution of identical queries without caching.

Scanning unnecessary rows; use EXPLAIN to detect.

Refactoring Strategies

Break complex queries into several simpler ones.

Split large queries into smaller batches.

Decompose joins: fetch related tables separately and combine in application code.

Miscellaneous Optimizations

COUNT() : use

SELECT IFNULL((SELECT 1 FROM tableName WHERE condition LIMIT 1),0)

to test existence instead of counting rows.

Replace subqueries with joins for MySQL 5.5 and earlier.

Prefer GROUP BY / DISTINCT on primary keys when possible.

Optimize LIMIT with covering indexes, e.g.:

SELECT * FROM sa_stockinfo i
JOIN (SELECT StockInfoID FROM sa_stockinfo ORDER BY StockAcc LIMIT 400,5) t
ON i.StockInfoID = t.StockInfoID;

Use UNION ALL instead of UNION when duplicate elimination is unnecessary.

Reference

"High Performance MySQL" (O'Reilly) – recommended reading for deeper insight.