Yann Neuhaus and MyISAM index pointers

Yann Neuhaus has replied one of my help requesting emails:


My understanding is that you have 2 main storage strategies for MyISAM :

Variable length : tables with at least one varchar-like type (variable). Then in the MYI file we store the Offset of the row in the .MYD file

Fixed length : (only char or fixed length data types in the table) Then in the MYI file we store the row number which gives multiplied by the row length the Offset of the row in the MYD file.

Thanks for this information. I didn't know about those two kinds of pointers. Now I have a more exact idea. Thanks.

I do not really understand why multiplying the row number with the row length is faster (or better) than storing the offset.

I guess it is more economic in space to store a counter (fixed length row number) than an offset (variable length tables), because the offset is always a bigger number. Much bigger in some cases, where the record length is big. To store a big number requires more bits than a small number, so the index is smaller when using the counter and the general resource usage (buffers, I/O operations) is lower in this case.

It is also important to consider that we also have the compressed storage.

Thanks, Yann. But is the index compressed, the data or both of them?

Nevertheless, I go on my investigations, and unfortunately I still haven't found the reason for MySQL to read the MYD file when just asking for the number of results in a simple query, in which it is supposed not to be needed any information stored in the MYD file.

Thanks!