Too Many Afterthoughts The greatest ideas are often far too late

TagPerformance

Choose the right primary key to save a large amount of disk I/O

Cardboard boxes

Imagine you’re working in a large book warehouse and in charge of new arrivals. There’s a separate, digital system for metadata like authors, categories, etc., so the only information you’ll use during storage is the inventory number. Each book is identified by a unique number with many digits and all books must be findable by their number. To make handling quicker, books are packed in boxes, ordered by the inventory number. When looking for a book, the box must be identified first. Therefore, each box is labeled by the lowest inventory number it contains and the first number that’s in the next box.

Your job in the arrivals room is to pick up books-to-be-stored one by one, assign them a new inventory number in the metadata system, label them by number, and put them in a box as mentioned before. Now, the room is quite small and if you run out of space, you’ll need to move the filled boxes into the basement, which might be two floors down.

(more…)

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

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…)
Too Many Afterthoughts The greatest ideas are often far too late

Pages