5

I have developed a web application that is used mainly for archiving all sorts of textual material (documents, references to articles, books, magazines etc.). There can be any given number of archive tables in my system, each with its own schema. The schema can be changed by a moderator through the application (imagine something similar to a really dumbed down version of phpMyAdmin).

Users can search for anything from all of the tables. By using FULLTEXT indexes together with substring searching (fields which do not support FULLTEXT indexing) the script inserts the results of a search to a single table and by ordering these results by the similarity measure I can fairly easily return the paginated results.

However, this approach has a few problems:

  • substring searching can only count exact results
  • the 50% rule applies to all tables separately and thus, mysql may not return important matches or too naively discards common words.
  • is quite expensive in terms of query numbers and execution time (not an issue right now as there's not a lot of data yet in the tables).
  • normalized data is not even searched for (I have different tables for categories, languages and file attatchments).

My planned solution Create a single table having columns similar to

id, table_id, row_id, data

Every time a new row is created/modified/deleted in any of the data tables this central table also gets updated with the data column containing a concatenation of all the fields in a row. I could then create a single index for Sphinx and use it for doing searches instead.

Are there any more efficient solutions or best practises how to approach this? Thanks.

Improve this question
3
  • That would certainly give you your needed matches, and it sounds like it eliminates your four negative bullet points. So what do you mean by "better" then?
    – Robert Harvey
    Sep 14, 2011 at 22:08
  • By "better" I mean if there is a more efficient, sort of best practice way of doing it? I wouldn't want to be to hasty with this only to find out the solution doesn't work as intended half way in. I don't have much experience in terms of natural language searching.
    – kgilden
    Sep 14, 2011 at 22:16
  • 2
    Your approach seems sound to me. You should probably limit your search to only those fields that might actually produce useful results, like "memo" fields and titles. You won't know how well it performs until you actually create some mockup data and perform some test searches, but that shouldn't be too hard to do, and once you do that and prove that it works, you're golden.
    – Robert Harvey
    Sep 14, 2011 at 22:20

1 Answer 1

Reset to default
2

Why not let the text search engine like lucene or sphinx do all the work? I use Lucene and have found it to be very good at searching multiple tables and fields for whatever you set as a target.

You don't say what your web front end is written in but both of these tools can be used with most languages.

You will be adding a lot of code to maintain in the triggers that watch for created/modified/deleted in all the tables. Wouldn't it be easier to add the option for admins to mark a column as "to be searched". Then write code to add this column to indexed fields?

I see that Sphinx has "offline index construction and incremental on-the-fly index updates" which would seem to allow you to add a field, rebuild the index offline and then bring the new index up.

Improve this answer
2
  • I am thinking about using Sphynx for this. I'll look into Lucene as well, thanks. My application is written in PHP on top of the Symfony2 framework. I also have a central Manager class that handles dynamic data persistence. I could do my data mirroring from there. Foreign fields, of course, might be a problem, because they can change independently from the dynamic data (e.g category name changes). This is the point where I think either Lucene or Sphinx comes in. If I had multiple indexes, would I still be able to search from them all with a single query?
    – kgilden
    Sep 19, 2011 at 7:40
  • 1
    Lucene has a MultiSearcher class that will search multiple local or remote indexes. One other thing Lucene has going for it is pluggable storage, which would make it possible to include index updates in transactions.
    – Blrfl
    Sep 19, 2011 at 10:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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