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?

New contributor
John Gowers is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
  • (1) Yes. Clustered index is an index where physical ordering of records matches their logical ordering by clustered index expression. (2) Yes. Any unique index (including primary) forbids index expression duplicated value insertion. – Akina Feb 13 at 11:28
  • @Akina So does that mean that lookups by the index happen in constant time (in the number of rows)? – John Gowers Feb 13 at 11:32
  • No. Sequental data in a file does not mean sequental clusters chain on disk. But when the index is cached in memory it doesn't matter does it is unique (the more clustered/primary). – Akina Feb 13 at 11:39
  • "array-like lookup performance" - do you mean O(1)? – akuzminsky 10 hours ago
  • @akuzminsky Yes, but more specifically that it performs a similar sort of procedure to get that O(1) performance - i.e., compute an offset and go to the data at that offset. – John Gowers 7 hours ago

Your Answer

John Gowers is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.