CODEWITHSUNDEEP
mysqlindexinguuid
theex
theex

Reputation: 85

Mysql: partial index on varchar

I want to do some index optimizations in a db.

I see there is an index on field varchar 32, an index on field of varchar 100.

For example varchar 32 contains an id (maybe the user id or whatever, like this string 60c487df-38e8-1c79-da00-561799874092), varchar 100 contains short string as pluto or pippo for example. (I know, this field have to be reduced in size)

First question) For these fields, drop the index and create a partial index is correct?

For example, reduce the size of the index on the varchar 32 to the first 8 char, help mysql to find the row faster? (analyzing a smaller index). Are there drawback ?

Second question) Field x varchar 100 has a full index (not partial), how mysql works when I add a row with a x="pippo"? Will Mysql fill (or reserve) all the 100 bytes for index of that column?

Thanks in advance.

Upvotes: 2

Views: 1165

Answers (2)

Rick James
Rick James

Reputation: 142346

  • A prefix index would help only because the index would be smaller. But you loose uniqueness, if that matters. Often prefix indexes are useless; beware.
  • A UUID is of fixed length of 36 characters, not 32, nor 100.
  • Since you have Type 1 UUID, you can rearrange the bits to make them chronological, thereby perhaps providing better "locality of reference". See link below.
  • Packing the 36 characters into 16 bytes would save a bunch of space. That leads to better cacheablility (especially for huge tables), hence better speed. See link.

Link

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270181

I doubt there is any reason to further "optimize" your indexes. Reducing the size of the fields in an index does reduce the size of the index. But, unless you are in a memory constrained environment, they just add unnecessary complexity.

A partial index is not going to allow MySQL to "find a row faster". Only comparing the first 8 bytes is not sufficient for finding a row, so MySQL would have to do much more work to find a given row.

The varchar() data type is a varying length character field. That means that it stores only the data in the string, plus a length in the first byte or two. This is true whether the values are in a table or in an index.

You should keep the indexes that you have and look for other ways to optimize your system -- if performance is an issue.

Upvotes: 1

Related Questions