The Art of SQL

Chapter 8. Weaknesses and Strengths

Recognizing and Handling Difficult Cases

No one can guarantee success in war, but only deserve it.

—Sir Winston Churchill (1874–1965)

There are a number of cases when one has either to fight on unfavorable ground, or to attack a formidable amount of data with feeble weapons. In this chapter, I am going to try to describe a number of these difficult cases; first to try to sketch some tactics to disentangle oneself with honor from a perilous situation, and, perhaps more importantly, to be able to recognize as soon as possible those options that may just lead us into a trap. In mechanics, the larger the number of moving parts, the greater the odds that something will break. This is an observation that applies to complex architectures as well. Unfortunately, snappy, exciting new techniques—or indeed revamped, dull old ones—often make us forget this important principle: keep things simple. Simpler often means faster and always means more robust. But simpler for the database doesn’t always mean simpler for the developer, and simplicity often requires more skills than complexity.

In this chapter, we shall first consider a case when a criterion that looks efficient proves rather weak but can be reinvigorated, and then we shall consider the dangers of abstract “persistency” layers and distributed systems. We shall finally look in some detail at a PHP/MySQL example showing the subtleties of combining flexibility with efficiency when a degree of freedom is left ...