What cons should I consider if I put index on every UUID column in my database

Question

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?

Update

Datebase = MariaDB
Version = 10.10.2

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.
• A PRIMARY KEY is an INDEX.
• 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.