wowprompt

SQL full-text search with ranking and typo tolerance

Builds site search on the database you already run, with full-text indexes, weighted ranking that puts title matches first, prefix matching for search-as-you-type and a fallback for common typos, before reaching for a separate search engine. Use it for a product catalogue, a help centre, a blog or an internal directory.

A full-text index definition, a ranked search query weighting title over body, a typo fallback query, and a test list of searches with expected top results
The output this prompt produced, unedited.

The prompt

Fill in these first

  • Database and version: [e.g. MySQL 8.0, or PostgreSQL 16]
  • Table and searchable columns: [e.g. products: name, brand, short_description, tags; about 40,000 rows]
  • Filters that apply with search: [e.g. category, in stock, price range]
  • Language of the content: [e.g. English with some Hindi words written in Latin letters]
  • Example searches real users type: [paste 10 to 20, including misspellings]
  • Backend language: [e.g. PHP with PDO, or Python]

Task

Design search that returns the right results first for the example searches, using only the database.

Output format

  1. Index definitions and the migration, with the reason for each column choice.
  2. The main query: weights so a match in the name ranks above a match in the description, a boost for exact phrase matches, and filters applied in the same query.
  3. Prefix matching for search-as-you-type, with a minimum of 2 or 3 characters.
  4. Typo fallback when the main query returns nothing: trigram similarity in PostgreSQL, or a small synonym and misspelling table in MySQL, filled from the example searches.
  5. Pagination of results that stays stable between pages.
  6. The backend function that runs the search with prepared statements and escapes full-text operator characters typed by users.
  7. A test table: | Search | Expected top 3 | Why |, built from the example searches.
  8. The point at which this stops being enough, such as row counts or features, and what to move to then.

Rules

  • Every query uses bound parameters; user input never enters SQL as text.
  • Explain any stop-word or minimum-word-length setting that would make a common search return nothing.
full text searchsql search rankingmysql fulltextpostgresql full text searchtypo tolerant searchsearch as you type

How to run this

Copy the prompt and paste it into Gemini, then set the aspect ratio to 3:4. No reference photo is needed for this one.

The prompt is written in Markdown with each part under its own heading, which is what the copy action hands over. Change one section at a time, keeping the rest intact, so you can tell which change did the work.

Related prompts

All productivity