CODEWITHSUNDEEP
mysqlindexing
Paul Spencer
Paul Spencer

Reputation: 1385

Impact of adding new column/index to existing table?

I am looking to implement a new feature in a database that requires linking records to another table. The table already contains about 76000 records and only newly added records would need a link id. I'm looking at two approaches:

  1. add a new table with the source and target ids. This is possibly (?) the most performant from a database point of view but requires more logic in the application.

  2. add a new indexed column to the existing table. The value of this column would be null for existing records and would contain a integer value for new records.

What I'd like to understand is the the impact of the second approach the size and performance of the index specifically when I am selecting rows based on a specific value in the new column.

Upvotes: 0

Views: 667

Answers (1)

manassehkatz-Moving 2 Codidact
manassehkatz-Moving 2 Codidact

Reputation: 3034

When you are working with millions of records, MySQL starts to choke a bit, depending on server speed, RAM, etc. However, 76,000 records is "nothing". So go with option 2, add a field, default NULL, indexed and use that as a foreign key into the other table. Selecting on specific rows using the new column will run very fast as long as it is indexed.

Upvotes: 2

Related Questions