Master MySQL Indexes: Differences, Composite Strategies & Performance Hacks

Introduction

Below is a concise reference of common MySQL database topics that are useful for further study.

1. Difference between UNION ALL and UNION

Both combine two result sets.

UNION removes duplicate rows, which requires sorting and deduplication.

UNION ALL simply concatenates the results without deduplication.

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

2. Difference between TRUNCATE and DELETE

TRUNCATE is a DDL statement; DELETE is DML.

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

TRUNCATE cannot be rolled back; DELETE can.

TRUNCATE returns “0 rows affected” (no result set).

TRUNCATE resets auto‑increment counters; DELETE does not.

TRUNCATE works only on whole tables; DELETE can filter rows with conditions.

In most cases, TRUNCATE performs slightly better than DELETE.

3. Difference between TIMESTAMP and DATETIME

Similarities

Both display in the format YYYY‑MM‑DD HH:MM:SS and occupy a fixed 19‑character width.

Differences

TIMESTAMP : 4‑byte storage, 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 : 8‑byte storage, range 1000‑10‑01 00:00:00 to 9999‑12‑31 23:59:59; stored as literal values without time‑zone conversion.

4. What is a composite (joint) index

A composite index is an index that covers two or more columns; it is also called a multi‑column index.

5. Why use composite indexes

Reduced overhead : An index on (col1,col2,col3) behaves like three separate indexes (col1), (col1,col2), (col1,col2,col3) while saving disk space.

Covering index : For a query SELECT col1,col2,col3 FROM test WHERE col1=1 AND col2=2, MySQL can retrieve rows directly from the index without accessing the table, reducing I/O.

Higher selectivity : More indexed columns filter out more rows. Example: with 10 000 000 rows, three conditions each filtering 10 % reduce the result set to 1 % (10 000 rows) when using a composite index, versus 10 % per condition when using separate indexes.

6. MySQL left‑most prefix rule

MySQL uses the leftmost prefix of a composite index; matching starts from the first column.

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

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

7. Clustered vs non‑clustered indexes

Clustered index: created automatically on the primary key; the table’s data is stored in the index B‑Tree.

Non‑clustered index: created on non‑primary columns; stores the primary key value plus indexed columns.

8. Covering index

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

9. Prefix index

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

Creation syntax:

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 addresses.

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

InnoDB secondary indexes store the primary key value plus indexed columns, making them larger if the primary key is wide.

MyISAM secondary indexes also store record addresses, similar to the primary index.

11. Prefer monotonic increasing primary keys

In InnoDB, rows are stored in primary‑key order within leaf pages. An auto‑increment key inserts new rows at the end of the current page, minimizing page splits and movement, which yields high insert efficiency.

Non‑incrementing keys cause random inserts, leading to page splits, row movement, and fragmentation, which degrades performance.

12. Meaning of length after INT

The length specifier for INT does not affect storage; it only defines the display width when used with ZEROFILL, padding with leading zeros.

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 : Approximate number of unique values; higher values generally mean better selectivity.

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

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

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

14. LIKE pattern and index loss

When a pattern starts with a wildcard ( %abc or %abc%), MySQL cannot use the index and performs a full table scan. Patterns ending with a wildcard ( abc%) can use a range scan.

Solution: use a covering index or rewrite queries to avoid leading wildcards.

15. Efficient pagination

Using SELECT * FROM ttl_product_info ORDER BY id LIMIT N,M forces MySQL to read N+M rows and discard the first N, which is slow for large N.

Better approach: SELECT id FROM ttl_product_info WHERE id > N LIMIT M where id is a unique, monotonic primary key. This uses a range scan and returns the desired page in milliseconds.

Prerequisite: id must be a unique, increasing index.

The client must remember the last id from the previous page.

Only sequential page navigation is supported.