A Simple Web Search Engine

Understanding the Needle‑and‑Haystack Model for Web Search

Every visitor who lands on a site expects to find the content they’re looking for with just a few keystrokes. That expectation is driven by the search engine experience that we have grown to rely on. But behind the sleek interface of Google or Bing lies a series of logical steps that transform a word or phrase into a list of URLs, titles, and summaries. In this guide we’ll peel back that curtain and show how a very small, well‑structured database can mimic the essential part of that process.

The heart of a search engine can be boiled down to two questions: does a particular word exist in a set of documents? and which documents contain that word? If we look at the first question, the answer is a simple true‑false. The second question is a lookup: it tells us which page or file the word belongs to. In programming lingo, the words are the keys and the documents are the values. Think of it like a phone book where you type a name and receive a number. A search engine is the same idea, but the “phone book” is usually huge, and the “name” is any word or combination of words a user might type.

When you build a system that can answer these questions fast enough for real‑time interaction, you’re basically writing a search engine. You can achieve this with a simple relational database such as MySQL, SQLite, or PostgreSQL. The key is to design two tables that play well together: one to hold the actual web pages and another to hold the words that appear in those pages. The two tables will be joined on a unique identifier that ties each word to the page it came from.

Why not cram everything into a single table? That would seem simpler at first glance. In practice, combining pages and words into one large table quickly becomes unwieldy. The number of rows would explode because each word in a page would create a separate row, making every page appear many times over. Query performance would suffer, and inserting new pages would become a nightmare. Splitting the data into a page_data table and a keywords_list table keeps the database tidy, improves indexing, and speeds up lookups. It also follows the normalization rules that most database designers swear by.

Below we will walk through the two‑table approach in detail, but first let’s look at the exact schema we’ll use. We’ll write the CREATE TABLE statements in MySQL syntax so you can copy and paste them directly into your own database. If you’re using a different SQL engine, the syntax will be almost identical; just adjust the data types if necessary.

The page_data table will hold the core information for every page you want to index: the URL, a human‑readable title, a short summary, and a primary key that uniquely identifies each row. We’ll call that key page_id. The primary key is critical because it lets us link the page to its words in the second table. The keywords_list table will store every keyword that appears in any page, along with the page_id of the page it belongs to. It will also store a simple score column that counts how many times a word appears on a page; this is handy for relevance ranking later on.

In the next section we’ll dive into the exact table definitions and explain how they relate to each other. With the tables in place, the rest of the search engine comes down to a single, well‑crafted SQL query and a small PHP helper function to tie everything together.

Designing a Two‑Table Schema that Powers Quick Look‑ups

We now move from theory to implementation. The first table we create is page_data, which holds one record per page you want to expose in search results. The schema looks like this:

Each field serves a clear purpose. page_id is an integer that automatically increments with each new page, guaranteeing uniqueness. url stores the full address, up to 2083 characters, the maximum length allowed by most browsers. title holds the page’s heading, and summary is a short excerpt you can display under the title in search results.

The second table, keywords_list, ties individual words to the pages that contain them. Its definition is slightly more involved because it has to capture both the word itself and the page it belongs to:

The keyword column stores a single word, typically in lowercase to keep comparisons consistent. page_id is a foreign key that points back to page_data, ensuring referential integrity. score records how many times the keyword appears on that page; for simple use‑cases you can set it to 1, but a higher number gives you more granular relevance data later.

When you insert a new page, you first add a record to page_data. That operation gives you a page_id. Then you parse the page’s content, split it into individual words, and insert a row into keywords_list for each unique word, attaching the same page_id. The FOREIGN KEY constraint guarantees that every keyword must belong to a real page, preventing orphan rows.

Why this split? The benefit is two‑fold. First, it reduces duplication. A single page that contains 500 words results in 500 rows in keywords_list but only one row in page_data. Second, it makes searching efficient. To find all pages that contain a given word, you simply look up the word in keywords_list and fetch the page_id values. Then you join those IDs to page_data to retrieve the URLs, titles, and summaries.

Indexing is a key performance lever. Add a composite index on (keyword, page_id) in keywords_list so that lookups by keyword are fast and the database can quickly retrieve the relevant page IDs. On the page_data side, the primary key index on page_id is already in place. With these indexes, the join that follows will happen in a matter of milliseconds even for tens of thousands of pages.

In practice, you’ll populate these tables with a script that crawls your site, extracts text, normalizes words to lowercase, removes punctuation, and skips stop‑words like “the” or “and.” The result is a clean index that reflects the content of your site.

Now that the tables are ready, the heavy lifting comes down to crafting an SQL query that can retrieve the best matches for a user’s search phrase. That query will make heavy use of regular expressions, grouping, and scoring to surface the most relevant pages first. The next section walks through that query and explains how to build it dynamically in PHP.

From SQL Statements to PHP Code: Turning Queries into User‑Facing Results

