MySQL: I'm not finding this in the manual or in google, but do column names get stored in full in each row?

I suppose that this might change depending on compression, but leaving compression aside, suppose I have a normal InnoDB table with a column's name "really_long_name_for_a_column". Will this name (29 bytes) be stored in each row for every row in the table? Or is a shortened version recorded for each row or nothing at all?



The answer is of course an emphatic no. It would be a very silly thing to store them in each and every - of the possibly billion - rows.

Column names are metadata and are stored only once, in the system tables.

The format of the rows and how they know where a column ends and the next starts depend on the engine of the table. It differs between InnoDB, MyISAM, Memory, etc. Check the respective documentation for more details.

Having said that, nothing prevents you from writing - and using - an engine of your own that stores the column names in each and every row. It would still be a very silly thing to do. You should also consider where the names will be kept when the table has 0 rows, eg. immediatey after creation of the table or after it has been truncated.

  • 1
    For very sparse data such as Entity-Attribute-Value, a single JSON column can be handy. It contains the "column" names and is open-ended.
    – Rick James
    Feb 11 '17 at 19:56
  • @ ypercubeTM Thanks a lot! That was what I thought, but I couldn't find this information anywhere. I'll try to find it in the storage engines documentation, forgot to search there. @Rick James, cool tip!
    – flen
    Feb 13 '17 at 1:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.