Resolving 'Row size too large' Error in MySQL CREATE TABLE Queries

When creating a table in MySQL, developers might encounter the error 'Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535'. This error occurs when the total size of all columns exceeds the allowed limit.

Error Analysis and Causes

MySQL imposes a maximum row size limit of 65535 bytes for certain table types, excluding BLOBs. The size is calculated based on defined data types, with varchar columns contributing their maximum length. For instance, in the original query, multiple varchar(8000) fields can easily surpass 65535 bytes in total.

-- Original query causing error
Create Table PerformaceReport (
  campaignID int,
  keywordID bigint,
  keyword varchar(8000),
  avgPosition decimal(18,6),
  cost int,
  clicks int,
  conv1PerClick int, 
  impressions int,
  day datetime,
  currency varchar(8000),
  account varchar(8000),
  timeZone varchar(8000),
  adGroup varchar(8000),
  adGroupState varchar(8000),
  approvalStatus varchar(8000),
  lowestPosition varchar(8000),
  campaign varchar(8000),
  campaignState varchar(8000),
  convManyPerClick int,
  totalConvValue decimal(18,6),
  maxCPCSource varchar(8000),
  clientName varchar(8000),
  destinationURL varchar(8000),
  device varchar(8000),
  firstPageCPC int,
  isNegative bit,
  matchType varchar(8000),
  maxCPC varchar(8000),
  maxCPM varchar(8000),
  highestPosition varchar(8000),
  qualityScore int,
  keywordState varchar(8000),
  viewThroughConv int
)

Solution Implementation

To resolve this error, an effective approach is to convert varchar(8000) fields to TEXT type. TEXT columns are stored as BLOBs and do not count towards the row size limit. The modified query is shown below:

-- Modified query using TEXT instead of varchar(8000)
Create Table PerformaceReport (
  campaignID int,
  keywordID bigint,
  keyword text,
  avgPosition decimal(18,6),
  cost int,
  clicks int,
  conv1PerClick int, 
  impressions int,
  day datetime,
  currency text,
  account text,
  timeZone text,
  adGroup text,
  adGroupState text,
  approvalStatus text,
  lowestPosition text,
  campaign text,
  campaignState text,
  convManyPerClick int,
  totalConvValue decimal(18,6),
  maxCPCSource text,
  clientName text,
  destinationURL text,
  device text,
  firstPageCPC int,
  isNegative bit,
  matchType text,
  maxCPC text,
  maxCPM text,
  highestPosition text,
  qualityScore int,
  keywordState text,
  viewThroughConv int
)

Data Type Optimization Recommendations

Beyond using TEXT, it is advisable to optimize data type selection. For example, the currency field might only require varchar(3) to store ISO codes, rather than 8000 characters. This reduces unnecessary space usage and improves database performance. When designing tables, evaluating the actual needs of each field and choosing appropriate types is key.

In conclusion, by understanding the row size limit and applying proper solutions, developers can effectively avoid the 'Row size too large' error in MySQL.