Help with optimization and search.

BebeBlueEyez

I can't send URL's cause it's a private network.

Ok...
it's this.

Users will upload documents in various formats. (word, wordperfect, pdf, etc) using PHP into a mySQL database.

Some of these will be extremely large and for some reason to optimize the database (i'm still new and didn't do this part nor totally understand why only that it's faster and more effective) the huge ones are being broken into binary chunks and stored (BLO😎 in different rows of the database.

So Along comes me.

And My task is to build a search, with php and mySQL allowing users to search text in the documents. For instance,a word doc has "Running snail" In it.

The user types in "Running snail" in the search, and will get the result according to those two words being found in that document stored in the database.

This is the problem.

Say the document was one of the huge ones. And Running was in the first chunk, snail in the second.

It would give that result, but it would also give anything with "Running" and anything with "snail" in it.

Therefore the results would be more of an OR query rather then an AND query... (this part isn't making sense in my explanation).

So...

what's the best possible way to get around the chunk issue, while still allowing users to search for text in a stored document (word or wordperfect) and not taking too long?

AstroTeg

I'm thinking you're going to need to index your documents. Then when its time to perform your search(es), you just refer to the index. You can use the database to store the index, but I'd leave the binary files out on the filesystem. Have your index point to the various file names.

Trick is building the index. I have theories on how to do this, but have never tried, so I'm not much help on this aspect...

mtimdog

I've built an index before (with an array for ordinary words to throw out like a, the, etc)...

If you're interested...let me know, but I warn you it'll take some time and it may not be too optimized, but will only need run when you upload a doc.

BebeBlueEyez

I'm not positive, but i think the ID number in my table is already being used as an index.

It's more figuring out the search i'm stuck on.

AstroTeg

Originally posted by mtimdog
I've built an index before (with an array for ordinary words to throw out like a, the, etc)...

I'm not asking you to look (since I could, but I'm being lazy), but you think there's a list out on the 'net with a pre-built list of ordinary words? Or how did you build yours?

AstroTeg

Originally posted by BebeBlueEyez
I'm not positive, but i think the ID number in my table is already being used as an index.

It's more figuring out the search i'm stuck on.

You're indexing off a numeric ID. How does that help your search? I don't think it will.

An index can be of anything. You could have an index of numbers. You could have an index of fruit. If you were to have an index of fruit, how would you set it up? Well, in some cases, you may want it to be alphabetical, so apples would come before oranges. When it comes time to search for grapes, you know if you searched past oranges that your search is over because grapes would have come before oranges. By doing this, you optimize your query.

What you need to do is index the words in your documents. So when a user searches for a keyword, you will search an index and pull up all the references to that keyword.

BebeBlueEyez

oh wow.

That would be a hell of a lot of words.

Some docs will be 300 pages of text.

And how would SQL index thewords in said documents on document upload?

I just found something on FULLTEXT search, i'm trying to figure out how it works too.

And i'm thinking on what you said.

nemonoman

If you are using mysql, look here. Read the user notes especially...some good ideas and cautions:

http://www.mysql.com/doc/en/Fulltext_Search.html

mtimdog

AstroTeg: I just built an array of meaningless words...only about 50, I'm sure I could have made it larger, but that would make the process even longer...now onto the process

replace punctuation (. ! ?) by a space
explode on space
build an array of words (only add if key doesn't exist in array of meaningless words....to do this fast without duplicates, just set array[$value]=1;....then loop through the array assigning $key to-->). implode the array into delimited list to insert into database with that article(since you're breaking the text up, attach this to the parent table if there is one).