Denormalizing the tables
My latest project is to denormalize some of the heavily used tables. The core ‘person’ table which is used for displaying profiles is fine but the message, wink and friend invite tables usually need to be joined with the person table.
I’ve found that joins in MySQL are very expensive, the slow down is in the region of hundreds or thousands of times.
So I am going to amend the message directory so that instead of simply using a foreign key to reference the person who sent or received the message it will actually contain the basic details of the person. This will remove the need for the join.
It will give me a problem if people change their username, person type or names but presently only the latter can be changed through the web interface and if people do change their names then they will just have to accept that messages sent may have their previous names on them - which is just what happens with ordinary email anyway.
At the same time I have also decided to re-organize the photos directory. At the moment there is a directory for every member with uploaded photos in the top level which means I have a single directory containing 16,000 photo directories. To be honest the performance of this on ext3 isn’t bad but it’s clearly not going to scale. Under the new regime I have a maximum of 10,000 entries per directory.
I hope this works out as well as the summary tables has. Since I made that change the member home page has disappeared completely from the log of slow pages (slow = more than 400 ms).