Re: Mysql sub query optimization
Posted by:
JH NG
Date: July 04, 2014 01:01AM
CREATE TABLE `oc_category` (
`category_id` int(11) NOT NULL AUTO_INCREMENT,
`image` varchar(255) DEFAULT NULL,
`parent_id` int(11) NOT NULL DEFAULT '0',
`top` tinyint(1) NOT NULL,
`column` int(3) NOT NULL,
`sort_order` int(3) NOT NULL DEFAULT '0',
`status` tinyint(1) NOT NULL,
`date_added` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`date_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`category_id`)
) ENGINE=MyISAM AUTO_INCREMENT=143 DEFAULT CHARSET=utf8
CREATE TABLE `oc_category_description` (
`category_id` int(11) NOT NULL,
`language_id` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
`description` text NOT NULL,
`meta_description` varchar(255) NOT NULL,
`meta_keyword` varchar(255) NOT NULL,
PRIMARY KEY (`category_id`,`language_id`),
KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
CREATE TABLE `oc_category_to_store` (
`category_id` int(11) NOT NULL,
`store_id` int(11) NOT NULL,
PRIMARY KEY (`category_id`,`store_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
CREATE TABLE `oc_product_to_category` (
`product_id` int(11) NOT NULL,
`category_id` int(11) NOT NULL,
PRIMARY KEY (`product_id`,`category_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
CREATE TABLE `oc_product` (
`product_id` int(11) NOT NULL AUTO_INCREMENT,
`model` varchar(64) NOT NULL,
`sku` varchar(64) NOT NULL,
`upc` varchar(12) NOT NULL,
`ean` varchar(14) NOT NULL,
`jan` varchar(13) NOT NULL,
`isbn` varchar(13) NOT NULL,
`mpn` varchar(64) NOT NULL,
`location` varchar(128) NOT NULL,
`quantity` int(4) NOT NULL DEFAULT '0',
`stock_status_id` int(11) NOT NULL,
`image` varchar(255) DEFAULT NULL,
`manufacturer_id` int(11) NOT NULL,
`shipping` tinyint(1) NOT NULL DEFAULT '1',
`price` decimal(15,4) NOT NULL DEFAULT '0.0000',
`points` int(8) NOT NULL DEFAULT '0',
`tax_class_id` int(11) NOT NULL,
`date_available` date NOT NULL,
`weight` decimal(15,8) NOT NULL DEFAULT '0.00000000',
`weight_class_id` int(11) NOT NULL DEFAULT '0',
`length` decimal(15,8) NOT NULL DEFAULT '0.00000000',
`width` decimal(15,8) NOT NULL DEFAULT '0.00000000',
`height` decimal(15,8) NOT NULL DEFAULT '0.00000000',
`length_class_id` int(11) NOT NULL DEFAULT '0',
`subtract` tinyint(1) NOT NULL DEFAULT '1',
`minimum` int(11) NOT NULL DEFAULT '1',
`sort_order` int(11) NOT NULL DEFAULT '0',
`status` tinyint(1) NOT NULL DEFAULT '0',
`date_added` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`date_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`viewed` int(5) NOT NULL DEFAULT '0',
PRIMARY KEY (`product_id`)
) ENGINE=MyISAM AUTO_INCREMENT=1417 DEFAULT CHARSET=utf8