Now that I've converted my primary keys to GUIDs, how do I fix the performance?

Question

I'm using TopLink as my ORM and MySQL as the DB.

I traded my auto-increment primary keys for GUIDs for one of my tables (alright, not quite: I'm actually using a random 64 bit integer, but that's good enough for my needs).

Anyway, now queries, which don't even use the key, are taking much longer.

What can I do?

Answer 1

If your table is indexed by the fields your are querying by. content of the key shouldn't have any noticeable performance impact. They maybe something else there .

Answer 2

Make sure the key column really is the PRIMARY KEY and thus the clustered (physical) index.

Make sure you have indexes that hit your common queries.

---

Random integer? You realize there's a chance of hitting the same pri key?

Answer 3

i have a field that represents the creationDate. how can i force my table to be index accordingly?

create index Table_creationDate on Table(creationDate);

Answer 4

If you're using innodb tables in MySQL all rows are referred to by primary key so if you're using a secondary key performance will suffer.