MYSQL table and column naming convention

Question

I am curious to know what is best naming convention in terms of performance for mysql table names and column names. I am designing a new database for my project.

What I have used so far is use descriptive table/column names which sometimes seems long but I think it helps in easily understanding the use/function of a table.

For example see below DDL:

CREATE TABLE `product_configuration` (
  `product_configuration_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(20) NOT NULL,
  `product_size_id` int(20) NOT NULL,
  `product_color_id` int(20) NOT NULL,
  `price` float NOT NULL,
  `image` varchar(255) DEFAULT NULL,
  `locked` tinyint(1) DEFAULT '0' COMMENT '1=locked,  0 =unlocked. if locked then this row can''t be deleted/updated',
  `active` tinyint(1) DEFAULT '1' COMMENT '1=active, 0=inactive and wont display on frontend',
  PRIMARY KEY (`product_configuration_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2342 DEFAULT CHARSET=latin1

And another DDL in which I use the primary key from above DDL as foreign key :

CREATE TABLE `product` (
  `product_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_name` varchar(255) NOT NULL,
  `product_description` varchar(255) NOT NULL,
  `product_image` varchar(255) NOT NULL,
  `price` float NOT NULL,
  `active` tinyint(1) NOT NULL COMMENT '1=active, 0=inactive',
  `date_added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `product_type_id` int(11) DEFAULT NULL,
  `date_modified` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`product_id`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=latin1

Basically I use singular table names with table name as prefix in most of the column names inside that table and I keep the same name and datatype for primary and foreign keys so that I can easily know which foreign key relates to which primary key/tables.

But I wonder, do using long table/column names have performance impact when database size grows. Like instead of just using "id" as primary key I am using long "product_configuration_id".

Also if I name tables/columns in uppercase and lowercase mixed like

"ProductConfiguration"

for table name and

"ProductConfigurationId"

for column name will that have any performance impact or linux/windows environment compatibility issue.

Answer 1

Long table and column names do not have (any significant) performance impact. All tables and column references are turned into internal locators during the compilation phase of the query. So pretty much the only impact is having to query a longer query string. The parsing part of query compilation is usually ignored from a performance perspective.

The following is opinion-based. As a general rule, I follow these conventions for naming:

• Table names are in the plural, because they contain multiple entities.
• Each table (almost always) has an auto-incremented numeric primary key, which is the singular form of the table followed by Id.
• This column is the first column defined, so I can use order by 1 desc to get the most recent rows added to the table.
• The table name is not (generally) part of the column name. I always (try to) use table aliases, so including the table name would be redundant.
• Foreign key references use the same column name as the primary key they are referring to, when possible, so I can use using for joins.

I admit that these are "opinion-based", so the real answer to your question is in the first paragraph.