• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL
Help

9. Case Studies > Database Table Growing Without Limit

Database Table Growing Without Limit

The home page of a particular newspaper web site typically takes about one second to be dynamically generated from a database query. A new “personalization” feature is added to the web page at the end of January. Every time the home page is hit, a database is now queried for news that fits the user’s preferences. This feature is well received, but monitoring shows a jump in latency when the feature was added. Worse, monitoring also shows that the time to retrieve that customized page is continuously increasing (see Figure 9-1).

Latency increase with new customization page

Figure 9-1. Latency increase with new customization page

It is determined that the database table of all news stories is being fully scanned for relevant news on each login, and that table continuously grows in size. The solution to this problem is that a new index is added to the table, mapping each story to the relevant users when it is added to the database, and latency falls back to as good as it was before the feature was added. The creation of the index is a simple SQL statement that looks like this:


PREVIEW

                                                                          

Not a subscriber?

Start A Free Trial


  
  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint