Impact of adding new column/index to existing table?

247
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
READ ALSO
Substract (Query1 - Query2) in same query ? Possible

Substract (Query1 - Query2) in same query ? Possible

It's possible too substract in a Query ?

200
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

283