What is the difference between a clustered and a non-clustered index?
In a clustered index the index and the table are the same thing. In a non-clustered index they are two separate things.
So what does that mean in practice? In a clustered index, the rows of the table are stored in the order of the index, so the index does not hold a copy of the data. It holds the data, and it holds the order that the rows are in. When you look a row up, the engine goes to the index and the row is right there. You can have only one clustered index per table, because the rows can only be in one order.
In a non-clustered index, the index holds the values of the indexed columns and a pointer back to the row. Looking a row up means finding the index entry and then following the pointer to the row itself, which is a second step. You can have as many of these as you want, and they do not change the order of the rows in the table.
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
email VARCHAR(200) NOT NULL
);
-- In MySQL the primary key is the clustered index by default.
-- In PostgreSQL there is no clustered index at all: the table is a heap
-- and every index is a separate structure with a pointer.
So the rule to remember is this. A clustered index decides where the rows sit on the disk, and there can only be one. Every other index is a separate lookup that ends with a jump to the row.
The next question in this topic looks at what the choice does to a table with a random key.