Member Avatar for minitauros

Okay so me and a friend of mine are discussing the following:

If you have 1.

a table with 10 fields, each a varchar(255)

or 2.

a table with 10 fields, each a varchar(1billion)


Which would be better, performance wise?

I say it won't matter, because MySQL reads the field names before it scans the content. My friend says it will matter, because the field size always impacts the performance of the scanning process.

  • 2 Contributors
  • 6 Replies
  • 115 Views
  • 12 Hours Discussion Span
  • Latest Post Latest Post by minitauros

Dani AI

Generated

As asked and began to point out, the important distinction is between declared maximum size and actual stored size. VARCHAR is variable‑length: MySQL stores the actual bytes plus a 1‑ or 2‑byte length prefix, so the server does not automatically read or store the declared maximum for every row. See the MySQL docs on CHAR/VARCHAR storage for details: CHAR and VARCHAR types.

When the declared maximum does matter:

  • The MEMORY engine treats variable types as fixed‑length, so huge declarations waste RAM (MEMORY storage engine).
  • Very large columns contribute to row‑size limits and can force different storage behavior (InnoDB can store long values off‑page depending on row format).
  • Sorting/GROUP BY that uses internal temporary tables can become slower if large text/BLOB columns are involved because MySQL may create on‑disk temporaries (Internal temporary tables).

Practical, immediate guidance:

  • Choose realistic max lengths. If values are frequently long, use TEXT/BLOB (and appropriate indexing strategy) or move large fields to a child table to keep the "hot" row small.
  • For measuring current impact, run queries like:
    SELECT MAX(CHAR_LENGTH(col)), AVG(CHAR_LENGTH(col)) FROM your_table;

    and profile queries with EXPLAIN and SHOW STATUS for disk reads.

Direct answer to the filled‑to‑max scenario: if those varchar columns are actually filled with huge strings, performance will degrade simply because there is more data to read, write, index, and sort. If they remain small in practice, the large declared maximum alone gives little penalty in most engines (except the MEMORY engine and certain temp/table format cases noted above).

Recommended Answers

All 6 Replies

Member Avatar for debasisdas

varchar(1billion) is not supported.

Member Avatar for minitauros

I was just giving an example ;). Let's say "the largest number available for varchar" then.

Member Avatar for debasisdas

it won't matter.

Member Avatar for minitauros

So no performance difference? :o

Member Avatar for debasisdas

Should not be, but it may depend on how you use the column and what is stored in the column.

Member Avatar for minitauros

That's the question :). Would it make difference if the varchars were used to their max? So if they were completely filled?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.