I'm creating a system that is not entirely microservices, but it's getting close to microservices and each module has its own database.

And based on what I have seen in many online services (Azure, AWS, Google Cloud, etc.) I have used UUID (or GUID) for my identifications.

This means that in the Orders database, I have CustomerUuid column. So, if I want to query a customer's orders, I should use where CustomerUuid = 'uuid_valu'.

To boost reading performance for all UUID I have this plan to put an index on every UUID column.

What negative impacts this decision might have? What are the cons for this strategy?


Datebase = MariaDB
Version = 10.10.2

  • Why a separate index, why not a clustered primary key? And why do you feel you need UUIDs anyway? Commented Mar 5, 2023 at 12:40
  • @Charlieface, because for foreign keys we can't have clustered primary keys. Commented Mar 5, 2023 at 13:44
  • I have no idea what that means. If the foreign keys are on different columns then what is the UUID for in the first place? Why use them, why not just use whatever primary key you have? Commented Mar 5, 2023 at 15:08
  • @Charlieface, what part of it is ambiguous? When you create a foreign key, the referencing column should be of the same type as the referenced column. Or do I know it wrong? And when your primary key column is UUID, then the foreign key column should also be UUID. Commented Mar 6, 2023 at 7:05
  • So if the foreign key column in the other table is supposed to be another type why isn't this column also? Why use UUIDs, why not an incremental IDENTITY integer? And if you want to use UUIDs then make the foreign key columns in the child also UUID. You seem to indicate that the UUID must be an entirely separate column, why? Commented Mar 6, 2023 at 10:18

1 Answer 1


When you create a foreign key, the referencing column should be of the same type as the referenced column

Absolutely. They should both be INT or both be UUID strings or otherwise compatible datatypes.

If you are having performance problems, then consider these:

  • INT (or another numeric datatype) is smaller than UUID, hence slightly faster.
  • If your dataset size is bigger than RAM, UUIDs tend to be significantly slower than INTs.
  • UUIDs should be used if you want the client apps to assign ids without the help of the database.
  • UUIDs can be packed into a smaller datatype. (cf MariaDB's datatype "UUID" and/or UUIDs )

As for indexing...

  • A FOREIGN KEY creates an INDEX if there is not already a suitable index defined.
  • An index helps the performance of SELECTs, including JOINs.
  • It is slightly wasteful to add indexes to columns if no query (Select/Update/Delete) will ever use that index. My point here is that it is the queries that determine whether an index is desirable. (Not the schema.)
  • where CustomerUuid = 'uuid_valu' -- Yes, for performance, that needs INDEX(CustomerUuid) or PRIMARY KEY(CustomerUuid) or a suitable FOREIGN KEY. Ditto for SELECT ... FROM table1 JOIN table2 ON table1.CustomerUuid = table2.CustomerUuid.
  • Thank you for the notes. The point is, we use a JSON file and a database generator to create the database for us. We want to put that logic into the generator. Add one index to every UUID column. Otherwise, yes you're right. Each index should be analyzed independently. Commented Mar 29, 2023 at 8:06
  • @SaeedNeamati - Does the "generator" use the JSON to design the schema? Or is the data in JSON? I hope you are not using a JSON column in a table and trying to index things inside the JSON strings.
    – Rick James
    Commented Mar 29, 2023 at 15:00
  • It's using JSON to create the schema. For example: { "table": { "name": "Customers", "columns": [] } } Commented Mar 31, 2023 at 8:21
  • What is the name of the "generator" app that converts JSON to "CREATE TABLE ..."?
    – Rick James
    Commented Mar 31, 2023 at 19:29
  • It's a home-made utility. It's not a public app. Commented Apr 2, 2023 at 8:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.