MySQL Forums
Forum List  »  Optimizer & Parser

help, optmization... 6 tables... 1 select!
Posted by: John Aust
Date: April 27, 2010 06:59AM

Hi! I'm working on an e-commerce project and need to put some flexibility to the system, the products can have attributes, categories (max 1 sublevel), images and types.

I do this, have one table for product, one for attribute, one for inventory (attribute<->product<->qty), one for images and one for types

Have this tables structures...

CREATE TABLE `product_attribute` (
`idproduct_attribute` int(11) unsigned NOT NULL AUTO_INCREMENT,
`attribute` varchar(45) NOT NULL,
PRIMARY KEY (`idproduct_attribute`)
) ENGINE=InnoDB;

CREATE TABLE `product_category` (
`idproduct_category` int(10) unsigned NOT NULL AUTO_INCREMENT,
`category` varchar(45) NOT NULL,
`category_slug` varchar(45) NOT NULL,
`category_father` int(10) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`idproduct_category`)
) ENGINE=InnoDB;

CREATE TABLE `product_image` (
`idproduct_image` int(10) unsigned NOT NULL AUTO_INCREMENT,
`idproduct` int(10) unsigned NOT NULL,
`image_file` varchar(45) NOT NULL,
`sort` tinyint(3) unsigned NOT NULL,
PRIMARY KEY (`idproduct_image`,`idproduct`),
KEY `fk_product_image_product` (`idproduct`)
) ENGINE=InnoDB;

CREATE TABLE `product_inventory` (
`idproduct_inventory` int(10) unsigned NOT NULL AUTO_INCREMENT,
`qty` int(10) unsigned NOT NULL DEFAULT '0',
`total` int(10) unsigned NOT NULL DEFAULT '0',
`idproduct` int(10) unsigned NOT NULL,
`idcampaign` int(10) unsigned NOT NULL,
`idtype` int(10) unsigned NOT NULL,
`idcategory` int(10) unsigned NOT NULL,
`idsupplier` int(10) unsigned NOT NULL,
`idattribute` int(11) NOT NULL,
PRIMARY KEY (`idproduct_inventory`,`idproduct`,`idcampaign`,`idtype`,`idcategory`,`idsupplier`,`idattribute`) USING BTREE,
KEY `fk_product_inventory_product_attribute` (`idattribute`),
KEY `fk_product_inventory_product` (`idproduct`,`idcampaign`,`idtype`,`idcategory`,`idsupplier`) USING BTREE
) ENGINE=InnoDB;

CREATE TABLE `product_type` (
`idproduct_type` int(10) unsigned NOT NULL AUTO_INCREMENT,
`type` varchar(45) NOT NULL,
`type_slug` varchar(45) NOT NULL,
PRIMARY KEY (`idproduct_type`)
) ENGINE=InnoDB;

CREATE TABLE `product` (
`idproduct` int(10) unsigned NOT NULL AUTO_INCREMENT,
`idcampaign` int(10) unsigned NOT NULL,
`idtype` int(10) unsigned NOT NULL,
`idcategory` int(10) unsigned NOT NULL,
`idsupplier` int(10) unsigned NOT NULL,
`price` decimal(8,2) NOT NULL DEFAULT '0.00',
`market_price` decimal(8,2) NOT NULL DEFAULT '0.00',
`description` text NOT NULL,
`quick_description` text NOT NULL,
`title` varchar(255) NOT NULL,
`attribute_name` varchar(45) NOT NULL,
`slug` varchar(255) NOT NULL,
`id_at_suplier` varchar(45) NOT NULL,
`sort` tinyint(3) unsigned NOT NULL,
`rand` varchar(12) NOT NULL,
PRIMARY KEY (`idproduct`,`idcampaign`,`idtype`,`idcategory`,`idsupplier`),
KEY `fk_product_campaign` (`idcampaign`),
KEY `fk_product_product_type` (`idtype`),
KEY `fk_product_product_category` (`idcategory`),
KEY `fk_product_supplier1` (`idsupplier`)
) ENGINE=InnoDB;

and, to select the product i'm using this...

SELECT `product`.*, `product_type`.`type`, `product_type`.`type_slug`, SUM(product_inventory.qty) AS `qty`, SUM(product_inventory.total) AS `total`, (SELECT `product_image`.`image_file` FROM `product_image` WHERE (product_image.idproduct = product.idproduct) ORDER BY `sort` ASC LIMIT 1) AS `image` FROM `product`
INNER JOIN `product_type` ON product_type.idproduct_type = product.idtype
INNER JOIN `product_inventory` ON product_inventory.idproduct = product.idproduct WHERE (product.idcampaign = 1) AND (product.idcategory IN(SELECT `product_category`.`idproduct_category` FROM `product_category` WHERE (product_category.idproduct_category = 1) OR (product_category.category_father = 1))) GROUP BY `product`.`idproduct` ORDER BY `type` DESC, `qty` DESC, `product`.`sort` ASC

it return the category name, type, quantity, total, first image and i can select by level of category... can anyone tell if it is crap trash or right?

sorry my poor english!

thx advice!

Options: ReplyQuote


Subject
Views
Written By
Posted
help, optmization... 6 tables... 1 select!
2597
April 27, 2010 06:59AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.