Can you contrive a simple example in MySQL where clustered index is the solution, instead of normal index?

Question

I don't see the point of clustered index, when will we benefit?

Answer 1

Clustered indexes

A clustered index means that the records are physically stored in order (at least near each other), based on the index. Clustered indexes are most important when you are retrieving various columns from each record, in order, because the database engine does not have to jump around to get the next record. Instead, the records are stored sequentially, therefore the seek time between records is at its minimum.

Clustered indexes are most important when reading multiple records that appear near each other in the index.

By default, with InnoDB, your primary index is a clustered index.

Use case for clustered indexes

If you were doing an incremental search like the Google and Yahoo search, where as you start typing, you see the first few records that match what you've typed so far, performance is paramount. If you were returning just a single indexed column in the result set, you wouldn't need a clustered index, but let's pretend that you also want to return the number of hits for each key_word, forcing the database engine to access the actual row. Since you want to return sequential rows, you should store them sequentially for optimal performance.

SELECT key_word, hits FROM keywords
WHERE key_word LIKE 'britney s%'
ORDER BY key_word
LIMIT 10

You'd want your primary key (clustered index) to be on key_word.

Comparison to nonclustered indexes

All indexes are physically stored in order (a btree actually, but basically), so if you are returning just the column that is stored in the index, you're still getting the same benefit. That is because the indexed column's actual value is stored in the index, therefore MySQL will use the index value instead of reading the record. However, if you start retrieving columns that aren't part of the index, this is where you'd also want the actual records stored in order, such as they are with a clustered index.

MySQL Documentation on clustered indexes

Accessing a row through the clustered index is fast because the row data is on the same page where the index search leads. If a table is large, the clustered index architecture often saves a disk I/O operation when compared to storage organizations that store row data using a different page from the index record. (For example, MyISAM uses one file for data rows and another for index records.)

In InnoDB, the records in nonclustered indexes (also called secondary indexes) contain the primary key columns for the row that are not in the secondary index. InnoDB uses this primary key value to search for the row in the clustered index. If the primary key is long, the secondary indexes use more space, so it is advantageous to have a short primary key.

MySQL Clustered and Secondary Indexes