CODEWITHSUNDEEP
mysqldatabaseperformanceurlkey
Jacob
Jacob

Reputation: 461

What database key design to implement when considering performance and friendly URLs

Introduction

So I recently asked a question on the information security stack exchange which led me to decide that I don't want to be exposing auto incrementing integer ids externally not because of security reasons, but because I think it would be quite unnerving for the user to come to the conclusion how many students had signed up before them.

The problem

But this leads me to another decision, if I'm going to use a GUID or some sort of other randomly generated string id, should I be using it as the primary key or not? Since my RDBMS of choice is MySQL and database engine of choice is InnoDB, in InnoDB the primary key is always clustered, and all other indexes are non-clustered. If I make the GUID the primary key, I would be getting extremely bad performance with a higher number of inserts.

Proposed solution

So I'm thinking about keeping the auto incrementing integer ids for all the tables and as well, keeping all my foreign keys as the integer ids but, also adding in a unique index to each table to store the GUID, which would be exposed externally. Then every time I received a request in my API, and I wanted to join the Student table with the Books table in my database, the first thing I would need to do is query the database to figure out what the integer id is for the specified student GUID.

Since I assume I'm definitely not the first one to come to this problem, or a similar one for that matter, is this an optimal approach for solving my issue?

Upvotes: 1

Views: 34

Answers (1)

Rick James
Rick James

Reputation: 142298

Yes, that is reasonable.

Having the GUID as a secondary index, with an INT AUTO_INCREMENT limits the inefficient part to that one index. Suggest you pack the GUID -- instead of CHAR(36) CHARSET ascii (always 36 bytes), pack it into BINARY(16) (always 16 bytes). That, plus a 4-byte INT, leads to maybe 50MB for a million students. Tiny compared to your multi-GB disk?

For minimal overhead, simply make the GUID a secondary key on the Students table. That mostly eliminates what appears to be an extra step of "first thing I would need to do is query the database ..."

Alternatives:

  • A random number. Smaller, but need to avoid dups.
  • A salted and hashed function of the AUTO_INC.

Upvotes: 3

Related Questions