Writing efficient queries with Select and conditionsfor Drupal 11 , 10 , 9 , and 8

Imagine a librarian who, instead of walking straight to the right shelf, reads every book in the building before handing you the one you asked for. That's what a badly-written database query does to your Drupal site: it makes the database work far harder than it needs to, on every single page load. The Select API — the same one you use to query custom tables or core entities — gives you precise control over exactly which rows and columns come back, and getting that precision right is the single highest-leverage skill in database performance work.

What you'll learn in this lesson

• How to build a Select query that only asks for the fields you actually need
• Why condition() calls should filter as early and as specifically as possible
• The difference between filtering in the database versus filtering in PHP afterward — and why it matters enormously at scale

Selecting only the fields you need

$query = \Drupal::database()->select('node_field_data', 'n');
$query->fields('n', ['nid', 'title', 'created']);
$query->condition('n.status', 1);
$query->condition('n.type', 'article');
$query->orderBy('n.created', 'DESC');
$query->range(0, 10);
$result = $query->execute()->fetchAll();

Notice fields('n', ['nid', 'title', 'created']) names exactly three columns — not SELECT *. Every extra column MySQL has to read and hand back to PHP costs real time and memory, especially on a table like node_field_data that can carry dozens of columns once contributed modules add their own fields. This habit alone — asking only for what you'll actually use — is often the single biggest, cheapest performance win available to you.

Filtering in the database, not in PHP

A mistake that looks harmless in code review but is catastrophic in production: loading every row and then filtering with an if statement in PHP.

// Don't do this — pulls every article into PHP memory first.
$all_articles = \Drupal::database()->select('node_field_data', 'n')
  ->fields('n')
  ->condition('n.type', 'article')
  ->execute()
  ->fetchAll();
foreach ($all_articles as $article) {
  if ($article->status == 1) {
    // ...
  }
}

Add the status condition to the query itself instead. The database has indexes and query-planning logic built for exactly this job; PHP looping over rows it should never have received in the first place is slower at every scale, and gets dramatically worse as the table grows — a 500-row table won't show the difference, a 5-million-row table will bring your site to its knees.

range() — never fetch more rows than you'll display

$query->range(0, 10);

If a page only ever shows the 10 most recent articles, range(0, 10) tells the database to stop after the tenth matching row — it doesn't compute, sort, and hand back thousands of rows just so your PHP can throw away all but ten of them. Pair this with orderBy() on an indexed column (you'll build the index itself in the next lesson) and this single line can turn a multi-second query into a near-instant one.

Quick check: you need to display just the titles of the 5 newest published pages. Which change matters more for performance — narrowing fields() to just title, or adding range(0, 5)? (Both matter, but range() usually wins by a wider margin: it prevents the database from even considering rows beyond the fifth, while narrowing fields mostly saves on data transfer size for however many rows were already scanned.)

Key takeaways

• Name exact columns in fields() instead of pulling every column from a table — every unneeded column has a real cost.
• Put every filter you can into condition() calls on the query itself; never load rows into PHP just to discard most of them with an if.
• range() caps how many rows the database even has to finish processing — use it whenever you have a known display limit.
• Use countQuery() or a 1-row range() when you only need to know whether something exists, not the full row data.

Coming up next

A well-written query can still be slow if the database has to scan an entire table to satisfy a condition(). Next, we add indexes to a custom table so the database can jump straight to matching rows instead of checking every one.