Kyle Bragger : MySQL performance tip: Prefetch using "IN"

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.

If you don’t follow this advice, shame on you. I’m always surprised at how nutty or just plain stupid some of the SQL I come across is. See also: EXPLAIN

Kyle Bragger

2008-06-18

MySQL performance tip: Prefetch using “IN”