MySQL Essentials: UNION vs UNION ALL, TRUNCATE vs DELETE, Index Types Explained

1. Difference between UNION ALL and UNION

Both UNION and UNION ALL combine two result sets.

UNION removes duplicate rows by sorting and deduplication, which adds overhead.

UNION ALL simply concatenates the results without removing duplicates.

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

2. Difference between TRUNCATE and DELETE

TRUNCATE is a DDL statement; DELETE is a DML statement.

TRUNCATE drops the whole table and recreates it, while DELETE removes rows one by one, making TRUNCATE faster.

TRUNCATE cannot be rolled back; DELETE can be.

TRUNCATE returns "0 rows affected" without result data.

TRUNCATE resets auto‑increment counters; DELETE does not.

TRUNCATE can only clear an entire table; DELETE can delete rows based on conditions.

In most scenarios, 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 width of 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, requiring conversion 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 the literal value, independent of time zones.

4. What is a composite (joint) index

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

5. Why use composite indexes

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

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

Higher efficiency: With more indexed columns, the filtered result set becomes smaller. Example: a table with 10 million rows and a query SELECT FROM table WHERE col1=1 AND col2=2 AND col3=3 can reduce the scanned rows from 1 million (single‑column index) to 10 000 (composite index) when each condition filters 10% of rows.

6. MySQL composite index left‑most prefix rule

The index is used from the leftmost column onward; matching stops when a range condition (>, <, BETWEEN, LIKE) is encountered.

Order of columns matters: in an index (a,b,c,d), a condition on d alone cannot use the index.

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

7. What are clustered and non‑clustered indexes

Clustered index: the index built on the primary key; the table data is stored in the index B‑Tree.

Non‑clustered index: an index built on non‑primary columns; it stores the primary key values (or row pointers) along with the indexed columns.

8. What is a covering index

A covering index (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. MySQL cannot use prefix indexes for ORDER BY or GROUP BY, nor can they serve as covering indexes.

ALTER TABLE table_name ADD KEY(column_name(prefix_length));

10. Differences between InnoDB and MyISAM index storage

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

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

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

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

MyISAM primary and secondary indexes have the same structure; the primary index must be unique.

In short, InnoDB’s data and index are integrated, while MyISAM separates them.

11. Why prefer monotonically increasing numeric primary keys

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 current leaf page, minimizing page splits and movement, which yields high insert performance.

A non‑incrementing key inserts rows at random positions, causing page splits, record movement, and fragmentation, which degrades performance and may require OPTIMIZE TABLE to rebuild.

12. Meaning of the length after INT

The storage size of INT is fixed; the length attribute only affects display width. When used with ZEROFILL, values are left‑padded with zeros. It does not affect storage precision.

13. Meaning of SHOW INDEX columns

Table : table name.

Non_unique : 0 means the index is unique; 1 means duplicates are allowed.

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

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

Column_name : indexed column name.

Collation : sort order (A for ascending, D for descending, NULL for not sorted).

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

Sub_part : prefix length for prefix indexes; NULL if the whole column is indexed.

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

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

14. Solving the LIKE '%string%' index loss problem

Patterns that start with a wildcard (e.g., LIKE '%abc%') prevent index usage, causing a full table scan.

Solution: use a covering index so the query can be satisfied from the index, turning an ALL scan into an INDEX scan. Only one indexed column needs to be matched.

15. Efficient pagination in MySQL

Using SELECT * FROM ttl_product_info ORDER BY id LIMIT N,M retrieves N+M rows and discards the first N, which is slow for large N.

Better approach: SELECT id FROM ttl_product_info WHERE id > N LIMIT M. The id column must be a unique, monotonically increasing index, and N should be the last id from the previous page.

id must be a unique, auto‑increment index.

N is the last id from the previous query (stored on the client side).

Only sequential page navigation is supported.

References

https://segmentfault.com/a/1190000015416513

https://blog.csdn.net/bigtree_3721/article/details/73151472