Told or Known

Jun 18 2008 ∞

MySQL performance tip: Prefetch using "IN"

Let’s say you’re iterating through a list of items to display, each of which requires a separate lookup, like a posts-to-users mapping.

Normally you’d do it like this:

foreach ($posts as $p) {
    // Looks up e.g. 'SELECT * FROM users WHERE id = ?', $p->user_id
    $user = $p->user();

    // Render some stuff...
}

But this will query the database once per post. If you’re showing 20 posts per page, that’s 20 queries. Bad.

Instead, accumulate the secondary-table IDs first, then fetch them all at once with the IN clause:

$user_ids = array();
foreach ($posts as $p) { $user_ids[] = $p->user_id; }
// Then look up 'SELECT * FROM users WHERE id IN (...)'

Then you get all of your secondary-table objects with one query. And MySQL is remarkably good at handling big IN clauses.

I’ve been thinking for a couple weeks, but not capturing (GTD/ZTD speak for writing down) the idea of catching Marco on IRC or emailing him and encouraging him to write some “programming tip” type posts. Now I can just reblog and say what I just said. Double thanks, Marco.

Page 1 of 1