Skip to content
MySQL Table Design Guide: Naming Conventions, Index Optimization & Production SQL Templates
Databases 30 min read

MySQL Table Design Guide: Naming Conventions, Index Optimization & Production SQL Templates

This comprehensive guide covers MySQL table creation best practices including naming conventions, field type selection, index design principles, constraint strategies, and solutions for large-table performance, JSON querying, and logical deletion conflicts, with production-ready SQL templates and interview-focused insights.

Rain's Path to Java Mastery
Rain's Path to Java Mastery
Rain's Path to Java Mastery

MySQL Table Creation: What to Pay Attention To

Naming Conventions (Big Tech Red Lines, Basic but Essential)

Unify team standards to avoid maintenance disasters. Core principles:

All lowercase with underscore separators; prohibit camelCase and Chinese characters.

Table name prefix unified (e.g., t_) to avoid MySQL keyword conflicts.

Self-explanatory names; prohibit meaningless abbreviations (e.g., u_id → user_id).

Index name unified prefixes: normal index idx_xxx, unique index uk_xxx.

Examples of wrong vs correct usage:

Wrong: UserInfo → Correct: user_info (all lowercase, underscore separated)

Wrong: order → Correct: t_order (avoid MySQL keywords)

Wrong: u_name → Correct: user_name (self-explanatory, no abbreviations)

Wrong: createTime → Correct: create_time (camelCase to underscore, unified style)

Field Design (First Line of Defense for Performance)

Most overlooked but highest impact. Core principle: use the smallest data type that satisfies business needs .

Examples of wrong vs correct usage:

Wrong: int(1) for status → Correct: tinyint(1) — saves 3 bytes/row, 3MB per million rows

Wrong: char(20) for phone → Correct: varchar(20) — variable-length storage, significant space savings

Wrong: timestamp for time → Correct: datetime(6) — timestamp has 2038 overflow + timezone issues

Wrong: varchar(255) for all strings → Correct: set length as needed — overlong causes row overflow, hurts performance

Wrong: NULL for defaults → Correct: 0/''/special values — NULL causes index failure, statistics errors

Additional Points:

Large fields (text/blob) split into separate tables to avoid row overflow and query degradation.

Enum types discouraged: schema change required for extension; use tinyint + dictionary table instead.

Primary key must be bigint to avoid int overflow (single table >2.1B rows breaks).

Index Design (Core Interview Topic)

Indexes are the soul of MySQL performance; plan during table creation. Core principle: few but precise, create on demand .

Must have primary key: recommend auto-increment bigint or snowflake ID; absolutely no tables without PK.

Composite indexes follow leftmost prefix principle ; high-selectivity columns first.

Avoid redundant indexes: if idx(a,b) exists, no need for idx(a).

Limit index count: single table ≤5 indexes; excess severely hurts write performance.

Prefer covering indexes to avoid table lookups.

Covering index illustration
Covering index illustration

Table Structure Optimization (Preempt Performance Bottlenecks)

Single table rows ≤20 million ; beyond that, plan sharding early.

Cold/hot data separation: archive historical data to history tables; main table stores only hot data.

Vertical split: move infrequently used large/low-frequency fields to extension tables.

Avoid wide tables: column count ≤30; excess impacts query efficiency.

Constraints & Security (Data Consistency Guarantees)

Not-null constraints: all business fields should be non-null with defaults.

Unique constraints: ensure data uniqueness, prevent dirty data.

Foreign keys discouraged : internet giants rarely use them; consistency enforced at business layer (avoid deadlocks, cascade updates).

Charset unified to utf8mb4 for emoji and full Chinese support.

Collation utf8mb4_unicode_ci more accurate than utf8mb4_general_ci.

Never store plaintext passwords; must use bcrypt/Argon2 encryption.

Special Scenario Design (Reflects Real-World Experience)

Soft delete: unified is_deleted tinyint(1) default 0, auto-filter in queries.

Auto timestamps:

create_time datetime(6) default current_timestamp(6),
update_time datetime(6) default current_timestamp(6) on update current_timestamp(6)

Optimistic lock: version int default 0 to prevent concurrent update conflicts.

Audit fields: create_by, update_by record operator for troubleshooting.

Interview Bonus Points (Gap-Widening Topics)

Pre-plan sharding strategy, e.g., hash by user ID.

Know lock-free schema change tools: pt-online-schema-change, gh-ost.

Monitor index cardinality; avoid indexes on low-cardinality columns (e.g., gender).

Understand implicit conversion causing index failure (e.g., varchar column queried with number).

Use EXPLAIN to analyze execution plans, verify index usage.

Pitfall Blacklist (Absolute No-Go Mistakes)

Using text/blob as PK or index column.

Indexing every column (over-indexing).

Wrong composite index column order causing failure.

