Searching through entire innodb database

[Neomech]

Hi all,

 

I'm new to MySQL, but I'm fairly comfortable with relational database structure.  I know how to use inner joins, search multiple columns, etc.

 

My problem is that I'm creating a site with a fairly large database with many tables representing many different types of products (e.g. digital cameras, refrigerators, etc.).  I am intending to use a table for each major product type rather than an Entity-Attribute-Value model, primarily because I've read so much about potential pitfalls with the EAV model.  I'm using InnoDB tables because I'm concerned about referential integrity.

 

I want to be able to have a single "search" box on the site that will allow a user to search through the entire product catalogue, and I don't know the best way to go about it.  I assume doing a huge number of joins and searching through multiple columns on multiple tables is NOT the best way to go, but I don't know what the best alternative is.  I've tried very hard to find something about this online, and am surprised I haven't been able to do so, as this would seem to be a fairly common issue (perhaps I'm using the wrong search criteria!)

 

In short, I need a way to search all of my product tables and return a correct result whether someone types "10x zoom camera" or "refrigerator with ice maker".  This sort of thing has the added difficulty in that the "cameras" table wouldn't normally have an entry of "10x zoom" but would instead have a field of Zoom_Length with an integer type.  By the same token, the "refrigerator" table would have a boolean field of Has_IceMaker.

 

I'm considering one of the following options, but don't know which is best, or if there's some other, easier method I'm just not considering:

 

1.  Add a keywords column to each table and have a trigger populate the keywords whenever an item is added/updated.  Then any search will search every table's keywords column.

2.  Add a single keywords table that somehow stores which product table and item relate to certain keywords.

3.  Use something like Sphinx or Lucene to create an index of my tables, although I'm not sure if they'll really do what I'm trying to accomplish here (particularly with the "10x zoom" problem identified above).

4.  Use something like Sphider to actually search all my product pages and create an index of those, then work backwords from the URL returned by a search query to grab the appropriate products from the database (if that makes sense).

 

Any help would be greatly appreciated.  Thanks!

[coalgames]

If you have multiple tables for different types of products, you would need to do a set of queries multiple times (once for each type of product). That may be good if you use a good cacheing system but might be slow when your website accumulates a lot of traffic.

 

If you do all the products in one table, the number of queries will be lower therefore reducing overhead. The problem with one table is that it will become very big.

 

The best solution in my opinion is to have different search pages. One for refrigerators, another for cameras and another for x product.

 

If you are using mysql and will always use mysql, you should use enum('a','b','c') each representing a product.

 

If you want your script to be portable (work on other sql applications that dont have enum), you could use CHAR(1).

[TheFilmGod]

If you want your script to be portable (work on other sql applications that dont have enum), you could use CHAR(1).

 

No. I recommend using TINYINT instead of char(1).

[coalgames]

Tiny int would probably be faster until you have more than 10 items.

 

But actually, on a second thought, tinyint would be good because you could index it faster.

[Neomech]

If you have multiple tables for different types of products, you would need to do a set of queries multiple times (once for each type of product). That may be good if you use a good cacheing system but might be slow when your website accumulates a lot of traffic.

 

Yeah, I'm concerned with the speed of this approach.  I gather if I was using MyISAM tables I could get some headway out of a FullText search, but I'm using InnoDB so that's not an option (and my understanding is that FullText starts to get too slow eventually).

 

If you do all the products in one table, the number of queries will be lower therefore reducing overhead. The problem with one table is that it will become very big.

 

I have too many distinct attributes for given product types to go with a single table, in my opinion.  There would be a TON of empty fields for each product.

 

The best solution in my opinion is to have different search pages. One for refrigerators, another for cameras and another for x product.

 

This would obviously be easier, but I need a single search that works for the whole site.  That's not something I can change at the UI level.

 

If you are using mysql and will always use mysql, you should use enum('a','b','c') each representing a product.

 

If you want your script to be portable (work on other sql applications that dont have enum), you could use CHAR(1).

 

I'm actually not sure what you're suggesting here.  Could you elaborate?