Markus Winand's Blog

Posts Tagged ‘database’

Finding the Best Match With a Top-N Query

In Performance on 2010-09-29 at 11:16

There was an interesting index related performance problem on Stack Overflow recently. The problem was to check an input string against a table that holds about 2000 prefix patterns (e.g., LIKE 'xyz%'). A fast select is needed that returns one row if any pattern matches the input string, or no row otherwise.

I believe my solution is worth a few extra words to explain it in more detail. Even though it’s a perfect fit for Use The Index, Luke it’s a little early to put it as an exercise there. It is, however, a very good complement to my previous article Analytic Top-N queries—so I put it here.

Although the problem was raised for a MySQL database, my solution applies to all databases that can properly optimize Top-N queries.

Read the rest of this entry »

Clustering Factor: Row Migration’s Victim

In Performance on 2010-03-09 at 10:15

This article describes the effects of a high row migration rate on the clustering factor and the optimizer’s ability to select the best execution plan.

In my previous article—Row Migration and Row Movement—I have demonstrated that the “insert empty, update everything” anti-pattern can lead to 100% row migration. This article continues the research on row migration and unveils surprising effects on the clustering factor. To be precise, the clustering factor can become completely bogus in presence of a very high row migration rate. Once the clustering factor is “wrong”, it’s just a finger exercise to construct an optimizer trap and proof that row migration can affect the query plan.

Read the rest of this entry »