The core of any search engine is a query that takes a list of keywords and returns a set of pages that contain those words. The approach we’ll use is simple yet effective: use a regular expression to match any of the user’s terms, join the two tables, count how many keywords matched per page, and order by that count to surface the most relevant results first.

First, let’s write the SQL in plain English:

1. Look at keywords_list and find rows where the keyword column matches any of the search terms.
2. For each matched keyword, collect the associated page_id
3. Group the results by page_id and count how many distinct keywords matched for that page. This count is the relevance score.
4. Join the grouped results back to page_data to pull the URL, title, and summary.
5. Order the final set by the score in descending order so that pages with more keyword matches appear first.

   Translating that into SQL, we get something like this:

   Prompt

   SELECT</p> <p> pd.url,</p> <p> pd.title,</p> <p> pd.summary,</p> <p> COUNT(*) AS score</p> <p>FROM</p> <p> keywords_list kl</p> <p>JOIN</p> <p> page_data pd ON kl.page_id = pd.page_id</p> <p>WHERE</p> <p> kl.keyword REGEXP ?</p> <p>GROUP BY</p> <p> pd.page_id</p> <p>ORDER BY</p> <p> score DESC;</p>

   The REGEXP placeholder will be replaced with a regular expression that captures all of the user’s search terms. In MySQL, REGEXP performs a case‑insensitive pattern match, which is perfect for a quick search. If you need stricter control or support for advanced regex features, consider PostgreSQL’s ~* operator.

   Generating the regular expression is straightforward. Suppose the user enters “php tutorial beginner.” We split that string into an array of words, escape any regex meta‑characters, wrap each word in parentheses, and then join them with the pipe symbol (|) to form an alternation pattern. Finally, we enclose the entire expression in square brackets to indicate a single character set:

   Prompt

   $terms = preg_split('/\s+/', strtolower($query));</p> <p>$escaped = array_map('preg_quote', $terms);</p> <p>$pattern = '[' . implode('|', $escaped) . ']';</p>

   When $pattern is bound to the prepared statement, the database will search for any keyword that matches one of the user’s terms. The use of a prepared statement protects against SQL injection and lets the engine cache the query plan for repeated use.

   Moving on to the PHP layer, we write a search() function that takes two arguments: a database configuration array and a string of search terms. The function follows these steps:

   1. Extract connection parameters from the configuration array.
   2. Open a MySQLi connection and select the database.
   3. Prepare the SQL statement shown above.
   4. Build the regular expression from the user’s query.
   5. Bind the regex to the statement and execute.
   6. Fetch all rows into an array of associative arrays.
   7. Return the array to the caller.

      Here is a trimmed‑down version of the function. It omits error handling for brevity, but you should add checks for failed connections and query execution in a real application.

      Prompt

      function search(array $db, string $query): array {</p> <p> $mysqli = new mysqli(</p> <p> $db['host'],</p> <p> $db['user'],</p> <p> $db['pass'],</p> <p> $db['database']</p> <p> );</p> <p> $sql = "</p> <p> SELECT pd.url, pd.title, pd.summary, COUNT(*) AS score</p> <p> FROM keywords_list kl</p> <p> JOIN page_data pd ON kl.page_id = pd.page_id</p> <p> WHERE kl.keyword REGEXP ?</p> <p> GROUP BY pd.page_id</p> <p> ORDER BY score DESC</p> <p> ";</p> <p> $stmt = $mysqli->prepare($sql);</p> <p> // Build regex from user query</p> <p> $terms = preg_split('/\\s+/', strtolower($query));</p> <p> $escaped = array_map('preg_quote', $terms);</p> <p> $regex = '[' . implode('|', $escaped) . ']';</p> <p> $stmt->bind_param('s', $regex);</p> <p> $stmt->execute();</p> <p> $result = $stmt->get_result();</p> <p> $matches = [];</p> <p> while ($row = $result->fetch_assoc()) {</p> <p> $matches[] = $row;</p> <p> }</p> <p> return $matches;</p> <p>}</p>

      After calling search(), the caller receives an array of pages. Each element contains the URL, title, summary, and a score indicating how many search terms matched. If the array is empty, no pages matched the query. You can use this array to build a simple results page that lists links and excerpts, or pass it to a template engine for a richer UI.

      Performance is the real test. With indexes on keyword and page_id, the REGEXP search will touch only the rows that match the pattern, and the grouping operation is executed in memory. For a site with a few thousand pages, the query runs in well under 200 milliseconds on a modest server. For larger sites, you might add full‑text indexing or switch to a dedicated search engine like Elasticsearch, but for many small or medium projects this approach is more than sufficient.

      In summary, by splitting the data into two logical tables, using a regular expression to match user terms, and ordering results by match count, you create a lightweight yet functional search engine in just a handful of lines of SQL and PHP. The same principles can be extended to full‑text search, fuzzy matching, or even ranked relevance algorithms if you want to grow your system over time.