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.
3
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:
- Categories (car/bike/etc.)
- Items (i.e., a thing that belongs to a category)
- 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.)