Using NULL as default value.

Using char for variable-length strings.

Using timestamp for timestamps.

Big Tech Standard Table Template (Core Code + Technical Highlights)

Below is a production-validated standard CREATE TABLE SQL used in real projects with ten-million-row verification; all technical highlights annotated in comments:

CREATE TABLE `t_user` (
-- Highlight 1: PK must be bigint unsigned, avoid int overflow (2.1B limit), unsigned doubles capacity
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary Key ID',

-- Highlight 2: Field length set as needed, no blind varchar(255)
  `user_name` varchar(32) NOT NULL DEFAULT '' COMMENT 'Username',
  `mobile` varchar(20) NOT NULL DEFAULT '' COMMENT 'Mobile',
  `email` varchar(64) NOT NULL DEFAULT '' COMMENT 'Email',

-- Highlight 3: Status uses tinyint + dictionary table, not enum (extension requires ALTER)
  `status` tinyint NOT NULL DEFAULT 1 COMMENT 'User status: 1-normal, 2-disabled, 3-cancelled',

-- Highlight 4: datetime(6) replaces timestamp, solves 2038 overflow & timezone
  `last_login_time` datetime(6) DEFAULT NULL COMMENT 'Last login time',

-- Highlight 5: Soft delete standard, 0-not deleted, 1-deleted
  `is_deleted` tinyint NOT NULL DEFAULT 0 COMMENT 'Is deleted',

-- Highlight 6: Auto timestamps, millisecond precision, DB-maintained, no app code needed
  `create_time` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT 'Create time',
  `update_time` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT 'Update time',

-- Highlight 7: Optimistic lock for concurrent updates
  `version` int NOT NULL DEFAULT 0 COMMENT 'Optimistic lock version',

-- Highlight 8: Index naming standard, unique uk_xxx, normal idx_xxx
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_mobile` (`mobile`) COMMENT 'Mobile unique index',
  UNIQUE KEY `uk_email` (`email`) COMMENT 'Email unique index',

-- Highlight 9: Composite index follows leftmost prefix, high-selectivity first
  -- status low selectivity (3 values), create_time high, so order status+create_time
  KEY `idx_status_create_time` (`status`, `create_time`) COMMENT 'Status+create_time composite index',

-- Highlight 10: Unified charset utf8mb4 for emoji/Chinese; collation unicode_ci more accurate
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='User table';

Additional Technical Highlights in Code

No foreign keys : all relations enforced at business layer; avoids deadlocks, cascade updates, distributed transaction issues (internet giant iron law).

All fields non-null + defaults : completely eliminates NULL-caused index failures, statistics errors, NPEs.

ENGINE=InnoDB : forces InnoDB for transactions, row locks, crash recovery.

COMMENT mandatory : every table and column must have clear comments for maintenance.

Production Core Technical Challenges & Solutions

Challenge 1: Ten-million-row online schema change locks table

Impact: Long lock time, business unavailable, potential DB avalanche

Solution: Use gh-ost or pt-online-schema-change: create shadow table → sync data → swap names, zero locks throughout

Challenge 2: PK uniqueness conflict after sharding

Impact: Auto-increment IDs repeat across shards, data overwrite

Solution: Prefer snowflake algorithm (64-bit: timestamp + machine ID + sequence); fallback: segment mode (Meituan Leaf), UUID (not recommended, unordered & space-heavy)

Challenge 3: NULL causing index failure

Impact: IS NULL/IS NOT NULL queries skip index, full table scan

Solution: Force all fields non-null + defaults with special values (0, '', -1) to represent empty state

Challenge 4: Implicit conversion causing index failure

Impact: Column type vs param type mismatch triggers auto-conversion, index lost

Solution: Unify column and query types; e.g., mobile is varchar, must use where mobile='13800138000', not numeric

Challenge 5: Large fields (text/blob) causing row overflow

Impact: Row exceeds InnoDB 16KB page, overflow pages, query performance drops 10x+

Solution: Split large fields to separate table (e.g., t_user_extend), query on demand, avoid SELECT *

Challenge 6: Composite index leftmost prefix trap

Impact: Index(a,b,c), query where b=? and c=? fails completely

Solution: Strictly follow leftmost prefix; equality columns first, range columns last

Challenge 7: Single table >20M rows

Impact: Query performance plummets, index maintenance cost spikes

Solution: Cold/hot separation: archive history to history tables; pre-plan sharding by time or user ID hash

Challenge 8: Over-indexing hurts writes

Impact: Single table >5 indexes, insert/update/delete performance drops 50%+

Solution: Follow "few but precise"; drop redundant indexes, merge duplicates (if idx(a,b) exists, drop idx(a))

Realistic Mock Interview

Mock Interview: MySQL Table Creation Considerations

Interviewer:

"You've handled many DB designs. Tell me directly, what do you focus on most when creating MySQL tables? No textbook answers, just real experience."

Candidate:

"Okay, I've summarized 7 must-grab points from project practice. Starting with basics:

1️⃣ Naming & comments must be standardized. Table names all lowercase with underscores, e.g., t_order. Every field forced to have COMMENT, table itself also commented; database becomes living documentation.

2️⃣ Field types chosen carefully . Status/type use TINYINT, money strictly DECIMAL, variable strings VARCHAR with proper length, time unified DATETIME. Almost all fields set NOT NULL with defaults to avoid NULL pitfalls."

Interviewer:

"Good, standards are detailed. What about indexes ? Many just add INDEX(user_id), INDEX(create_time) blindly. Your take?"

Candidate:

"Absolute hotspot. I require the team:

PK mandatory, InnoDB strongly recommends auto-increment BIGINT , inserts ordered, reduces page splits. Distributed scenarios use ordered snowflake IDs.

Normal indexes only for WHERE, ORDER BY, GROUP BY; composite indexes follow leftmost prefix , high-distinctness columns first.

Long strings use prefix indexes , e.g., KEY idx_desc(description(20)).

Every index must pass EXPLAIN before deployment; redundant indexes forbidden.

Also, I require every table to carry the metadata quartet : id, create_time, update_time, is_deleted. Makes soft delete, time tracking easy; works great with MyBatis-Plus."

Interviewer:

"Nice. You mentioned DATETIME for time, why not TIMESTAMP? And charset/engine defaults?"

Candidate:

" TIMESTAMP has timezone conversion issues and range only to 2038; DATETIME wider range, worry-free. Engine always InnoDB for transactions, row locks, crash recovery. Charset must be utf8mb4 + utf8mb4_general_ci, stores emojis 😂, never use fake three-byte utf8. These are usually locked at database creation to prevent omissions."

Interviewer:

"Good. Deeper: if table needs large text like article content, or user extension attributes that change often, how to design?"

Candidate:

" Large fields split out . Article content to separate t_article_content, one-to-one with main table via PK, avoids main table dragging TEXT fields. For extension attributes, use JSON type for flexibility. If need to search inside JSON, create a virtual column index . Also appropriate denormalization: redundantly store common fields to reduce joins. More hard-earned avoidances 🩸: never use foreign keys, no ext1,ext2 reserved columns, single table columns ≤20. If data volume estimated at tens of millions, pre-plan sharding or partitioning."

Interviewer:

"Solid theory. Can you show a table structure diagram to visualize your design habits?"

Candidate:

"Sure, typical user module design looks like this:"

User module table structure
User module table structure

📌 Main table holds high-frequency small fields; details split out large fields and JSON; linked by user_id but no physical FK. All essential fields present, both disciplined and flexible.

Interviewer:

"Theory solid. Talk is cheap; show me the actual CREATE TABLE SQL you use daily , preferably with highlights showing technical depth."

Candidate:

"No problem. Here's a recent e-commerce order table design covering most points discussed plus advanced usages."

-- Order main table: note table name, field comments, type selection, index design, metadata fields
CREATE TABLE t_order (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'PK ID',
  order_no CHAR(32) NOT NULL COMMENT 'Order no, distributed ID generation',
  user_id BIGINT UNSIGNED NOT NULL COMMENT 'User ID',
  total_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT 'Order total amount',
  status TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Order status 0-pending 1-paid 2-cancelled',
  is_deleted TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Logical delete 0-normal 1-deleted',
  create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Create time',
  update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Update time',
  PRIMARY KEY (id),
  UNIQUE KEY uk_order_no (order_no),
  KEY idx_user_status_time (user_id, status, create_time) -- composite index covering common queries
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Order table';

-- Order detail table: large field split, JSON extension + virtual column index
CREATE TABLE t_order_detail (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'PK ID',
  order_id BIGINT UNSIGNED NOT NULL COMMENT 'Order ID',
  product_snapshot JSON NOT NULL COMMENT 'Product snapshot at order time',
  -- Extract frequently queried field from JSON, generate virtual column, index it
  product_name VARCHAR(200) GENERATED ALWAYS AS (product_snapshot ->> '$.name') VIRTUAL,
  is_deleted TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Logical delete',
  create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Create time',
  PRIMARY KEY (id),
  KEY idx_order_id (order_id),
  KEY idx_product_name (product_name) -- virtual column index for product name search
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Order detail table';

-- If data volume exceeds 100M, consider partitioned table (by time range)
-- Syntax shown as optional (production needs specific evaluation)
ALTER TABLE t_order PARTITION BY RANGE (YEAR(create_time)) (
  PARTITION p2024 VALUES LESS THAN (2025),
  PARTITION p2025 VALUES LESS THAN (2026),
  PARTITION p2026 VALUES LESS THAN (2027)
);

Technical Highlights:

GENERATED ALWAYS AS ... VIRTUAL

virtual column + index perfectly solves need for both JSON flexibility and search performance.

Composite index idx_user_status_time precisely covers "my orders" filtered by status and sorted by time.

Partition syntax kept as optional ; transform directly when data volume grows.

Interviewer:

"SQL very standard, virtual column index used beautifully. In actual development, what technical challenges did this design face and how were they solved? "

Candidate:

"Pain points are real; I'll share the most representative ones."

Challenge 1: Composite Index "Leftmost Prefix" Pitfall

Symptom: Index KEY idx_user_status_time (user_id, status, create_time); querying only status = 1 or sorting only by create_time causes index failure, full table scan.

Cause: Composite index must match from leftmost column; skipping user_id to query later columns makes index unusable .

Solution:

Queries must include user_id; business-wise also reasonable.

If cross-user status query truly needed, add KEY idx_status (status) but it's redundant, weigh trade-offs.

Enforce EXPLAIN verification for all online SQL; team rule: index creation requires EXPLAIN screenshot on record .

-- ✅ Can use index
SELECT * FROM t_order WHERE user_id = 1001 AND status = 0 ORDER BY create_time DESC;

-- ❌ Cannot use index, key column NULL
SELECT * FROM t_order WHERE status = 0 ORDER BY create_time DESC;

Challenge 2: Soft Delete + Unique Index Conflict

Symptom: order_no unique index; user cancels order (soft delete is_deleted=1). When they reorder with same order_no, unique constraint errors.

Cause: Soft delete only marks field; physical record remains, unique index cannot treat "deleted" as non-duplicate.

Solution:

Include is_deleted in unique index: UNIQUE KEY uk_order_no (order_no, is_deleted); allows ('NO123', 0) and ('NO123', 1) coexistence.

Better: design order_no with timestamp/sequence to avoid reuse.

Team ultimately chose latter: order_no globally unique, never recycled , completely avoids conflict.

Challenge 3: Large Table COUNT() Performance Disaster

Symptom: Tens of millions rows;

SELECT COUNT(*) FROM t_order WHERE user_id = ? AND status = 0

takes seconds, high concurrency saturates CPU.

Cause: InnoDB has no cached row counter; each execution traverses index or table.

Solution:

Business often doesn't need exact count; use EXPLAIN SELECT ... for estimated rows, or Redis approximate counter.

If exact required, use covering index + subquery to scan only index, not data.

Ultimate: offload to Elasticsearch aggregations, or dedicated counter table + transactional updates.

-- Optimized: force composite index, avoid table lookup
SELECT COUNT(*) FROM t_order WHERE user_id = 1001 AND status = 0;
-- If index covers well, MySQL uses idx_user_status_time directly

Challenge 4: JSON Field Search & Indexing

Symptom: Early extension info dumped into JSON column; later ops needed "find all orders buying specific brand products", stuck with full table scan.

Solution: Exactly the virtual column index used above. Extract $.brand from JSON into virtual column, index it; query performance goes from full scan to index seek, zero business code intrusion . This case is the source of the product_snapshot SQL segment, perfectly balancing flexibility and performance.

Challenge Summary

Composite index failure : Query slow, CPU spike → Root cause: violates leftmost prefix → Solution: adjust query conditions or reasonably add redundant indexes

Soft delete + unique key conflict : Insert errors → Root cause: physical record still exists → Solution: order_no globally unique, never recycled

Large table COUNT slow : Timeout, concurrency crash → Root cause: InnoDB no row count cache → Solution: covering index optimization / move to ES / counter table

JSON field search difficult : Full table scan → Root cause: JSON lacks normal indexes → Solution: virtual column + index

Deep pagination poor : Slower further pages → Root cause: LIMIT 10000,10 massive table lookups → Solution: cursor pagination / delayed join

Interviewer:

"These challenges are all real production-grade; shows you don't just stop at syntax but consider performance, scalability, fault tolerance behind the scenes. Very solid 👍."

Candidate:

"Thanks, our project suffered from these designs, so now we inject these preventive shots upfront during table creation 😄."

Original Source

Signed-in readers can open the original source through BestHub's protected redirect.

Sign in to view source
Republication Notice

This article has been distilled and summarized from source material, then republished for learning and reference. If you believe it infringes your rights, please contactadmin@besthub.devand we will review it promptly.

shardingMySQLdatabase designindex optimizationinterview preparationSQL performancevirtual columnstable creationJSON querying
Reader feedback

How this landed with the community

Sign in to like

Rate this article

Was this worth your time?

Sign in to rate
Discussion

0 Comments

Thoughtful readers leave field notes, pushback, and hard-won operational detail here.