1

I have a database with about 100 tables, I want to add REST API requests connected with some tables. I don't want to expose id from the database directly to the request so I want to change PK (primary key) from auto-increment int to UUID for particular tables.

Do you think that this is nothing wrong with that situation - to have partly as PK UUID and partly auto-increment int (one table has UUID and another autoincrement int)?

I've just wanted to know if there could be any performance issue with such hybrid solution I want to choose and if there are any disadvantage to not use such options.

David
  • 67
  • 9
  • It's not disallowed by MySQL. But as for whether it's "wrong" or not, that's totally subjective. Stack Overflow [guidelines](https://stackoverflow.com/help/dont-ask) discourage questions that are bound to be answered by opinions. – Bill Karwin Mar 31 '22 at 16:40
  • What is wrong with exposing IDs? – Salman A Mar 31 '22 at 16:41
  • Does this answer your question? [Can I create a cross-platform app with Xamarin using a MySQL database?](https://stackoverflow.com/questions/71635808/can-i-create-a-cross-platform-app-with-xamarin-using-a-mysql-database) – Javier G.Raya Mar 31 '22 at 16:43
  • 1
    Be careful when you want to use a UUID as a primary key, see other questions like https://stackoverflow.com/questions/2365132/uuid-performance-in-mysql/2365176 – Progman Mar 31 '22 at 17:02
  • thanks for the answers! @SalmanA - this is for example post from StackOverflow: https://stackoverflow.com/questions/56576985/is-it-a-bad-practice-to-expose-the-database-id-to-the-client-in-your-rest-api: 1. They show your volume (for example: if you create an object and the API returns ID 10,001, it gives a rough estimate of how many objects of that kind you have on your DB, which might be interesting to hackers or to the competition) 2. Hackers could exploit "Insecure Direct Object References" 3. Hackers could use it for XSS attacks – David Mar 31 '22 at 17:07
  • @BillKarwin thanks Bill, I was thinking rather about users experiences, not opinions - for creating such hybrid solutions. Need for having partly UUID (for API) and int for better performance for most of table in db, especially for multiple JOIN operations. Maybe better will be just have int as PK and additional field with UUID for particular tables for API? – David Mar 31 '22 at 17:12
  • Stack Overflow really wants questions to be about specific code problems that have clear, correct answers. Not anecdotes or user experiences. I've been chastised or downvoted for even answering such questions. – Bill Karwin Mar 31 '22 at 17:17
  • @BillKarwin - maybe you are right, probably I should better preparing my question. Sorry for that. I've just wanted to know if there could be any performance issue with such hybrid solution I want to choose and if there are any disadvantage to not use such options. – David Mar 31 '22 at 17:33
  • Indexing your database will improve performance. Don't think using UUID as PK will result in performance issues but I'm not expert in MySQL – Evgeni Enchev Mar 31 '22 at 17:38
  • 1
    It's generally true that there are advantages and disadvantages to _every_ design. Optimization is always a tradeoff, it's virtually guaranteed that any optimization choice will improve certain queries, at the expense of other queries. Every app is different. I.e. they have different queries and different bottlenecks. So you must choose a design that is suited to your app. You must know which queries in your app are more important to optimize, and choose a design that helps those. Sorry there is no simple answer. – Bill Karwin Mar 31 '22 at 17:42
  • 1
    One option would be to use uuid as a secondary key. That being said, I deal with APIs from different vendors (e.g. Airbnb room numbers are just integers) most of the time and never saw anyone using obfuscated IDs. – Salman A Mar 31 '22 at 19:33
  • UUIDs are inefficient for _large_ table; see [_UUIDs_](http://mysql.rjweb.org/doc.php/uuid) – Rick James Mar 31 '22 at 22:22
  • @RickJames - thanks, but I want to use this solution with MySQL 8 and it doesn't seem inefficient from this perspective: "With these new features in MySQL 8.0.0 (Sep. 2016; 8.0.11 GA in Apr. 2018), this blog is rendered mostly useless. MySQL 8.0.0 improves the usability of UUID manipulations (WL#8920) by implementing three new SQL functions: ....." – David Apr 01 '22 at 07:06
  • @David - Yes, use the features of 8.0. For users not yet using 8.0, my paper provides a similar solution. – Rick James Apr 01 '22 at 16:08

0 Answers0