Creating a Search application for a website - What you think about my concept

[eluser]tochie[/eluser]
Hello Folks,

I’d like your ideas on a concept I’m working on for a project. I’m developing a CI based site that’ll sell entertainment products i.e audio, video, comedy, ringtones etc. I want to add a comprehensive search application and I’m a bit confused on what method to use.

This is it

Note: Each product type has its own db table and each record will have a ‘tags’ column to store words that describes the item in that row(for easy searching)

A. Will the search be faster if I create a special table for search with only two columns: ‘item id’ and ‘tags’ where all the products sold on the website will be. This is where i’ll run searches and then with the item_id call up more information from the specific products table.

OR

B. Do I just use the tags in the tables created for each product to run searches.

Please If u suggest option A, how do i work-around the limits in number of rows a mysql table can have.

Thanks in advance!

[eluser]danmontgomery[/eluser]
[quote author="tochie" date="1268179645"]Please If u suggest option A, how do i work-around the limits in number of rows a mysql table can have.[/quote]

If you really need tables with more than 2^32 rows (4,294,967,296), you can compile mysql with --with-big-tables, to up that limit to (2^32)^2 (1.84467441 × 10^19)... I'm skeptical that either of these scenarios will even be close to entering the realm of possibility.

My suggestion is to not decide on a database architecture halfway through the thought process of what you want that database to do. As for a response to your question, you might consider some light reading:

http://www.pui.ch/phred/archives/2005/04...hemas.html
http://www.pui.ch/phred/archives/2005/06...tests.html
http://iamcal.com/publish/articles/php/search/
http://www.niallkennedy.com/blog/uploads/flickr_php.pdf