TagMySQL

Partitioning InnoDB tables by time-based pseudo-sequential UUIDs

2022-09-25

18 Min read

Time Twister Abstract Concept Illustration with Twisted Vintage Clock

Partitioning has multiple uses – spreading load onto multiple disks, cold storage of older data on cheaper disks, and probably others. Most importantly though, partitions are not for performance.

The main use case I’m going to explain is time-based partitioning as a tool for limiting the scope of stored data. This could be required to comply with a data retention policy or simply to save money on disk space.

(more…)

Read on Read later

“Using index” doesn’t necessarily mean fast lookup in MySQL

In Databases

2021-03-21

3 Min read

Using index in the output of EXPLAIN query

While dealing with slow MySQL queries my go-to approach is to quickly run an an EXPLAIN on the query and check if the indices I expect to be used are actually being used. A quick check is to look at the “Extra” column of the explain output to see if it contains the term “Using index”

However, I found out that “Using index” does not mean what I thought it meant.

(more…)

Read on Read later

TagMySQL

Pages