Are there any benefits to ordering table fields in specific way in MemSQL? For example, putting fields of a specific data type, length-variability, nullability, or key-inclusion at the start or end of a table, either for read performance, write performance, or storage optimization. I’ve heard of such benefits for other database management systems. I don’t recall what they were, but here is one example that I found for SQL Server.
There’s no significant benefit to changing the order of fields. MemSQL row store tables use a fixed width field format for all fields, and the format is independent of field order. If you are optimizing things to the highest possible degree, it might make sense to group together frequently accessed fields to benefit from locality of reference in cache memory. But I expect this to be of little benefit and not a worthwhile place to spend time tuning. Other things, like choosing the right indexes, will matter much more.
Similarly, there’s no benefit to changing the order of columnstore table fields. Columnstore table fields are stored independently of each other, and their space usage and layout doesn’t depend on column order.
1 Like