CODEWITHSUNDEEP
mysql
sg31
sg31

Reputation: 11

MySQL Indexes: What is Size field in Indexes?

What is the Size field in Indexes and how it works?

enter image description here

Upvotes: 0

Views: 346

Answers (2)

Schwern
Schwern

Reputation: 164809

This is how much of the value will be uniquely indexed. You can index the whole value on a one-to-one basis, or you can index just a prefix which might put multiple values into one bucket. This is a performance/space trade off.

Here is a simplified example.

If you were to create an index with a single character...

create table animals (
    name varchar(255),
    index(name(1))
);

That will only index the first character of each name.

index       name
----------------
A           Ape
A           Aardvark
A           Ant
A           Anteater
B           Baboon
C           Cat
D           Dog
D           Dingo

So when you query where name = 'Aardvark' it will use the A index to find a list of Ape, Aardvark, Ant, Anteater and search it. The index improves the performance of the query, but there's still some searching to do.

Let's say you had index(name(3)).

index       name
----------------
Ape         Ape
Aar         Aardvark
Ant         Ant
Ant         Anteater
Bab         Baboon
Cat         Cat
Dog         Dog
Din         Dingo

Now when you query where name = 'Aardvark' it will use the Aar index to find just Aardvark and will perform fast. But if you search for where name = 'Ant' it will use Ant to find Ant, Anteater and have to search that list.

You need to make the decision between index size and performance that fits your data and queries.

A practical example, say I'm storing SHA-1 checksums as text. Those are 40 characters long. But for all practical purposes the first 7 or 8 characters are very, very likely to be unique. So I store all 40 characters, but only index the first 8.

checksum char(40),
index(checksum(8))

Now where checksum = '97531bc4cb33c00f3e9ff10d65386b8e96cdae3d' will use the 97531bc4 index and likely produce a single value. This potentially saves a lot of space without any impact on performance.

Upvotes: 1

Barmar
Barmar

Reputation: 780974

When you create an index, you can specify that only a prefix of the value should be included in the index. This is the size of the index. It's optional for most datatypes, but required for TEXT and BLOB columns. It's also needed for any column if its length exceeds the limit on index size; for instance, InnoDB's index size limit is 767 bytes for some table formats; if you want to index a VARCHAR(1023) column, you'll need to specify a prefix size less than 768.

See Column Prefix Key Parts

Upvotes: 1

Related Questions