MySQL – Impact of Using UUIDs with UNIQUE Index vs. PK

There appears to be no way to get the answer out of mysql directly; however you can do a pretty good job of estimating it:

For innodb, PRIMARY index is part of the data itself and shows up in data_length. If a table has no other indexes index_length is 0.

For every row in your table, every non-PRIMARY (UNIQUE or INDEX) index stores

• 6 bytes overhead
• every column indexed
• every column in PRIMARY not already in the index
• 1 bit per possible NULL column, rounded up to the byte

The size of a column in an index is the space the actual data takes up plus 1 or 2 bytes of length per VARCHAR/TEXT/BLOB. NULLs take no space.

There is an additional overhead of about 1.4x to 2.8x for the B-tree pages and non-full leaf pages. In my databases it looks to be around 2x usually.

FKs should not have any space overhead beyond the index they require.

mysql docs - source

From your question, I'm not completely sure if you're inserting multiple values per query, but you definitely should be. With MyISAM as the engine, there should not be a performance difference between the index being UNIQUE or PRIMARY; MyISAM doesn't treat them differently in this case. If you were using InnoDB, however, there would be a difference since it stores the data in primary key order. If you don't need the id column, removing it and making domain the primary key would help performance.

Changing the collation should help since ascii is much simpler than utf8, but you might want to use ascii_general_ci instead of ascii_bin since domain names are case-insensitive.

One other way to do the queries would be to get the number of rows, INSERT DELAYED, flush the delayed writes, and then get the new row count. The difference in the counts would be the same as the affected rows. However, I don't think this would be significantly faster, but it would make the process more complex.