I have a website that allows users to search for items in various categories. Each category is a separate area (page) of my website. For example, some categories might be cars, bikes, books etc. At the moment a user has to search for an item by going to the page (for example, cars) and searching for the car they want. I would like to allow the user to search for anything on my site, from my main home page.

At the moment, each page (category) has its own set of tables, and I don't really want to turn Full Text Search on for each table (20+ of them) and search each table individually when a search is done. This is going to be slow and tedious.

What I'm thinking of doing is creating a single table that will hold all searchable information for each category of item (when an item is saved in its respective table, I would copy all searchable information over to my 'Search' table). I would then turn Full Text Search on for that table, and search that table.

Does this sound reasonable? Is there a better way? I've never used Full Text Search before, so this is new to me.

  • Is this a public facing website?
    – user69037
    Jul 1, 2013 at 0:53
  • Yes it is a public facing website
    – Hosea146
    Jul 1, 2013 at 10:46
  • Is there a reasons you can't just use a custom Google site search?
    – user69037
    Jul 1, 2013 at 22:58

2 Answers 2


Depending if you have the freedom to do so, I would suggest using something designed for searching rather than doing full-text searches in database. Databases aren't really designed for full-text searches, so the performance isn't going to be the best and can put a lot of additional load onto it.

I would suggest taking a look at Solr, ElasticSearch, or Sphinx. They will allow you to do far more customized searches than a full text database search. They all offer ways of importing data from a database and can do indexing based on the fields you provide (important if you want a brand to be indexed differently than a product name, for instance).


Well, for starters it's worth wondering why you have distinct tables for each category, instead of sharing your tables between categories. Were I to re-design your database from scratch, I'd have a three-aspect table structure:

  1. Categories (car/bike/etc.)
  2. Items (i.e., a thing that belongs to a category)
  3. Item Properties (a thing about an item that's searchable.)

You'd have indexes on Item-Properties, for both the property name and its value, and a full-text search on the item's text.

Additional tables could be added for Property-Type and "full text", or those could be built into the tables above. (That's an optimization question, after all, and premature optimization is the root of all evil.)

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.