Should I denormalize my table, should I denormalize a FK column

Question

I have a table with a column created_time (DateTime) and a foreign_key i need to retrieve all the rows with x foreign_key that the last row created with x foreign_key was lets say over 2 minutes (and retrieve all the rows with that date)

My question: what is better for performance, to add a column that stores the last row created date every time i create a row, or to use a more complex query to retrieve those rows with the last row joined?

My opinion is that from performance point of view the things are OK the way they are. You dont need to try to tune something that most probably will not need running. As long as you have correct indexes everything will be just fine. — Alexander Petrov, Jul 10 '16 at 07:48
doesn't answer my question... everything is index and all is ok, but i have a new feature to make that will retrieve rows that were created with a certain time difference from the last — WalksAway, Jul 10 '16 at 07:49
If I understand correctly your table structure. You are asking if you should denormalize the create time. If I understand correctly the question. Then Dont. Make a Join query. — Alexander Petrov, Jul 10 '16 at 07:50
yes... so your saying to use a more complex query that will join the last row in... — WalksAway, Jul 10 '16 at 07:51
From performance point of veiw the performance decrease with correct indexes set will be negligible. You need to have strong reasons to do denormalization. I dont see such a strong reasoning here. Plus the query will not be thaaatt.... complex :) — Alexander Petrov, Jul 10 '16 at 07:52
^^ i know but its my way to describe using one query instead of a simpler query :P — WalksAway, Jul 10 '16 at 07:54
you should only denormalize once you can demonstrate that you have a performance problem, and that you cannot solve this problem by adding hardware, or changing the application logic. — Neville Kuyt, Jul 10 '16 at 09:16

score 4 · Accepted Answer · answered Jul 10 '16 at 07:56

4

Essentially the question is "Should I denormalize the created_time?" The answer here is that from performance point of view the difference with correct indexes set is negligible and if denormalized this will lead to data duplication across your schema which may trigger one day other problems. From this point of view my recommendation is to not do it and use the expressive powers of SQL to retrieve what you need.

answered Jul 10 '16 at 07:56

Alexander Petrov

9,204
31
70

i reached a bit of a problem in constructing that query if you could give me an example of one that would be great... – WalksAway Jul 10 '16 at 09:18
You need to play a little bit with the time functions check this out for ideas:http://stackoverflow.com/questions/4471041/how-to-retrieve-records-for-last-30-minutes-in-ms-sql The functions depend on you Database provider so they varry across DB vendors. – Alexander Petrov Jul 10 '16 at 09:23
my problem is joining only the last row :/ – WalksAway Jul 10 '16 at 09:24
You need to join by the foreign key and then filter out the date the way you want it. You should not join by the date. – Alexander Petrov Jul 10 '16 at 09:24
Ok so you want only the last record ? – Alexander Petrov Jul 10 '16 at 09:25
wasn't planing on joining by date, but joining all the rows like that will be massive... consider i have 10k rows with the same foreign_key, joining each one with every related one... – WalksAway Jul 10 '16 at 09:26
For a DB server joining 10000 rows is nothing. Seriously stop over running it :) – Alexander Petrov Jul 10 '16 at 09:27
Do something like select * from A join (select maxDate(date) from table B where bla bla bla ) – Alexander Petrov Jul 10 '16 at 09:30
http://stackoverflow.com/questions/19432913/select-info-from-table-where-row-has-max-date – Alexander Petrov Jul 10 '16 at 09:31
Yeah but these are micro-optimisations. It is good to optimize, but at the same time it is important to differentiate micro optimizations from the real things that need to be optimized. – Alexander Petrov Jul 10 '16 at 09:37

score 0 · Answer 2 · answered Jul 10 '16 at 09:03

By denormalizing the created_time you are heading towards flat tables design and will end up creating a NoSQL DB , which I believe is not an approach you would like to follow .Since you are using SQL , you should use the SQL Query power with EXPLAIN keyword and notice the time it takes to run your query . I believe it won't take much time to process all your rows.

Should I denormalize my table, should I denormalize a FK column

2 Answers2