Performance Comparison of Clustered and Non-Clustered Indexes in SQL Server and MySQL

Indexes in databases aim to improve query efficiency but can increase the cost of data modification; this article conducts experiments to quantify those trade‑offs.

A clustered index orders and stores table rows physically according to the key, allowing only one per table; tables without a clustered index store rows in an unordered heap.

The leaf level of a clustered index contains the actual data rows, which can require up to 120% additional storage, whereas a non‑clustered index stores only key values and pointers to the data rows.

Non‑clustered indexes keep items sorted by the index key while the underlying table data may follow a different order defined by the clustered index.

The test environment consists of two IBM PCs (client: i5‑4590 + 4 GB RAM, server: i7‑4790 + 8 GB RAM) running Windows 7 (32‑bit client, 64‑bit server). The databases used are MS SQL Server 2008R2 and MySQL Server 5.5. Three tables— student_no (no index), student_in (non‑clustered index), and student_cin (clustered index)—share the same schema, created with the following SQL:

<code>CREATE TABLE `student_xx` (
  `ID` int(11) NOT NULL,
  `name` varchar(10) NOT NULL,
  `age` int(3) NOT NULL,
  `address` varchar(10) NOT NULL
);</code>

Six types of operations were defined: single‑row insert (I1), batch insert (I2), single‑field update (U1), multi‑field update (U2), delete (D1), and four query variants (S1: plain select, S2: SELECT COUNT, S3: SELECT SUM, S4: SELECT ORDER BY), each executed on 1,000 rows and timed.

Results for a small dataset (10,000 rows) show that in SQL Server the clustered index generally yields the best performance for inserts, updates, and deletes, while in MySQL indexes dramatically reduce query times; non‑clustered indexes also improve queries but are slightly slower than clustered ones.

The study concludes that for small‑scale tables, a clustered index is the optimal choice, and that indexes are crucial for query speed in MySQL. Future work will repeat the experiments on million‑row tables.

Disclaimer: The content is compiled from online sources; copyright belongs to the original author.