Impact of adding new column/index to existing table?

July 10, 2017, at 7:25 PM

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.

Answer 1

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.

Rent Charter Buses Company
Substract (Query1 - Query2) in same query ? Possible

Substract (Query1 - Query2) in same query ? Possible

It's possible too substract in a Query ?

MYSQL count hours passed exluding weekends

MYSQL count hours passed exluding weekends

I've come to a halt, I made a funtion inside mysql counting hours but it keeps giving me faulty numbers, I've been looking at it for hours and I just can't see what I am doing wrong