Creating tables & indexes in MySQL

Designing the schema is where the real work happens. Good column types and constraints stop bad data from entering the database at all, and well-chosen indexes keep queries fast as the table grows.

CREATE TABLE

CREATE TABLE customers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(190) UNIQUE,
  city VARCHAR(80) NOT NULL DEFAULT 'Unknown',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT NOT NULL,
  total DECIMAL(10,2) NOT NULL,
  status ENUM('NEW','PAID','SHIPPED') NOT NULL DEFAULT 'NEW',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

CREATE INDEX idx_orders_customer ON orders(customer_id);

Use utf8mb4 so Hindi text and emojis store correctly — the older utf8 alias is a three-byte subset that cannot hold them. ENGINE=InnoDB gives you foreign keys, row-level locks and transactions.

Choosing column types

Type	Use for	Note
INT / BIGINT	ids and counts	UNSIGNED doubles the positive range
DECIMAL(10,2)	money	Exact; never use FLOAT for rupees
VARCHAR(n)	names, emails	Only the bytes used are stored
CHAR(n)	fixed codes such as a 6-digit PIN code	Padded to n characters
DATE / DATETIME	dates and timestamps	Store UTC for multi-region apps
ENUM(...)	a short fixed list	Adding a value needs ALTER TABLE
TEXT / JSON	long notes, flexible payloads	Cannot take a plain default

Keep lengths realistic: an index on VARCHAR(190) for email fits comfortably inside MySQL's index key limits.

Constraints

Constraint	Guarantees
PRIMARY KEY	Unique and not null; one per table
UNIQUE	No duplicates, e.g. a GSTIN or an email
NOT NULL	A value must be supplied
DEFAULT	Value used when none is given
FOREIGN KEY	The referenced row exists
CHECK	A condition, enforced from MySQL 8.0.16

ALTER TABLE orders
  ADD COLUMN gst_amount DECIMAL(10,2) NOT NULL DEFAULT 0,
  ADD CONSTRAINT chk_total_positive CHECK (total >= 0);

A foreign key can also say what happens to children: ON DELETE CASCADE removes the orders along with the customer, while the default ON DELETE RESTRICT blocks the delete.

Indexes

An index is a sorted structure that lets MySQL find rows without reading the whole table. Add them on the columns used in WHERE, JOIN and ORDER BY.

CREATE INDEX idx_orders_created ON orders(created_at);
CREATE UNIQUE INDEX uq_customers_email ON customers(email);
SHOW INDEX FROM orders;

Every index speeds reads but slows INSERT, UPDATE and DELETE and costs disk space, so add them deliberately and check query plans with EXPLAIN.

Normalise first

Store each fact once: customer details in customers, one row per order in orders, one row per line item in order_items. Repeating the customer's city inside every order row invites contradictions the day someone moves.

Common mistakes

• Storing money in FLOAT and watching 1249.99 become 1249.9899.
• Using VARCHAR(255) for everything, including a two-letter state code.
• Creating a separate index per column instead of one composite index in the right order.
• Omitting NOT NULL, so half the rows end up with a NULL city.
• Choosing MyISAM, which silently ignores foreign keys and transactions.