Mysql – Does a huge key length value for a mulibyte column affect the index performance

encodingexplainindexMySQLperformance

When I look at the EXPLAIN results, the key len value is always calculated based on the actual column length multiplied on the maximum number of bytes for the chosen encoding. Say, for a varchar(64) using utf8 encoding the key len is 192.

Does this number affect performance in any way and should I reduce it when possible? I mean, does it make MySQL to reserve some space somewhere that remain unused, or it's just a maximum possible value while the used space is based on the exact data length?

So the actual question is: if I have a column that contains only Latin letters and numbers, should I change its encoding to latin1 from utf8 in regard of the space occupied by the index/overall index performance?

Best Answer

The number is a worst-case estimate. Remember that EXPLAIN doesn't typically read the data, so it has no idea if your data contains only a short string of 1-byte characters on average, or if it contains a full 64 characters each 3-bytes per character.

The performance of the query is much more influenced during execution by the number of pages read, and that varies more directly with the actual data. Short string values will allow more index entries to fit per page, therefore fewer pages need to be read. This is something the EXPLAIN cannot predict.

I would recommend do NOT change your encoding to latin1 solely for the sake of making EXPLAIN output "less confusing." This is not a good reason, and it's bound to limit you later if you want to support UTF-8 characters in that column.

In today's era, there's virtually no reason to use the latin1 character set. I recommend setting all strings to utf8mb4 as a default.

If the column has something like strings of hex digits for a uuid or a hash value, that's different. You could set the character set to ascii. But that's for a reason of the data, not for the convenience of someone reading the EXPLAIN.

Related Solutions

Mongodb – How do databases store index key values (on-disk) for variable length fields

You can store your index as a list of fixed-size offsets into the block containing your key data. For example:

+--------------+
| 3            | number of entries
+--------------+
| 16           | offset of first key data
+--------------+
| 24           | offset of second key data
+--------------+
| 39           | offset of third key data
+--------------+
| key one |
+----------------+
| key number two |
+-----------------------+
| this is the third key |
+-----------------------+

(well, the key data would be sorted in a real example, but you get the idea).

Note that this does not necessarily reflect how index blocks are actually constructed in any database. This is merely an example of how you might organise a block of index data where the key data is of variable length.

Postgresql – Would index lookup be noticeably faster with char vs varchar when all values are 36 chars

No. No gain at all. The manual explicitly states:

Tip: There is no performance difference among these three types, apart from increased storage space when using the blank-padded type, and a few extra CPU cycles to check the length when storing into a length-constrained column. While character(n) has performance advantages in some other database systems, there is no such advantage in PostgreSQL; in fact character(n) is usually the slowest of the three because of its additional storage costs. In most situations text or character varying should be used instead.

Bold emphasis mine.

char(n) is a largely outdated, useless type. Stick with varchar(n). Without need to enforce a maximum length, varchar or text are a tiny bit faster. (You won't be able to measure a difference.)

If all strings are exactly 36 characters in length, there is no storage saving either way, not even a minuscule one. Both have exactly the same size on disk and in RAM. You can test with pg_column_size() (on an expression and on a table column).

And if all strings must have 36 characters, rather make it text with a CHECK (length(col) = 36) constraint enforcing exact length, not varchar(36) only enforcing max. length.

Any downsides of using data type “text” for storing strings?

You didn't ask for other options, but I'll mention two:

COLLATION - unless you are running your DB with the "C" collation. Collation is often overlooked and possibly expensive. Since your strings don't seem to be meaningful in a natural language, there is probably no point in following COLLATION rules. Related:
- How do I efficiently get "the most recent corresponding row"?
- EXECUTE within function not using index?
Extensive benchmark comparing (among other) the effect of COLLATE "C" on performance:
- Slow query ordering by a column in a joined table
UUID, obviously. Your string suspiciously looks like a UUID (32 hex digits plus 4 delimiters). It would be much more efficient to store these as actual uuid data type, which is faster in multiple ways and only occupies 16 bytes - as opposed to 37 bytes in RAM for either char(36) or varchar(36) (stored without delimiters, just the 32 defining char), or 33 bytes on disk. But alignment padding would result in 40 bytes either way in many cases.) COLLATION would be irrelevant for the uuid data type, too.
```
SELECT '922475bb-ad93-43ee-9487-d2671b886479'::uuid
```
This may be helpful (last chapters):
- Convert hex in text representation to decimal number
See also:
- What is the overhead for varchar(n)?
- What is the optimal data type for an MD5 field?

Best Answer

Related Solutions

Mongodb – How do databases store index key values (on-disk) for variable length fields

Postgresql – Would index lookup be noticeably faster with char vs varchar when all values are 36 chars

Related Question