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.

Marco.org