Skip to Main Content
Facebook Cookbook
book

Facebook Cookbook

by Jay Goldman
October 2008
Intermediate to advanced content levelIntermediate to advanced
434 pages
12h 7m
English
O'Reilly Media, Inc.
Content preview from Facebook Cookbook

Advanced Relational Database Table Optimization

Pete Forde with Rowan Hick (see their bios in Contributors)

Problem

I have the problem everyone wants: popularity! Even with a proper caching strategy and sensible indexes in place, my application database is quickly becoming a bottleneck. Like most web applications, my resources tend to be frequently read, with relatively infrequent updates. I have adhered to common design best practices; my schema is properly normalized. What more can I do to scale my database throughput capacity?

Solution

The solutions listed next won’t work for everyone, but there’s a pretty good chance that they’ll solve some of your problems.

Denormalization

Are you consistently joining the same tables together? You might consider adding redundant columns to your high-traffic tables so that you can reduce or eliminate joins. For example, given a Users.province_id fk> Province.id relationship, it might make sense to create a Users.province column and store the literal text value directly on the Users table.

Of course, they call these databases “relational” for a reason! By giving that up, you accept the burden of updating the same data in multiple places. You might have to change how your application retrieves these new redundant values. Profile to find bottlenecks, and weigh the tradeoff cost of each optimization.

In most cases, it’s not recommended that you drop or abandon your normalized join tables completely; you should add redundant columns only for data that ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Beginning PHP 5.3

Beginning PHP 5.3

Matt Doyle
HTML5 Geolocation

HTML5 Geolocation

Anthony T. Holdener

Publisher Resources

ISBN: 9780596156695Errata