CODEWITHSUNDEEP
mysqldatabaseweb-applications
Dean
Dean

Reputation: 8065

Will adding large columns (VARCAHR) to MySQL table have significant impact on performance?

I am sorry if the question is somewhat newbish, I have little knowledge of databases.

I am working on a web based application to manage the organization users. The number of records will be 10,000-20,000. I wanted to add another column to the table to allow the staff to add notes to the users record, just in case. I assume this will not be utilized very often.

Will adding VARCHAR column to the table have a significant performance impact on the DB? Would you suggest separating the notes to another table, relational to the users table?

Upvotes: 0

Views: 74

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Adding a large varchar() column can have an impact on performance, but not necessarily. The key idea here is storing data on pages in the database. Reading a table requires reading all the pages that contain records. Normally, many records fit on a single page. If a record is quite large, then only a few records fit on a page. This can affect performance because more pages need to be read (for certain queries) or the records don't all fit into memory.

In any case, there are mitigating circumstances:

  • You can have the field and never populate it. Hence, the record sizes are never very large.
  • All your queries are "needle-in-the-haystack" queries, where you are using an index to fetch only one or a few records.
  • If you actually store the column as text, then it is a separately allocated object.
  • If you only have a "few" users, so the total data size is small (say measured in a few megabytes), then performance probably isn't an issue.

There are also methods to mitigate performance, such as putting the longer columns in a separate table.

So, the impact on performance depends on the overall size of the database, its structure, and how it will be used.

Upvotes: 1

Related Questions