Hi,
first of all Hi to all members. i have a shopping portal in which we data coming from different merchant on web of there products in CSV format. the CSV of the product come in standard Structure from all the merchant. we get these CSV at same time schedule everday. so the product table is formed everyday. we have another table called category , in which we have categoery and have aditional field to which store the filter value to fetch the product from the products table. like camera is a caegoery name and the filter will contain the keyword to fetch product from product table , as product table contain all type of product.
the product table can have product from 20 M to 200 M product. so if use full text search , will having the performance issue. my current database structure is as follows :
Categoery Table
1. field name = id datatype = int(10) Hold Id Primary Key
2. field name = name datatype = varchar(200) hold categoery name
3. field name = small_desc datatype = text hold small description
4. field name = catlinkname datatype = varchar(300) Hold htaccess page name
5. field name = parentid datatype = int(11) hold zero(0) if parent , other catgoery id of parnet to whome it belong.
6. field name = requirekeyword datatype = varchar(500) Will be necessary keyword should present in product table to fetch product from product table.
7. field name = notrequire datatype = varchar(500) hold the keyword , this is used to omit the product if that contain those keyword.
8. field name = canberequire datatype = varchar(500) hold keyword optional means while filtering product from products table this keyword may be or may not be there.
9. field name = price_from datatype = float hold the min price to show , default is 0
10. field name = price_to datatype = float hold the max price to show max like product range up to 100 USD.
we can modify Category table if needed, this is designed by us.
but the product table is the same structure as we getting from different merchant.
the product table is to be created everyday once at particular time. as we received everyday feeds
my products table current structure is as follow :
1 Product ID Number Unique ID that is used to identify a product. It must be an integer
greater than
2 and less than 4 billion. All future references to a given product use this ID.
2 Product Name VarChar2(255) Product name.
3 SKU Number VarChar2(40) SKU Number.
4 Primary Category VarChar2(50) Primary product category.
5 Secondary Category(ies)VarChar2(2000) Secondary product categories, delimited with
double tildes (~~).
6 Product URL VarChar2(2000) URL of the product page.
7 Product Image URL VarChar2(2000) URL of product image. This will be an absolute URL.
8 Buy URL VarChar2(2000) URL of shopping cart with product.
9 Short Product Description VarChar2(500) Short description of product.
10 Long Product Description VarChar2(2000) Long description of product.
11 Discount Number Relies on discount type (below) to determine how to apply.If Discount
Type is amount, then discount is deducted. If it is percentage, then
percentage is deducted.
12 Discount Type VarChar2(255) Values: amount or percentage.
13 Sale Price Number This price reflects any discounts.
14 Retail Price Number This price does not reflect any discounts.
15 Begin Date Date(mm/dd/yyyy) Date that the product becomes available.
16 End Date Date(mm/dd/yyyy) Date that the product ceases to be available.
17 Brand VarChar2(255) Brand name.
18 Shipping Number The cost of the default shipping option available.
19 Keyword(s) VarChar2(500) Keywords for searches, delimited with double tildes (~~).
20 Manufacturer Part # VarChar2(50) Manufacturer’s part number (may sometimes be the same
as SKU).
21 Manufacturer Name VarChar2(250) Manufacturer’s name.
22 Shipping Information VarChar2(50) Text-based shipping information – provides details
on the default shipping option.
23 Availability VarChar2(50) Denotes whether the product is in stock.
24 Universal Pricing Code VarChar2(15) Universal Pricing Code.
25 Class ID Number Classification ID based on product type (see class definition table).
26 Currency VarChar2(3) The three character ISO Currency Code. Default is USD.
27 M1 VarChar2(2000) Blank field unless other arrangements have been made.
28 Pixel VarChar2(128) 1x1 pixel <img> tag used to track impression data for the link.
This is a full <img> tag with all appropriate attributes. See Appendix C for a sample
record.
29 Miscellaneous Attribute VarChar2(128)
30 Attribute 2 VarChar2(128)
31 Attribute 3 VarChar2(128)
32 Attribute 4 VarChar2(128)
33 Attribute 5 VarChar2(128)
34 Attribute 6 VarChar2(128)
35 Attribute 7 VarChar2(128)
36 Attribute 8 VarChar2(128)
37 Attribute 9 VarChar2(128)
38 Attribute 10 VarChar2(128)
attribute are depend upon the type of the product and class id will define that like book will contain what in attribute number and camera will contain .
we have same feed same feed structure as table , we insert product using LOAD File command of mysql.
We have to run differnt kind of search for user like
Search by Product id.
search by sku number.
find common sku number to compare them.
search by keyword like camera and product should come to relevance like if i search rose , rose should come not rose shirt or rose perfume.
if i use mysql full text search , it will be taken huge time in searching product and making indexing. Kindly suggest in the best data structure and searching technique . i have read sphinxsearch and many other technique , sphinxsearch is typical for me.
I need help please help me out.
Thanks in advance.
Naveen