We build WebEquipe PDF Search, a WordPress plugin that indexes PDFs page by page so visitors can search inside them, including scanned documents.
Two customers asked for the same thing a day apart: when a word appears on several pages of one PDF, show all of them, not just the best match. One of them runs a digital archive of an old magazine, with decades of scanned issues.
This post covers how we built it, and three problems we hit along the way:

  1. An N+1 query hiding in the first version
  2. A WordPress pagination bug that was older than the feature
  3. Page numbers that broke in a flipbook viewer for two-page spreads

The starting point

The plugin already stored text per page, in a table roughly like this:

CREATE TABLE wp_webequipe_pdf_search_pages ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, pdf_id BIGINT UNSIGNED NOT NULL, -- attachment ID page_number INT UNSIGNED NOT NULL, content LONGTEXT NOT NULL, KEY pdf_page (pdf_id, page_number) );

The search returned one row per PDF, with an excerpt from its best-matching page. The data for every other matching page was already there. We just weren't showing it.

Design: group, don't split

Both customers asked for each matching page as a separate result. We didn't do that. A common word on 30 pages of one PDF would push every other PDF off the results page.
Instead, each PDF still appears once, with its best match. Under it, a toggle like "14 more matches on pages 3, 4, 5…" expands the rest. Each row links straight to that page.

Version 1: the N+1 query

The first version worked, and it looked like this:

// After the main search query returns $results (one per PDF)... foreach ( $results as $result ) { $result->extra_pages = $wpdb->get_results( $wpdb->prepare( "SELECT page_number, content FROM {$table} WHERE pdf_id = %d AND content LIKE %s ORDER BY page_number", $result->pdf_id, '%' . $wpdb->esc_like( $term ) . '%' ) ); }

Two problems, both flagged in code review by our CTO:
• One extra query per result. Ten results meant eleven queries. A results page of 20 meant 21.
• Full page text in every row. content is the whole page. We only needed a snippet.

Version 2: one query, trimmed snippets, capped per PDF

The fix: run the main search, collect the PDF IDs, then fetch every extra matching page for all of them in a single query.

function wpe_get_grouped_matches( array $pdf_ids, string $term, int $limit = 20 ): array { global $wpdb; if ( empty( $pdf_ids ) || '' === $term ) { return array(); } $table = $wpdb->prefix . 'webequipe_pdf_search_pages'; $placeholders = implode( ',', array_fill( 0, count( $pdf_ids ), '%d' ) ); $like = '%' . $wpdb->esc_like( $term ) . '%'; $sql = " SELECT pdf_id, page_number, snippet, total FROM ( SELECT pdf_id, page_number, SUBSTRING( content, GREATEST( LOCATE( %s, content ) - 120, 1 ), 300 ) AS snippet, ROW_NUMBER() OVER ( PARTITION BY pdf_id ORDER BY page_number ) AS rn, COUNT(*) OVER ( PARTITION BY pdf_id ) AS total FROM {$table} WHERE pdf_id IN ( {$placeholders} ) AND content LIKE %s ) ranked WHERE rn <= %d ORDER BY pdf_id, page_number "; $params = array_merge( array( $term ), array_map( 'absint', $pdf_ids ), array( $like, $limit ) ); $rows = $wpdb->get_results( $wpdb->prepare( $sql, $params ) ); $grouped = array(); foreach ( $rows as $row ) { $grouped[ (int) $row->pdf_id ][] = $row; } return $grouped; }

What each part does:

• IN ( ... ) with one %d per ID keeps it a single prepared query.
• SUBSTRING( ... LOCATE( ... ) - 120, 300 ) returns about 300 characters around the first match, not the full page.
• ROW_NUMBER() ... rn <= %d caps each PDF at 20 rows. The cap is a setting.
• COUNT(*) OVER (...) gives the real total, so the UI can say "Showing 20 of 30 pages" honestly.
Window functions need MySQL 8.0+ or MariaDB 10.2+. On older servers, drop ROW_NUMBER() and cap the rows in PHP instead.
The result: on our test site, a search returning 10 PDFs and 70 grouped rows took about 0.1 seconds longer with grouping on than off.

