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

MySQL Features

Understanding MySQL's characteristics helps you use it effectively. Unlike other databases, MySQL separates storage engines, each with its own traits, allowing you to choose or even develop a custom engine based on business needs.

MySQL's logical architecture is shown below:

InnoDB Storage Engine

Supports transactions

Supports row‑level locking

Data stored in tablespaces composed of multiple files

Uses MVCC for high concurrency

Primary‑key clustered index

Supports hot backup

Other Common Storage Engines

MyISAM – no transactions or row‑level lock, supports full‑text index, cannot recover after crash, supports compressed tables.

Archive – only INSERT and SELECT, fast bulk inserts, full‑table scans for queries.

CSV – treats a CSV file as a table.

Memory – stores data in memory.

Data Type Optimization

Principles: choose space‑efficient types, prefer simple types, avoid unnecessary nullable columns.

Integer Types

tinyint (8 bits)

smallint (16 bits)

mediumint (24 bits)

int (32 bits)

bigint (64 bits)

Unsigned integers double the positive range. Specifying length for integer types has no effect.

Decimal Types

Decimal provides exact arithmetic but consumes more space (4 bytes store 9 digits). For large datasets consider using bigint with scaling.

String Types

varchar – length + 1 or 2 bytes, space‑saving for variable‑length data.

char – fixed length, suitable when values are similar length.

varbinary / binary – binary strings, case‑sensitive, faster comparisons.

blob / text – large binary or text data stored off‑page, indexed only by prefix.

enum – stores a numeric index for a limited set of strings, saves space.

Time Types

year, date, time, datetime, timestamp

datetime covers 1001‑9999, timestamp stores seconds since 1970‑01‑01 and auto‑updates on INSERT/UPDATE.

Primary Key Choice

Prefer integer auto‑increment keys; avoid GUIDs or MD5 strings because they increase index size and fragment storage.

Index Optimization

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 order matters: MySQL uses left‑most prefix matching. Queries should match the index from left to right.

Good examples: 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 examples: SELECT * FROM people WHERE Age=2; SELECT * FROM people WHERE Name LIKE '%B'; SELECT * FROM people WHERE age=2; SELECT * FROM people WHERE NAME='ABC' AND number=3; SELECT * FROM people WHERE NAME LIKE 'B%' AND age=22;

A Trick Using Hash Values for Indexing

Create a column (e.g., URL_CRC) storing a numeric hash of a long string column and index it.

SELECT * FROM t WHERE URL_CRC = 387695885 AND URL = 'www.baidu.com';

SELECT CONV(RIGHT(MD5('www.baidu.com'),16),16,10);

Prefix Index

Index only the leading characters of long string columns to reduce index size; not usable for ORDER BY or GROUP BY.

Multi‑Column Index

Combine columns into a single index; place the most selective column first unless query patterns dictate otherwise.

SELECT * FROM t WHERE f1='v1' AND f2 <> 'v2' UNION ALL SELECT * FROM t WHERE f2='v2' AND f1 <> 'v1';

Clustered Index

InnoDB stores row data in the leaf nodes of the primary‑key index; only one clustered index per table.

Covering Index

If a query can be satisfied entirely from the index, MySQL avoids a second lookup.

Duplicate & Redundant Indexes

Avoid creating both a unique and a regular index on the same column; redundant indexes (e.g., (A) and (A,B)) should be consolidated.

Unused Indexes

Drop indexes that are never used to improve write performance.

Index Usage Summary

One star: index orders rows matching the WHERE clause.

One star: index order matches query ORDER BY.

One star: index contains all columns needed by the query (covering).

When data volume is huge, consider partitioning or sharding.

Query Optimization

Reasons for Slow Queries

Fetching unnecessary rows.

Fetching unnecessary columns (SELECT *).

Repeatedly executing the same query.

Scanning extra rows; examine the execution plan.

Refactor Query Approach

Break complex queries into simpler ones.

Split large queries into smaller batches.

Decompose joins into separate queries and combine results in application code.

Miscellaneous Optimizations

COUNT(): use approximations, covering indexes, summary tables, or cache counts.

Join optimization: index the foreign key column used for joining.

Subquery optimization: replace subqueries with joins in MySQL 5.5 and earlier.

GROUP BY / DISTINCT: apply on primary keys when possible.

LIMIT optimization: use index‑covering queries to avoid scanning unnecessary rows.

UNION: prefer UNION ALL to skip duplicate elimination.

Existence check: use IFNULL((SELECT 1 FROM table WHERE condition LIMIT 1),0) instead of COUNT(*).