Assemble the first answer and take its price

The first answer on the panel

The end of the first shift. The supervisor comes for the answer to the top line of his sheet: which of the regular visitors most often sees the thing through — orders a material and gets it into his hands instead of abandoning it half-way.

For this question you do not need to go down to the raw events. The room already has a mart, dm_vydacha: one row per visit, gathering how many actions the person took, how many materials he touched, how many he ordered and how many he received.

This is your mart. The archivist on desk duty assembles it during his shift, and the visitors read it all day long, one question at a time.

Do not confuse it with the mart that the intake level’s belt fills at night. That one has a different writer, a different reader and a different key.

We will assemble the first answer out of this chapter’s techniques: aggregates with the condition inside, suffixes, and the extreme value. But the work does not end at a correct number. We take the cost of the answer straight away — by the rule QUERY laid down back in the first lesson.

How to read someone else’s answer

The answer is ready. What matters now is something else: which numbers show that a query is reading more than it needs.

For a first check, two numbers are enough: how many rows were read and how many marks were touched.

The row count shows how much of the table the engine had to read at all. The marks show how many stretches of the layout that read landed in.

If as many rows were read as the table holds altogether, then nothing could be discarded up front. The query went through the whole table — even if a single row is left in the answer.

Now let us see where the difference in cost between two similar questions comes from.

The room’s mart is laid out by visitor and time. So the question “what did this visitor do” lands on the layout: the engine can skip the rest before it reads anything.

The question “what happened on such-and-such a day” fits this mart worse. The date is not in the key, so the engine has to look through every part in turn.

In the next cell the two questions stand side by side. Look not at the size of the answer, but at the cost of the read.

Eight chapters — eight layers of one answer

The first cell of the panel is done, but the panel itself is built for eight. Every chapter after this one adds another layer to the same desk:

1. What it is measured in — an answer that has a cost. This chapter.
2. The accuracy of counting — a number with a note beside it saying how exact it is.
3. Lists — an answer that would have called for a join in a row store.
4. The visitor’s path — did he get there, in what order, did he come back.
5. Time — a daily series with no gaps in it.
6. Layout — the same answer for half the cost, and proved by a number.
7. Joins — an answer with a lookup that fitted into memory.
8. The desk handed over — all of it together, on two machines, and the exam.

Chapters do not start from a blank page. The techniques stack up, and the three-column log runs through the whole course: question, technique, cost.

QUERY: The first cell is filled. Notice this: asking about a visitor came cheap. Ask the same mart about a day — and the cost changes. Not today; just remember the difference.