1

Currently, I have a database with 13 millions rows and we use uuid as the primary key. Everytime we do a database migration, it takes hours to finish a table. The query performance seems to be poor as well.

After Googling and reading some blogs, they recommended converting from uuid to binary(16), but the converted value is unreadable and very awkward to use. It's also hard to use in my Ruby code.

Is there an alternative solution to get a globally unique identifer in MySQL beside uuid?

mysql> select UNHEX(REPLACE('A4E7890F-A188-4663-89EB-176D94DF6774','-',''));
+---------------------------------------------------------------+
| UNHEX(REPLACE('A4E7890F-A188-4663-89EB-176D94DF6774','-','')) |
+---------------------------------------------------------------+
| ���Fc��m��gt                                                       |

I checked, and mongodb also has ObjectId which is only 12 bytes. Is it possible to use that in MySQL server? How could I take advantage of that to use in MySQL?

the Tin Man
  • 158,662
  • 42
  • 215
  • 303
Chamnap
  • 4,666
  • 2
  • 34
  • 46
  • I suggest first breaking your large table into several smaller ones. That'll help with migrations at the cost of little code complexity. – Sergio Tulentsev Jun 07 '12 at 12:46
  • It's a database for 200 portals, that table is for those portals. If I split, I should split into 200 databases. That means I would need to do maintenance quite alot. – Chamnap Jun 07 '12 at 12:49
  • 1
    Why 200 databases? You could divide them in groups and create, say, 10-20 tables (databases) – Sergio Tulentsev Jun 07 '12 at 12:51
  • each table belongs to all tables. if it needs to split, split them all together right? – Chamnap Jun 07 '12 at 12:54
  • Not necessarily. You can also split them differently (one table becomes 10 tables, and another one - 20). – Sergio Tulentsev Jun 07 '12 at 12:56
  • well, it will difficult to do reporting because the data scatters around. – Chamnap Jun 07 '12 at 12:59
  • 1
    Yes, but migrations run faster. And you can always keep splitting (and moving data to another machine). Messing with data formats has much lesser effect. – Sergio Tulentsev Jun 07 '12 at 13:02

1 Answers1

1

Answer

You will have to test this for your application, but with really large datasets I would expect performance to improve if you:

  1. Use an AUTO_INCREMENT column for your primary key, since MySQL is optimized for that.
  2. Index your UUID column so that you can do fast lookups without a full table scan.

Comparing lengthy UUIDs can't possibly be as efficient as integer keys, so you should definitely get a performance boost this way even if you're still using the UUID column heavily. There's no substitute for benchmarking it yourself, though.

Related Question

UUID performance in MySQL?

Community
  • 1
  • 1
Todd A. Jacobs
  • 81,402
  • 15
  • 141
  • 199