Master MySQL: Key Differences, Index Types, and Performance Tips

1. UNION ALL vs UNION

Both UNION and UNION ALL combine two result sets.

UNION removes duplicate rows, which requires sorting and deduplication.

UNION ALL simply appends the results without removing duplicates.

Because UNION performs sorting, UNION ALL is generally much faster.

2. TRUNCATE vs DELETE

TRUNCATE is a DDL statement; DELETE is DML.

TRUNCATE drops the table and recreates it, making it faster than row‑by‑row DELETE.

TRUNCATE cannot be rolled back; DELETE can.

TRUNCATE returns "0 rows affected" and does not produce a result set.

TRUNCATE resets auto‑increment counters; DELETE does not.

TRUNCATE removes all rows; DELETE can delete rows based on a condition.

In most cases, TRUNCATE performs slightly better than DELETE.

3. TIMESTAMP vs DATETIME

Similarities

Both display in the format YYYY‑MM‑DD HH:MM:SS and occupy 19 characters.

Differences

TIMESTAMP

Stored in 4 bytes, range 1970‑01‑01 08:00:01 to 2038‑01‑19 11:14:07.

Values are stored in UTC and converted to the session time zone on retrieval.

DATETIME

Stored in 8 bytes, range 1000‑10‑01 00:00:00 to 9999‑12‑31 23:59:59.

Stored as literal values, independent of time zones.

4. What is a composite (joint) index?

A composite index is an index on two or more columns, also called a multi‑column index.

5. Why use a composite index?

Reduced overhead: An index on (col1,col2,col3) effectively creates indexes for (col1), (col1,col2), and (col1,col2,col3), saving disk space.

Covering index: For a query selecting col1, col2, col3 with conditions on col1 and col2, MySQL can retrieve data directly from the index without accessing the table rows, reducing random I/O.

Higher efficiency: With a large table, filtering using a composite index dramatically reduces the number of rows processed. Example: select from table where col1=1 and col2=2 and col3=3 If each condition filters 10% of rows, a single‑column index would process 1,000,000 rows, whereas the composite index would process only 10,000 rows.

6. MySQL composite index left‑most prefix rule

The index is matched from the leftmost column outward.

Matching stops when a range condition (>, <, BETWEEN, LIKE) is encountered.

Equality (=) and IN conditions can be reordered; MySQL’s optimizer will rearrange them to use the index.

7. Clustered vs non‑clustered indexes

Clustered index: created on the primary key.

Non‑clustered index: created on non‑primary key columns.

8. What is a covering index?

A covering index allows a query to be satisfied entirely from the index pages, avoiding a table lookup and reducing I/O.

9. What is a prefix index?

A prefix index indexes only the first N characters of a column, making the index smaller, but it cannot be used for ORDER BY, GROUP BY, or as a covering index.

Syntax to create a prefix index:

ALTER TABLE table_name ADD KEY(column_name(prefix_length))

10. InnoDB vs MyISAM index storage

MyISAM stores index files separately from data files; the index contains only record pointers.

InnoDB stores the table data itself as a B+Tree index; the leaf nodes contain the full row data, and the primary key serves as the clustered index.

InnoDB secondary indexes store the primary key value along with the indexed columns.

MyISAM secondary indexes are similar to primary indexes, storing only the record address.

Key differences:

Primary index: InnoDB’s data file is the primary index; MyISAM keeps data and index separate.

Secondary index: InnoDB stores the primary key in secondary index leaves; MyISAM stores record addresses.

11. Why prefer an auto‑incrementing primary key?

In InnoDB, rows are stored in the leaf nodes of the primary‑key B+Tree. An auto‑increment key appends new rows to the end of the leaf page, minimizing page splits and movement, leading to higher insert efficiency.

Non‑incrementing keys cause random inserts, requiring page splits and row movement, increasing overhead and fragmentation.

12. Meaning of length after INT in CREATE TABLE

The length specifies display width, not storage size; it only affects zero‑padding when used with ZEROFILL.

13. Meaning of SHOW INDEX columns

Table: Table name.

Non_unique: 0 = unique index, 1 = non‑unique.

Key_name: Index name (PRIMARY for the primary key).

Seq_in_index: Position of the column within the index (starting at 1).

Column_name: Column name.

Collation: Sort order (A = ascending, D = descending, NULL = not sorted).

Cardinality: Estimate of index uniqueness; higher values generally mean better selectivity.

Sub_part: Number of characters indexed for a prefix index; NULL if the whole column is indexed.

Null: YES if the column permits NULL, '' otherwise.

Index_type: Index method (BTREE, FULLTEXT, HASH, RTREE).

14. Solving LIKE '%string%' index loss

Patterns starting with % cause full table scans. Using a covering index can turn an ALL scan into an INDEX scan, allowing the query to use the index for the needed columns.

15. Efficient pagination in MySQL

Typical pagination: SELECT * FROM ttl_product_info ORDER BY id LIMIT N, M becomes inefficient for large N because MySQL must skip N rows.

Better approach: SELECT id FROM ttl_product_info WHERE id > N LIMIT M where id is a monotonic, unique index, reducing the operation to a range scan and dramatically improving performance.

Prerequisites: id must be a unique, incrementing index; N must be the last id from the previous page; pagination proceeds sequentially.