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? Mar 5 at 12:40
  • @Charlieface, because for foreign keys we can't have clustered primary keys. Mar 5 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? Mar 5 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. Mar 6 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? Mar 6 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. Mar 29 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
    Mar 29 at 15:00
  • It's using JSON to create the schema. For example: { "table": { "name": "Customers", "columns": [] } } Mar 31 at 8:21
  • What is the name of the "generator" app that converts JSON to "CREATE TABLE ..."?
    – Rick James
    Mar 31 at 19:29
  • It's a home-made utility. It's not a public app. Apr 2 at 8:59

Your Answer

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

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