I have a functionality which I must deal. The requirement of a project is to transform papers to pdf and store this files. The main functionality is that the user is being able to search words in the content of the files, for example it the user is looking for any book that aims on "biological risks", the software has to research on each pdf file content. So what I was thinking is that each time a user upload a new book or file, the software reads its content and store it into a table, so when the user looks for any content, will get all the books that have theese words.

But the problem is that the size of the database will be very large. Is there another way to achieve this?

  • Is there a way to index the contents of the books for key words and phrases and just store those? There's probably lists of common words that can be removed as well.
    – JeffO
    Oct 12, 2016 at 17:22
  • 1
    Sounds like you might want to use a full text search engine either with your database or instead of it. Oct 12, 2016 at 18:05
  • Thats right @DanPichelman Oct 12, 2016 at 20:55

1 Answer 1


I hope you already solved it, but in case it's usefull:

I wouldn't store the whole PDFs on a table. I'ld rather take a fixed (or variable but limited) quantity of keywords instead. This process takes two stages when the user uploads the PDF:

1: Extract the whole text as plaintext. If the PDF is in a readable format, then use any library out there for this. Eg: https://github.com/spatie/pdf-to-text

If there are just text images, like a scanned book for example, things gets more interesting. I would use Google Vision OCR API to extract text from images first.

Google Vision OCR link: https://cloud.google.com/vision/docs/ocr

2: Extract keywords from text. Definitively I would use Google's Natural Language API. It's AI powered, accepts text as an imput and returns the keywords , subjects , categories , of a text letting you know what it's about, with confidence percentage for each tag.

Google Natural Language API link: https://cloud.google.com/natural-language/

DB design: I'ld use a single pdf_contents table , with two or three columns: pdf_path ( VARCHAR your download link), keywords (a TEXT field) categories (TEXT field, if Google is able to clasify the text).

Then the query would just be :

SELECT UNIQUE path FROM pdf_contents WHERE keywords LIKE %{searchword}% OR categories LIKE %{search word}% LIMIT N;

EDIT: forgot to put the link to a pdf to text example library on PHP

  • It's a nice piece of advice, except for the DB schema; keyword search by LIKE %word% will have poor performance on documents of significant size, and cannot use an index. A full-text search index would fare much better.
    – 9000
    Jun 1, 2018 at 18:05
  • Good point. Absolutely agree , so you'll probably need to use only one text column , tokens , with FULLTEXT. And there you put a prudent qty of tokens extracted with Google. Aparently it's not very efficient to preform seaches filtering with other index / columns. hackernoon.com/… Maybe , if you want to add further filtering , you could filter it yourself on the backend. I didn't test the article's query though. Jun 1, 2018 at 22:49
  • How would you use fulltext search with multiple words ? I mean, if the user types "LEMON ROCKETS" then that the DB returns all rows containign "LEMON" and "ROCKETS", rather than only those containing the exact "LEMON ROCKETS" string Jun 1, 2018 at 23:44
  • Have you ever read the docs on how full text search works? In short: "lemon rockets" (quoted) is usually accepted as a query for consecutive words.
    – 9000
    Jun 2, 2018 at 17:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.