Need huge advise re Searchable Database project

Tea_J

Hey guys, project specs are pretty simple and making it work is not that complicated.. but making it as EFFICIENT AS POSSIBLE is something else, and that's where I stand, asking other equally or more experienced developers out there for advises regarding the best way to execute this project

Site Specs:
Unix,Apache, MySQL, and PHP

The Project:
Think of this project as a library. The client has PDF documents.. the PDF documents ranges from 5-20 pages each..

He simply wants to bring all of this online, and make it searchable (basic and advance).. he wants the data to display in plain HTML (no longer in PDF)..

He wants the users to be able to search through it via:

Author, date, title, category, and keywords found IN THE BODY OF THE DOCUMENTS... (maybe more search options but not important)

Pretty simple project really, but what complicates it is that he's got over 1.5 million PDF documents!!!! lol

So i need your advises on the BEST way to do this system in terms of:

1) how to make the searching as fast as possible and not kill the server

2) Whats the best engine to use for this? MyISAM (always my favorite)? or InnoDB(never used this)?

3) How the heck will i automate conversion of PDF to plain text and insert into the database properly (date, author, body, etc).. (is this even possible?)

I'll leave this to here for now. hehe.

any advises as well as tips and warnings will be MUCH APPRECIATED!

thanks all!

Tea

Roger_Ramjet

There are lots of pdf tools out there, including those made by adobe and I'm sure that some of them could be used to batch convert all those files to a useable text format.

That said, php has a whole battery of PDF Functions that will do the job for you. Read through the user notes there for lots of usefull code and tips for using them.

Once you have extracted the contents and stored it in mysql db then you should use Full Text Search.

bretticus

Gotta hate working with PDF format ;-)

Just one note about PDFLIB (the PHP functions require it.) It is NOT free, it is used for rendering PDFs (at least we never used it to convert from PDF to other formats.) Unless you get a license, it displays a HUGE pdflib watermark accross your documents.

What you probably want is a tool called pdftohtml (it installs real easy from Debian apt-get install pdftohtml.) you could then use it to convert the pdf to HTML (probabaly similiar to google's I'd assume...dunno, never used it) or to XML which it says it supports (that could be interesting.)

If you keep it in HTML format, I came across somebody else's implementation for searching and indexing html content. I was a little skeptical in the beginning as I was ready to development my own spider (ha!), but it is quite fast as you can see from a site I implemented it in. it's downfall is that your result base becomes broader over multiple search terms instead of more narrow (full text indexing in MySQL is probably a better way to search if you can get this content into a database.) Also, it's not a true spider as you have to feed it the links you need (Xenu works well in this role.) It might be good to give you some advise on database normalization techniques and help you along your way. Anyway, it's here. Where you wanna stick all this into a database, I probably did a lot of typing for nothing. 😃

Have fun!

Sxooter

fpdf

Roger_Ramjet

Hey guys, I think you are forgetting that TJ wants to extract PDF content not generate it.

I came across a usefull tool on SourceForge that will render pdf into html or xml. Now the xml sounds interesting. If I were going to render 1.5 million pdfs into some other format I would want to future-proof that effort. i would not want to have to go through it all over again in a couple of years, and I would want my solution to work for future documents.

XML seems to be the answer.

Tea_J

lol..

thanks all for the PDF - related help.... but please. dont forget about the other question.. handling over a million entries!!! lol

Sxooter

I think you'll want to use full text indexing. Most databases have some kind of text indexing functionality. Postgresql, MySQL, Oracle, MSSQL, and DB2 all can do it in one form or another. PostgreSQL and MySQL can both do it for free. they all tend to work in a somewhat similar manner of breaking all docs up into words and indexing on a hash lookup table for those words or something like that.

Normal btree / straight hash indexing is almost useless by comparison. You'll want them in some intermediate format, like text or xml for that.

Sxooter

Note that in the past, I have just put these documents all online in a simple HTML layout and then run htdig over the top of the website for indexing. Works for smaller sites, or ones where you can keep history over time of what you've indexed. Takes the actual lookup work away from the database engine, puts it into a CGI/web layer application. Speed is quite good for that. With simple pdf2text converters it's an easy setup. And htdig, the latest version, used to do full text searching and lots of other cool things.

Roger_Ramjet

Tea_J wrote:
lol..

thanks all for the PDF - related help.... but please. dont forget about the other question.. handling over a million entries!!! lol

You'll have to benchmark different tools to find the most efficient - try a few batches of 100 through each. Then you will have a basis for choosing between tools and methods, and for estimating the time and costs this will involve.

With 1million+ docs you've got a lot of desisions to make and planning to do. Don't expect to get away lightly - even if it only took 1 sec per doc, running 24/7 that is still 115 days. You will have to spread the load over a server farm to get this done in a reasonable time. hint: lots of companies have excess capacity they can rent you on evening and weekends

With a project of this size I'd be speaking to Adobe from the start. See what they have to say, they probably have tools and methods for indexing this kind of a document archive without converting from pdf format. Might be more cost-effective just to leave them as pdfs with a database catalogue to index them.

Tea_J

I see.. thank you for all replies

As with the technology to use, i have no other choice but to use MYSQL and PHP...and everything on one server... web and database server.

the client wants all of the pdf served as regular html pages, and the whole document searchable (not just keywords column, or other indexing).. and the search time should be less than 2 seconds....

So i gues im stuck with having to painfully covert pdf to plain text and then insert to database.

So my options are really on the "methods"...

you guys really think 1.5 million documents is just too over whelming?

I did some tinkering and the methods i thought of using is to obligate the user to break up the search into different categories and not let them do a "basic" search which will make the server have to go through the whole database......

Basically, i will require the user specify the following:

Category:
Date Range: (maximum of 1 year)

I feel that will lighten the load... dont you think?

any more "clever" ideas out there? lol

Sxooter

Actually, document storage is one of MySQL's strong points, so no problem there.

I'd just put them all online as pdfs and use pdf2txt to convert them on delivery. Unless they need to be edited after conversion. Then I'd put them online as txt / html or something.

I'd still look at using ht://dig to index them. it's a very nice search engine, if you're gonna have an html interface. Otherwise, put them online as html / txt and have mysql index them with it's full text search engine and then build a simple interface to let the users search that way.

csn

Tea_J wrote:
the client wants all of the pdf served as regular html pages, and the whole document searchable (not just keywords column, or other indexing).. and the search time should be less than 2 seconds....

So i gues im stuck with having to painfully covert pdf to plain text and then insert to database.

Nah, just write a script to iterate through them, convert to text/html, and insert into the database. Then use full-text-indexing, which I think mysql has.

Tea_J wrote:
you guys really think 1.5 million documents is just too over whelming?

Nah, not number-of-records-wise, but I've never tried full-text-indexing and searching with that many records.

Tea_J

more suggestions and tips are welcome 🙂