Normalization vs Redundancy

[jayant]

I have two tables in MySQL

TableA

Col1
Col2
Col3

TableB

Col1
Col4
Col5

The relationship between TableA and TableB is 1:M

table A will have approx 10,000 rows while tableB will have approx 100,000 rows.

I must select a row from TableB that is satisfies a complex condition and Col3 of TableA (parent table) also satisfies some condition.

Which will give me better performance:
1) keep tables normalized, use a join to check the condition
2) store Col3 in TableB as well and update all rows in TableB if the value of Col3 changes in TableA.

There have to be approx 500,000 SELECT queries run on the database daily. Ideally the value of the Col3 should change after each select query but I can postpone that till approx 10,000 queries after which I must update Col3.

[Oersoep]

Do some testing and you'll see. Give them both a batch an log microtime, calculate approx. time of arrival and log the load the process generates.

Does all data need to be up-to-date?
If not, you can make a third table containing all cols and update that one every x minutes/hours/days. That way managing your table will always be consistent and normalized.

[pohopo]

In your case I would choose 1 as you do not have to worry about redundancy, you do not need to worry about keeping the data consistent with some job/trigger, and though 2 will be slightly faster the difference will be very small as long as you properly index your tables.

If you do go with 1 and you find it is slow then you might have to switch to 2 for the speed.