MySQL Performance: MySQL => To normalise or not to normalise?

To normalise or not to normalise? [message #733]

Fri, 16 February 2007 09:38

al.james
Messages: 16
Registered: October 2006

Junior Member

I am designing a quite simple web app, but I am trying to design it so that it will scale well.

Basically I have an item table and a user table. A user may have many items and each item has exactly one user.

Every time an item is retrieved the corrisponding user must also be found. Currently, when listing items, the users username is retrieved using an inner join on the user_id field that is in both tables.

Reading some stuff on high-performance mysql and website design, it suggests de-normalization is something you should consider.

So I could de-normalise this schema to insert a 'cached copy' of the user's username in the item table.

This will save me a join in the most common query in the application. This is good.

However, I will face the issue of keeping the item.username field upto date. So, if a user changes their username, all their items will need to be updated. Given mysql's table locking rules (and the fact that I want the app to scale well in a replicated setup), is this a good idea?

[Updated on: Fri, 16 February 2007 09:38]

Report message to a moderator