Suppose I have a MySQL table with a unique integer index
id that is auto-incremented for each row.
id | PlanetName ---+----------- 0 | Mercury 1 | Venus 2 | Neptune
Suppose that I am primarily interested in optimizing queries that look up rows by the unique index. For example:
SELECT PlanetName FROM Planets WHERE id = 2
If I were programming in another language and I had the data loaded up into an array, then it would be very quick to perform these lookups by pointer arithmetic. I would like to be able to get the same sort of performance when I carry out the queries in MySQL.
My thinking is:
- a clustered index means the data is stored sequentially (i.e., very similarly to how a primitive array is stored in memory)
- a unique integer index ensures that SQL does not have to worry about duplicate rows
So if I create a unique clustered index for
id, does that mean that MySQL will carry out a pointer arithmetic-type lookup?