Rendering: preload, then reveal

Because all rows now arrive with the page, expanding needs no AJAX call. The list is rendered hidden, and a button toggles it:

<button class="pdf-gm__toggle" aria-expanded="false" aria-controls="gm-657"> 14 more matches on pages 3, 4, 5… </button> <ul class="pdf-gm__list" id="gm-657" hidden> <!-- one <li> per matching page --> </ul> 
document.addEventListener('click', (event) => { const button = event.target.closest('.pdf-gm__toggle'); if (!button) return; const list = document.getElementById(button.getAttribute('aria-controls')); const isOpen = button.getAttribute('aria-expanded') === 'true'; button.setAttribute('aria-expanded', String(!isOpen)); list.hidden = isOpen; });

The real UI shows six rows first, then a "Show all" button for the rest. That keeps long lists from taking over the page on mobile.

Gotcha 1: "Next" always showed page 1

While testing, we clicked "Next" on a search page. The address said page 2. The results were identical to page 1.
The bug was older than the new feature, and it affected every page using our search shortcode. Anyone with more than 10 results could never see the rest.
The cause: our shortcode read the paged query var. On a static WordPress Page, /page/2/ sets page, not paged. So the shortcode always thought it was on page 1.
We switched to our own query argument, which avoids the conflict entirely:

$per_page = 10; $current = max( 1, absint( $_GET['webequipe_pdf_result_page'] ?? 1 ) ); $offset = ( $current - 1 ) * $per_page; // ... run the search with LIMIT $per_page OFFSET $offset ... echo paginate_links( array( 'base' => add_query_arg( 'webequipe_pdf_result_page', '%#%' ), 'format' => '', 'current' => $current, 'total' => $total_pages, ) );

Lesson: test around a new feature, not just the feature itself. This one bug was worth the extra testing round.

Gotcha 2: two-page spreads in a flipbook

After release, the archive customer found an edge case. Some of his magazines were scanned two pages per sheet: each PDF page is a full spread.
He displays them in DearFlip, a flipbook viewer, using its "Double Internal Page" mode. That mode keeps the cover as one page, then splits every spread into two book pages. So the viewer's page numbers no longer match the PDF's:

Our plugin was passing the PDF page number, 7, straight to the viewer. The viewer opened its page 7, which sits inside PDF page 4.
The fix is a small conversion before opening the viewer:

/** * Convert a PDF page number to a flipbook page number. * In double-internal layout the cover stays single, and every * later PDF page n becomes book pages (2n - 2) and (2n - 1). */ function wpe_flipbook_page( int $pdf_page, bool $double_internal ): int { if ( ! $double_internal || $pdf_page <= 1 ) { return $pdf_page; } return ( 2 * $pdf_page ) - 2; // left page of the spread }

The plugin detects the layout itself, so there's nothing to set up per PDF. Single-page PDFs are untouched. Check how your viewer handles the last page too, since some keep it single as well.

Bonus: counting lightbox opens in analytics

Opening a page in the flipbook lightbox never leaves the search page, so there's no redirect to count. We send a beacon instead:

function trackOpen(pdfId, page) { const data = new FormData(); data.append('action', 'wpe_track_click'); data.append('nonce', WPE.nonce); data.append('pdf_id', pdfId); data.append('page', page); navigator.sendBeacon(WPE.ajaxUrl, data); }

sendBeacon survives the page changing or the lightbox closing, which fetch doesn't guarantee.

Takeaways

• Look for loops that query. If you fetch a list and then query per item, collect the IDs and fetch once.
• Send only what you show. Trim text in SQL, not in the browser.
• Return the real total. If you cap results, say "20 of 30", not "20".
• On WordPress Pages, don't trust paged. Use your own query argument for shortcode pagination.
• Viewers have their own page numbers. Map PDF pages to the viewer's layout before linking.
The feature went from first customer email to release in 10 days.

We build WebEquipe PDF Search Pro, a WordPress plugin that makes every PDF on your site searchable, including scanned documents, with page-level results and flipbook support.

Have you hit the pagedvs pageissue in WordPress, or a similar N+1 query? I'd like to hear how you handled it.