3

I am working with a table that has a couple hundred million rows of data. It uses varchar(32) UUID's as it's keys both primary and foreign (there are two). I was wondering with this size of table, how much of each field do I need to index to keep good performance but not index every character of those three fields.

For example if I set an index length of 24 characters, will I see any noticeable difference in performance? I was hoping there might be some kind of algorithm out there that can be used to estimate performance based on length of field and length of index.

Currently the table is ~100GB in size, and the index is taking up over half of that space. I would like to recover some of that space, and reduce the growth rate of the table.

2

This is not an answer to your question (how to cut index length), but an alternative to reach the same objective: use less space in your DB.

If you want to optimize storage you should not store UUIDs as varchar(32) (nor character(32)).

You can store them as binary(16) with no loss of information, and save (at least) 50% of space. A UUID contains 128 bits of information. If stored as a text representing the hexadecimal value, you use 4 bits of information out of every character, which employs 8 bits assuming the text is encoded as ASCII or latin1. So, you waste half your available bits. Storing the information in binary format, you waste none.

My response is heavily inspired in Storing UUID Values in MySQL Tables, which I would recommend you read.

The following example shows you how to store the same UUID data in the two different formats:

 CREATE TABLE t
 (
      uuid_a character(32) PRIMARY KEY
     ,uuid_b binary(16) UNIQUE KEY
 ) ;

Both uuid_a and uuid_b will be used to store the same UUID, encoded in two different ways.

Assuming we have a table called generator_64k with 64k of data... we fill t with lots of uuid() using:

 -- We fill it with random data
 INSERT INTO 
     t 
 SELECT
      replace(u,'-','')             -- text version
     ,unhex(replace(u,'-',''))      -- binary version
 FROM
     (SELECT 
         uuid() AS u 
      FROM 
         generator_64k
     ) AS s0 ;

... and we add also a well known value:

 -- We fill it with one known piece of data
 INSERT INTO 
     t 
 SELECT
      replace(u,'-','')             -- text version
     ,unhex(replace(u,'-',''))      -- binary version
 FROM
     (SELECT
         'aab5d5fd-70c1-11e5-a4fb-b026b977eb28' AS u
     ) AS s0 ;

You can then query the table and look for the known value using either of the representations:

 -- Encoded as character(32)
 SELECT
     uuid_a, hex(uuid_b)
 FROM 
     t
 WHERE
     uuid_a = replace('aab5d5fd-70c1-11e5-a4fb-b026b977eb28', '-', '') ;

 -- Encoded as binary(16)
 SELECT
     uuid_a, hex(uuid_b)
 FROM 
     t
 WHERE
     uuid_b = unhex(replace('aab5d5fd-70c1-11e5-a4fb-b026b977eb28', '-', '')) ;

You get, in both cases, the same result:

 uuid_a                          | hex(uuid_b)                    
 :------------------------------- | :-------------------------------
 aab5d5fd70c111e5a4fbb026b977eb28 | AAB5D5FD70C111E5A4FBB026B977EB28
 

In practice, you would only use uuid_b in your table.

By doing this, you halve the amount of space used to store your UUIDs (both in table and indexes), without having to resort to using a substring in your index (which can affect your performance, and still not save that much).

See all the code at dbfiddle here

  • This is really interesting, and looks like it could be quite useful for projects in the future, I am not sure how we could go about implementing something like this without a significant downtime, considering all the foreign keys in use that would need to be handled. – Jack Jun 15 '17 at 19:33
  • I am afraid you actually need some or a lot of downtime... You could, in principle, add a new column new_uuid of type binary(16) and let it be nullable. I am not sure about MySQL, but this shouldn't block the table, or at least not do it for too long. Do this for all UUIDs in use. Update this new column in batches. Finally, create indexes, change foreign keys. This bloats a lot the space used before you can start the shrinkage... by dropping the unneded columns (this takes a long time and blocks lots of things). I don't know the details of MySQL well enough to evaluate how much. – joanolo Jun 15 '17 at 19:41
  • 1
    @joanolo - Newer versions of MySQL might be able to do as you described without significant downtime. There are multiple ALTER TABLE actions (add, drop column and FK); it is not clear that each can be done without copying the table over, would would be terribly long. – Rick James Jun 29 '17 at 2:40
  • I guess drop columns is the one that might block... but I actually don't know enough of the specifics of MySQL to be able to tell. – joanolo Jun 29 '17 at 20:35
0

You may be doomed.

  • "Prefix indexes" (as in INDEX(uuid(24)) are not well optimized. In some situations, the Optimizer ignores them and scans the table instead. Not knowing all of your queries, I can't say.

  • Moving to BINARY(16) is definitely an improvement in space.

  • (Doomsday:) As soon as the size of an index on a "uuid" exceeds what can be cached in RAM, performance goes down. And it continues to go down as the table grows. Shrinking the number of bytes in the index delays this fate, but does not eliminate it. The reason is the randomness of UUIDs/GUIDs -- this effectively makes caching less and less useful.

  • What generated the uuids? Do you tend to touch "newer" rows? There may be some hope, see http://mysql.rjweb.org/doc.php/uuid on a technique for packing Type 1 uuids to give you 'locality of reference'.

Since there is no generic solution for your question, please describe your application and provide SHOW CREATE TABLE. From those, we may be able to help with performance (and space) with some other technique.

  • Interesting to know about the prefix indexes not really helping due to how UUID's are formed. I think what we might end up having to do is drop at least one of the indexes (a foreign key we don't apparently use in any of our queries) and possibly remove the primary key indicator and it's index. It's a dead-end table, nothing will ever use it's primary-key as a foreign-key. I appreciate everyone's help and suggestions on this one. I think in the future I have convinced them to use Binary(16) instead of varchar(32) but we have to live with this system for the time being. – Jack Jun 30 '17 at 13:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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