MySQL Forums
Forum List  »  Performance

Re: query performance optimization
Posted by: Devrishi Shandilya
Date: January 22, 2016 06:18AM

Hi,


I have done all the things which suggested, but nothing performance boost, I am attaching all the related tables and indexes, please go through it and suggest.


CREATE TABLE `sales_flat_order` (
`entity_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Entity Id',
`state` varchar(32) DEFAULT NULL COMMENT 'State',
`status` varchar(32) DEFAULT NULL COMMENT 'Status',
`coupon_code` varchar(255) DEFAULT NULL COMMENT 'Coupon Code',
`protect_code` varchar(255) DEFAULT NULL COMMENT 'Protect Code',
`shipping_description` varchar(255) DEFAULT NULL COMMENT 'Shipping Description',
`is_virtual` smallint(5) unsigned DEFAULT NULL COMMENT 'Is Virtual',
`store_id` smallint(5) unsigned DEFAULT NULL COMMENT 'Store Id',
`customer_id` int(10) unsigned DEFAULT NULL COMMENT 'Customer Id',
`base_discount_amount` decimal(12,4) DEFAULT NULL COMMENT 'Base Discount Amount',
`base_discount_canceled` decimal(12,4) DEFAULT NULL COMMENT 'Base Discount Canceled',
`base_discount_invoiced` decimal(12,4) DEFAULT NULL COMMENT 'Base Discount Invoiced',
`base_discount_refunded` decimal(12,4) DEFAULT NULL COMMENT 'Base Discount Refunded',
`base_grand_total` decimal(12,4) DEFAULT NULL COMMENT 'Base Grand Total',
`base_shipping_amount` decimal(12,4) DEFAULT NULL COMMENT 'Base Shipping Amount',
`base_shipping_canceled` decimal(12,4) DEFAULT NULL COMMENT 'Base Shipping Canceled',
`base_shipping_invoiced` decimal(12,4) DEFAULT NULL COMMENT 'Base Shipping Invoiced',
`base_shipping_refunded` decimal(12,4) DEFAULT NULL COMMENT 'Base Shipping Refunded',
`base_shipping_tax_amount` decimal(12,4) DEFAULT NULL COMMENT 'Base Shipping Tax Amount',
`base_shipping_tax_refunded` decimal(12,4) DEFAULT NULL COMMENT 'Base Shipping Tax Refunded',
`base_subtotal` decimal(12,4) DEFAULT NULL COMMENT 'Base Subtotal',
`base_subtotal_canceled` decimal(12,4) DEFAULT NULL COMMENT 'Base Subtotal Canceled',
`base_subtotal_invoiced` decimal(12,4) DEFAULT NULL COMMENT 'Base Subtotal Invoiced',
`base_subtotal_refunded` decimal(12,4) DEFAULT NULL COMMENT 'Base Subtotal Refunded',
`base_tax_amount` decimal(12,4) DEFAULT NULL COMMENT 'Base Tax Amount',
`base_tax_canceled` decimal(12,4) DEFAULT NULL COMMENT 'Base Tax Canceled',
`base_tax_invoiced` decimal(12,4) DEFAULT NULL COMMENT 'Base Tax Invoiced',
`base_tax_refunded` decimal(12,4) DEFAULT NULL COMMENT 'Base Tax Refunded',
`base_to_global_rate` decimal(12,4) DEFAULT NULL COMMENT 'Base To Global Rate',
`base_to_order_rate` decimal(12,4) DEFAULT NULL COMMENT 'Base To Order Rate',
`base_total_canceled` decimal(12,4) DEFAULT NULL COMMENT 'Base Total Canceled',
`base_total_invoiced` decimal(12,4) DEFAULT NULL COMMENT 'Base Total Invoiced',
`base_total_invoiced_cost` decimal(12,4) DEFAULT NULL COMMENT 'Base Total Invoiced Cost',
`base_total_offline_refunded` decimal(12,4) DEFAULT NULL COMMENT 'Base Total Offline Refunded',
`base_total_online_refunded` decimal(12,4) DEFAULT NULL COMMENT 'Base Total Online Refunded',
`base_total_paid` decimal(12,4) DEFAULT NULL COMMENT 'Base Total Paid',
`base_total_qty_ordered` decimal(12,4) DEFAULT NULL COMMENT 'Base Total Qty Ordered',
`base_total_refunded` decimal(12,4) DEFAULT NULL COMMENT 'Base Total Refunded',
`discount_amount` decimal(12,4) DEFAULT NULL COMMENT 'Discount Amount',
`discount_canceled` decimal(12,4) DEFAULT NULL COMMENT 'Discount Canceled',
`discount_invoiced` decimal(12,4) DEFAULT NULL COMMENT 'Discount Invoiced',
`discount_refunded` decimal(12,4) DEFAULT NULL COMMENT 'Discount Refunded',
`grand_total` decimal(12,4) DEFAULT NULL COMMENT 'Grand Total',
`shipping_amount` decimal(12,4) DEFAULT NULL COMMENT 'Shipping Amount',
`shipping_canceled` decimal(12,4) DEFAULT NULL COMMENT 'Shipping Canceled',
`shipping_invoiced` decimal(12,4) DEFAULT NULL COMMENT 'Shipping Invoiced',
`shipping_refunded` decimal(12,4) DEFAULT NULL COMMENT 'Shipping Refunded',
`shipping_tax_amount` decimal(12,4) DEFAULT NULL COMMENT 'Shipping Tax Amount',
`shipping_tax_refunded` decimal(12,4) DEFAULT NULL COMMENT 'Shipping Tax Refunded',
`store_to_base_rate` decimal(12,4) DEFAULT NULL COMMENT 'Store To Base Rate',
`store_to_order_rate` decimal(12,4) DEFAULT NULL COMMENT 'Store To Order Rate',
`subtotal` decimal(12,4) DEFAULT NULL COMMENT 'Subtotal',
`subtotal_canceled` decimal(12,4) DEFAULT NULL COMMENT 'Subtotal Canceled',
`subtotal_invoiced` decimal(12,4) DEFAULT NULL COMMENT 'Subtotal Invoiced',
`subtotal_refunded` decimal(12,4) DEFAULT NULL COMMENT 'Subtotal Refunded',
`tax_amount` decimal(12,4) DEFAULT NULL COMMENT 'Tax Amount',
`tax_canceled` decimal(12,4) DEFAULT NULL COMMENT 'Tax Canceled',
`tax_invoiced` decimal(12,4) DEFAULT NULL COMMENT 'Tax Invoiced',
`tax_refunded` decimal(12,4) DEFAULT NULL COMMENT 'Tax Refunded',
`total_canceled` decimal(12,4) DEFAULT NULL COMMENT 'Total Canceled',
`total_invoiced` decimal(12,4) DEFAULT NULL COMMENT 'Total Invoiced',
`total_offline_refunded` decimal(12,4) DEFAULT NULL COMMENT 'Total Offline Refunded',
`total_online_refunded` decimal(12,4) DEFAULT NULL COMMENT 'Total Online Refunded',
`total_paid` decimal(12,4) DEFAULT NULL COMMENT 'Total Paid',
`total_qty_ordered` decimal(12,4) DEFAULT NULL COMMENT 'Total Qty Ordered',
`total_refunded` decimal(12,4) DEFAULT NULL COMMENT 'Total Refunded',
`can_ship_partially` smallint(5) unsigned DEFAULT NULL COMMENT 'Can Ship Partially',
`can_ship_partially_item` smallint(5) unsigned DEFAULT NULL COMMENT 'Can Ship Partially Item',
`customer_is_guest` smallint(5) unsigned DEFAULT NULL COMMENT 'Customer Is Guest',
`customer_note_notify` smallint(5) unsigned DEFAULT NULL COMMENT 'Customer Note Notify',
`billing_address_id` int(11) DEFAULT NULL COMMENT 'Billing Address Id',
`customer_group_id` smallint(6) DEFAULT NULL COMMENT 'Customer Group Id',
`edit_increment` int(11) DEFAULT NULL COMMENT 'Edit Increment',
`email_sent` smallint(5) unsigned DEFAULT NULL COMMENT 'Email Sent',
`forced_shipment_with_invoice` smallint(5) unsigned DEFAULT NULL COMMENT 'Forced Do Shipment With Invoice',
`payment_auth_expiration` int(11) DEFAULT NULL COMMENT 'Payment Authorization Expiration',
`quote_address_id` int(11) DEFAULT NULL COMMENT 'Quote Address Id',
`quote_id` int(11) DEFAULT NULL COMMENT 'Quote Id',
`shipping_address_id` int(11) DEFAULT NULL COMMENT 'Shipping Address Id',
`adjustment_negative` decimal(12,4) DEFAULT NULL COMMENT 'Adjustment Negative',
`adjustment_positive` decimal(12,4) DEFAULT NULL COMMENT 'Adjustment Positive',
`base_adjustment_negative` decimal(12,4) DEFAULT NULL COMMENT 'Base Adjustment Negative',
`base_adjustment_positive` decimal(12,4) DEFAULT NULL COMMENT 'Base Adjustment Positive',
`base_shipping_discount_amount` decimal(12,4) DEFAULT NULL COMMENT 'Base Shipping Discount Amount',
`base_subtotal_incl_tax` decimal(12,4) DEFAULT NULL COMMENT 'Base Subtotal Incl Tax',
`base_total_due` decimal(12,4) DEFAULT NULL COMMENT 'Base Total Due',
`payment_authorization_amount` decimal(12,4) DEFAULT NULL COMMENT 'Payment Authorization Amount',
`shipping_discount_amount` decimal(12,4) DEFAULT NULL COMMENT 'Shipping Discount Amount',
`subtotal_incl_tax` decimal(12,4) DEFAULT NULL COMMENT 'Subtotal Incl Tax',
`total_due` decimal(12,4) DEFAULT NULL COMMENT 'Total Due',
`weight` decimal(12,4) DEFAULT NULL COMMENT 'Weight',
`customer_dob` datetime DEFAULT NULL COMMENT 'Customer Dob',
`increment_id` varchar(50) DEFAULT NULL COMMENT 'Increment Id',
`applied_rule_ids` varchar(255) DEFAULT NULL COMMENT 'Applied Rule Ids',
`base_currency_code` varchar(3) DEFAULT NULL COMMENT 'Base Currency Code',
`customer_email` varchar(255) DEFAULT NULL COMMENT 'Customer Email',
`customer_firstname` varchar(255) DEFAULT NULL COMMENT 'Customer Firstname',
`customer_lastname` varchar(255) DEFAULT NULL COMMENT 'Customer Lastname',
`customer_middlename` varchar(255) DEFAULT NULL COMMENT 'Customer Middlename',
`customer_prefix` varchar(255) DEFAULT NULL COMMENT 'Customer Prefix',
`customer_suffix` varchar(255) DEFAULT NULL COMMENT 'Customer Suffix',
`customer_taxvat` varchar(255) DEFAULT NULL COMMENT 'Customer Taxvat',
`discount_description` varchar(255) DEFAULT NULL COMMENT 'Discount Description',
`ext_customer_id` varchar(255) DEFAULT NULL COMMENT 'Ext Customer Id',
`ext_order_id` varchar(255) DEFAULT NULL COMMENT 'Ext Order Id',
`global_currency_code` varchar(3) DEFAULT NULL COMMENT 'Global Currency Code',
`hold_before_state` varchar(255) DEFAULT NULL COMMENT 'Hold Before State',
`hold_before_status` varchar(255) DEFAULT NULL COMMENT 'Hold Before Status',
`order_currency_code` varchar(255) DEFAULT NULL COMMENT 'Order Currency Code',
`original_increment_id` varchar(50) DEFAULT NULL COMMENT 'Original Increment Id',
`relation_child_id` varchar(32) DEFAULT NULL COMMENT 'Relation Child Id',
`relation_child_real_id` varchar(32) DEFAULT NULL COMMENT 'Relation Child Real Id',
`relation_parent_id` varchar(32) DEFAULT NULL COMMENT 'Relation Parent Id',
`relation_parent_real_id` varchar(32) DEFAULT NULL COMMENT 'Relation Parent Real Id',
`remote_ip` varchar(255) DEFAULT NULL COMMENT 'Remote Ip',
`shipping_method` varchar(255) DEFAULT NULL COMMENT 'Shipping Method',
`store_currency_code` varchar(3) DEFAULT NULL COMMENT 'Store Currency Code',
`store_name` varchar(255) DEFAULT NULL COMMENT 'Store Name',
`x_forwarded_for` varchar(255) DEFAULT NULL COMMENT 'X Forwarded For',
`customer_note` text COMMENT 'Customer Note',
`created_at` timestamp NULL DEFAULT NULL COMMENT 'Created At',
`updated_at` timestamp NULL DEFAULT NULL COMMENT 'Updated At',
`total_item_count` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Total Item Count',
`customer_gender` int(11) DEFAULT NULL COMMENT 'Customer Gender',
`hidden_tax_amount` decimal(12,4) DEFAULT NULL COMMENT 'Hidden Tax Amount',
`base_hidden_tax_amount` decimal(12,4) DEFAULT NULL COMMENT 'Base Hidden Tax Amount',
`shipping_hidden_tax_amount` decimal(12,4) DEFAULT NULL COMMENT 'Shipping Hidden Tax Amount',
`base_shipping_hidden_tax_amnt` decimal(12,4) DEFAULT NULL COMMENT 'Base Shipping Hidden Tax Amount',
`hidden_tax_invoiced` decimal(12,4) DEFAULT NULL COMMENT 'Hidden Tax Invoiced',
`base_hidden_tax_invoiced` decimal(12,4) DEFAULT NULL COMMENT 'Base Hidden Tax Invoiced',
`hidden_tax_refunded` decimal(12,4) DEFAULT NULL COMMENT 'Hidden Tax Refunded',
`base_hidden_tax_refunded` decimal(12,4) DEFAULT NULL COMMENT 'Base Hidden Tax Refunded',
`shipping_incl_tax` decimal(12,4) DEFAULT NULL COMMENT 'Shipping Incl Tax',
`base_shipping_incl_tax` decimal(12,4) DEFAULT NULL COMMENT 'Base Shipping Incl Tax',
`coupon_rule_name` varchar(255) DEFAULT NULL COMMENT 'Coupon Sales Rule Name',
`paypal_ipn_customer_notified` int(11) DEFAULT '0' COMMENT 'Paypal Ipn Customer Notified',
`gift_message_id` int(11) DEFAULT NULL COMMENT 'Gift Message Id',
`purchase_from` varchar(250) DEFAULT 'Main Website' COMMENT 'Purchase from App Or Website',
`purchase_admin_id` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`entity_id`),
UNIQUE KEY `UNQ_SALES_FLAT_ORDER_INCREMENT_ID` (`increment_id`),
KEY `IDX_SALES_FLAT_ORDER_STATUS` (`status`),
KEY `IDX_SALES_FLAT_ORDER_STATE` (`state`),
KEY `IDX_SALES_FLAT_ORDER_STORE_ID` (`store_id`),
KEY `IDX_SALES_FLAT_ORDER_CREATED_AT` (`created_at`),
KEY `IDX_SALES_FLAT_ORDER_CUSTOMER_ID` (`customer_id`),
KEY `IDX_SALES_FLAT_ORDER_EXT_ORDER_ID` (`ext_order_id`),
KEY `IDX_SALES_FLAT_ORDER_QUOTE_ID` (`quote_id`),
KEY `IDX_SALES_FLAT_ORDER_UPDATED_AT` (`updated_at`),
CONSTRAINT `FK_SALES_FLAT_ORDER_CUSTOMER_ID_CUSTOMER_ENTITY_ENTITY_ID` FOREIGN KEY (`customer_id`) REFERENCES `customer_entity` (`entity_id`) ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT `FK_SALES_FLAT_ORDER_STORE_ID_CORE_STORE_STORE_ID` FOREIGN KEY (`store_id`) REFERENCES `core_store` (`store_id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=664041 DEFAULT CHARSET=utf8 COMMENT='Sales Flat Order'



Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
sales_flat_order 0 PRIMARY 1 entity_id A 463544 BTREE
sales_flat_order 0 UNQ_SALES_FLAT_ORDER_INCREMENT_ID 1 increment_id A 463544 YES BTREE
sales_flat_order 1 IDX_SALES_FLAT_ORDER_STATUS 1 status A 98 YES BTREE
sales_flat_order 1 IDX_SALES_FLAT_ORDER_STATE 1 state A 16 YES BTREE
sales_flat_order 1 IDX_SALES_FLAT_ORDER_STORE_ID 1 store_id A 2 YES BTREE
sales_flat_order 1 IDX_SALES_FLAT_ORDER_CREATED_AT 1 created_at A 463544 YES BTREE
sales_flat_order 1 IDX_SALES_FLAT_ORDER_CUSTOMER_ID 1 customer_id A 154514 YES BTREE
sales_flat_order 1 IDX_SALES_FLAT_ORDER_EXT_ORDER_ID 1 ext_order_id A 2 YES BTREE
sales_flat_order 1 IDX_SALES_FLAT_ORDER_QUOTE_ID 1 quote_id A 463544 YES BTREE
sales_flat_order 1 IDX_SALES_FLAT_ORDER_UPDATED_AT 1 updated_at A 463544 YES BTREE

----------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE `sales_flat_order_payment` (
`entity_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Entity Id',
`parent_id` int(10) unsigned NOT NULL COMMENT 'Parent Id',
`base_shipping_captured` decimal(12,4) DEFAULT NULL COMMENT 'Base Shipping Captured',
`shipping_captured` decimal(12,4) DEFAULT NULL COMMENT 'Shipping Captured',
`amount_refunded` decimal(12,4) DEFAULT NULL COMMENT 'Amount Refunded',
`base_amount_paid` decimal(12,4) DEFAULT NULL COMMENT 'Base Amount Paid',
`amount_canceled` decimal(12,4) DEFAULT NULL COMMENT 'Amount Canceled',
`base_amount_authorized` decimal(12,4) DEFAULT NULL COMMENT 'Base Amount Authorized',
`base_amount_paid_online` decimal(12,4) DEFAULT NULL COMMENT 'Base Amount Paid Online',
`base_amount_refunded_online` decimal(12,4) DEFAULT NULL COMMENT 'Base Amount Refunded Online',
`base_shipping_amount` decimal(12,4) DEFAULT NULL COMMENT 'Base Shipping Amount',
`shipping_amount` decimal(12,4) DEFAULT NULL COMMENT 'Shipping Amount',
`amount_paid` decimal(12,4) DEFAULT NULL COMMENT 'Amount Paid',
`amount_authorized` decimal(12,4) DEFAULT NULL COMMENT 'Amount Authorized',
`base_amount_ordered` decimal(12,4) DEFAULT NULL COMMENT 'Base Amount Ordered',
`base_shipping_refunded` decimal(12,4) DEFAULT NULL COMMENT 'Base Shipping Refunded',
`shipping_refunded` decimal(12,4) DEFAULT NULL COMMENT 'Shipping Refunded',
`base_amount_refunded` decimal(12,4) DEFAULT NULL COMMENT 'Base Amount Refunded',
`amount_ordered` decimal(12,4) DEFAULT NULL COMMENT 'Amount Ordered',
`base_amount_canceled` decimal(12,4) DEFAULT NULL COMMENT 'Base Amount Canceled',
`quote_payment_id` int(11) DEFAULT NULL COMMENT 'Quote Payment Id',
`additional_data` text COMMENT 'Additional Data',
`cc_exp_month` varchar(255) DEFAULT NULL COMMENT 'Cc Exp Month',
`cc_ss_start_year` varchar(255) DEFAULT NULL COMMENT 'Cc Ss Start Year',
`echeck_bank_name` varchar(255) DEFAULT NULL COMMENT 'Echeck Bank Name',
`method` varchar(255) DEFAULT NULL COMMENT 'Method',
`cc_debug_request_body` varchar(255) DEFAULT NULL COMMENT 'Cc Debug Request Body',
`cc_secure_verify` varchar(255) DEFAULT NULL COMMENT 'Cc Secure Verify',
`protection_eligibility` varchar(255) DEFAULT NULL COMMENT 'Protection Eligibility',
`cc_approval` varchar(255) DEFAULT NULL COMMENT 'Cc Approval',
`cc_last4` varchar(255) DEFAULT NULL COMMENT 'Cc Last4',
`cc_status_description` varchar(255) DEFAULT NULL COMMENT 'Cc Status Description',
`echeck_type` varchar(255) DEFAULT NULL COMMENT 'Echeck Type',
`cc_debug_response_serialized` varchar(255) DEFAULT NULL COMMENT 'Cc Debug Response Serialized',
`cc_ss_start_month` varchar(255) DEFAULT NULL COMMENT 'Cc Ss Start Month',
`echeck_account_type` varchar(255) DEFAULT NULL COMMENT 'Echeck Account Type',
`last_trans_id` varchar(255) DEFAULT NULL COMMENT 'Last Trans Id',
`cc_cid_status` varchar(255) DEFAULT NULL COMMENT 'Cc Cid Status',
`cc_owner` varchar(255) DEFAULT NULL COMMENT 'Cc Owner',
`cc_type` varchar(255) DEFAULT NULL COMMENT 'Cc Type',
`po_number` varchar(255) DEFAULT NULL COMMENT 'Po Number',
`cc_exp_year` varchar(255) DEFAULT NULL COMMENT 'Cc Exp Year',
`cc_status` varchar(255) DEFAULT NULL COMMENT 'Cc Status',
`echeck_routing_number` varchar(255) DEFAULT NULL COMMENT 'Echeck Routing Number',
`account_status` varchar(255) DEFAULT NULL COMMENT 'Account Status',
`anet_trans_method` varchar(255) DEFAULT NULL COMMENT 'Anet Trans Method',
`cc_debug_response_body` varchar(255) DEFAULT NULL COMMENT 'Cc Debug Response Body',
`cc_ss_issue` varchar(255) DEFAULT NULL COMMENT 'Cc Ss Issue',
`echeck_account_name` varchar(255) DEFAULT NULL COMMENT 'Echeck Account Name',
`cc_avs_status` varchar(255) DEFAULT NULL COMMENT 'Cc Avs Status',
`cc_number_enc` varchar(255) DEFAULT NULL COMMENT 'Cc Number Enc',
`cc_trans_id` varchar(255) DEFAULT NULL COMMENT 'Cc Trans Id',
`paybox_request_number` varchar(255) DEFAULT NULL COMMENT 'Paybox Request Number',
`address_status` varchar(255) DEFAULT NULL COMMENT 'Address Status',
`additional_information` text COMMENT 'Additional Information',
PRIMARY KEY (`entity_id`),
KEY `IDX_SALES_FLAT_ORDER_PAYMENT_PARENT_ID` (`parent_id`),
CONSTRAINT `FK_SALES_FLAT_ORDER_PAYMENT_PARENT_ID_SALES_FLAT_ORDER_ENTITY_ID` FOREIGN KEY (`parent_id`) REFERENCES `sales_flat_order` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=657428 DEFAULT CHARSET=utf8 COMMENT='Sales Flat Order Payment'



Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
sales_flat_order_payment 0 PRIMARY 1 entity_id A 556724 BTREE
sales_flat_order_payment 1 IDX_SALES_FLAT_ORDER_PAYMENT_PARENT_ID 1 parent_id A 556724 BTREE
---------------------------------------------------------------------------------------------------------------------------------------------------------------------CREATE TABLE `sales_flat_order_item` (
`item_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Item Id',
`order_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Order Id',
`parent_item_id` int(10) unsigned DEFAULT NULL COMMENT 'Parent Item Id',
`quote_item_id` int(10) unsigned DEFAULT NULL COMMENT 'Quote Item Id',
`store_id` smallint(5) unsigned DEFAULT NULL COMMENT 'Store Id',
`created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Created At',
`updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Updated At',
`product_id` int(10) unsigned DEFAULT NULL COMMENT 'Product Id',
`product_type` varchar(255) DEFAULT NULL COMMENT 'Product Type',
`product_options` text COMMENT 'Product Options',
`weight` decimal(12,4) DEFAULT '0.0000' COMMENT 'Weight',
`is_virtual` smallint(5) unsigned DEFAULT NULL COMMENT 'Is Virtual',
`sku` varchar(255) DEFAULT NULL COMMENT 'Sku',
`name` varchar(255) DEFAULT NULL COMMENT 'Name',
`description` text COMMENT 'Description',
`applied_rule_ids` text COMMENT 'Applied Rule Ids',
`additional_data` text COMMENT 'Additional Data',
`free_shipping` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Free Shipping',
`is_qty_decimal` smallint(5) unsigned DEFAULT NULL COMMENT 'Is Qty Decimal',
`no_discount` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'No Discount',
`qty_backordered` decimal(12,4) DEFAULT '0.0000' COMMENT 'Qty Backordered',
`qty_canceled` decimal(12,4) DEFAULT '0.0000' COMMENT 'Qty Canceled',
`qty_invoiced` decimal(12,4) DEFAULT '0.0000' COMMENT 'Qty Invoiced',
`qty_ordered` decimal(12,4) DEFAULT '0.0000' COMMENT 'Qty Ordered',
`qty_refunded` decimal(12,4) DEFAULT '0.0000' COMMENT 'Qty Refunded',
`qty_shipped` decimal(12,4) DEFAULT '0.0000' COMMENT 'Qty Shipped',
`base_cost` decimal(12,4) DEFAULT '0.0000' COMMENT 'Base Cost',
`price` decimal(12,4) NOT NULL DEFAULT '0.0000' COMMENT 'Price',
`base_price` decimal(12,4) NOT NULL DEFAULT '0.0000' COMMENT 'Base Price',
`original_price` decimal(12,4) DEFAULT NULL COMMENT 'Original Price',
`base_original_price` decimal(12,4) DEFAULT NULL COMMENT 'Base Original Price',
`tax_percent` decimal(12,4) DEFAULT '0.0000' COMMENT 'Tax Percent',
`tax_amount` decimal(12,4) DEFAULT '0.0000' COMMENT 'Tax Amount',
`base_tax_amount` decimal(12,4) DEFAULT '0.0000' COMMENT 'Base Tax Amount',
`tax_invoiced` decimal(12,4) DEFAULT '0.0000' COMMENT 'Tax Invoiced',
`base_tax_invoiced` decimal(12,4) DEFAULT '0.0000' COMMENT 'Base Tax Invoiced',
`discount_percent` decimal(12,4) DEFAULT '0.0000' COMMENT 'Discount Percent',
`discount_amount` decimal(12,4) DEFAULT '0.0000' COMMENT 'Discount Amount',
`base_discount_amount` decimal(12,4) DEFAULT '0.0000' COMMENT 'Base Discount Amount',
`discount_invoiced` decimal(12,4) DEFAULT '0.0000' COMMENT 'Discount Invoiced',
`base_discount_invoiced` decimal(12,4) DEFAULT '0.0000' COMMENT 'Base Discount Invoiced',
`amount_refunded` decimal(12,4) DEFAULT '0.0000' COMMENT 'Amount Refunded',
`base_amount_refunded` decimal(12,4) DEFAULT '0.0000' COMMENT 'Base Amount Refunded',
`row_total` decimal(12,4) NOT NULL DEFAULT '0.0000' COMMENT 'Row Total',
`base_row_total` decimal(12,4) NOT NULL DEFAULT '0.0000' COMMENT 'Base Row Total',
`row_invoiced` decimal(12,4) NOT NULL DEFAULT '0.0000' COMMENT 'Row Invoiced',
`base_row_invoiced` decimal(12,4) NOT NULL DEFAULT '0.0000' COMMENT 'Base Row Invoiced',
`row_weight` decimal(12,4) DEFAULT '0.0000' COMMENT 'Row Weight',
`base_tax_before_discount` decimal(12,4) DEFAULT NULL COMMENT 'Base Tax Before Discount',
`tax_before_discount` decimal(12,4) DEFAULT NULL COMMENT 'Tax Before Discount',
`ext_order_item_id` varchar(255) DEFAULT NULL COMMENT 'Ext Order Item Id',
`locked_do_invoice` smallint(5) unsigned DEFAULT NULL COMMENT 'Locked Do Invoice',
`locked_do_ship` smallint(5) unsigned DEFAULT NULL COMMENT 'Locked Do Ship',
`price_incl_tax` decimal(12,4) DEFAULT NULL COMMENT 'Price Incl Tax',
`base_price_incl_tax` decimal(12,4) DEFAULT NULL COMMENT 'Base Price Incl Tax',
`row_total_incl_tax` decimal(12,4) DEFAULT NULL COMMENT 'Row Total Incl Tax',
`base_row_total_incl_tax` decimal(12,4) DEFAULT NULL COMMENT 'Base Row Total Incl Tax',
`hidden_tax_amount` decimal(12,4) DEFAULT NULL COMMENT 'Hidden Tax Amount',
`base_hidden_tax_amount` decimal(12,4) DEFAULT NULL COMMENT 'Base Hidden Tax Amount',
`hidden_tax_invoiced` decimal(12,4) DEFAULT NULL COMMENT 'Hidden Tax Invoiced',
`base_hidden_tax_invoiced` decimal(12,4) DEFAULT NULL COMMENT 'Base Hidden Tax Invoiced',
`hidden_tax_refunded` decimal(12,4) DEFAULT NULL COMMENT 'Hidden Tax Refunded',
`base_hidden_tax_refunded` decimal(12,4) DEFAULT NULL COMMENT 'Base Hidden Tax Refunded',
`is_nominal` int(11) NOT NULL DEFAULT '0' COMMENT 'Is Nominal',
`tax_canceled` decimal(12,4) DEFAULT NULL COMMENT 'Tax Canceled',
`hidden_tax_canceled` decimal(12,4) DEFAULT NULL COMMENT 'Hidden Tax Canceled',
`tax_refunded` decimal(12,4) DEFAULT NULL COMMENT 'Tax Refunded',
`base_tax_refunded` decimal(12,4) DEFAULT NULL COMMENT 'Base Tax Refunded',
`discount_refunded` decimal(12,4) DEFAULT NULL COMMENT 'Discount Refunded',
`base_discount_refunded` decimal(12,4) DEFAULT NULL COMMENT 'Base Discount Refunded',
`gift_message_id` int(11) DEFAULT NULL COMMENT 'Gift Message Id',
`gift_message_available` int(11) DEFAULT NULL COMMENT 'Gift Message Available',
`base_weee_tax_applied_amount` decimal(12,4) DEFAULT NULL COMMENT 'Base Weee Tax Applied Amount',
`base_weee_tax_applied_row_amnt` decimal(12,4) DEFAULT NULL COMMENT 'Base Weee Tax Applied Row Amnt',
`weee_tax_applied_amount` decimal(12,4) DEFAULT NULL COMMENT 'Weee Tax Applied Amount',
`weee_tax_applied_row_amount` decimal(12,4) DEFAULT NULL COMMENT 'Weee Tax Applied Row Amount',
`weee_tax_applied` text COMMENT 'Weee Tax Applied',
`weee_tax_disposition` decimal(12,4) DEFAULT NULL COMMENT 'Weee Tax Disposition',
`weee_tax_row_disposition` decimal(12,4) DEFAULT NULL COMMENT 'Weee Tax Row Disposition',
`base_weee_tax_disposition` decimal(12,4) DEFAULT NULL COMMENT 'Base Weee Tax Disposition',
`base_weee_tax_row_disposition` decimal(12,4) DEFAULT NULL COMMENT 'Base Weee Tax Row Disposition',
`tolexo_base_price` decimal(12,4) DEFAULT NULL,
`tolexo_delivery_details` int(11) DEFAULT NULL COMMENT 'Capture commited delivery time on order place',
`item_state` varchar(50) DEFAULT NULL,
`seller_state` varchar(50) DEFAULT NULL,
`item_status` varchar(32) DEFAULT NULL,
PRIMARY KEY (`item_id`),
KEY `IDX_SALES_FLAT_ORDER_ITEM_ORDER_ID` (`order_id`),
KEY `IDX_SALES_FLAT_ORDER_ITEM_STORE_ID` (`store_id`),
CONSTRAINT `FK_SALES_FLAT_ORDER_ITEM_ORDER_ID_SALES_FLAT_ORDER_ENTITY_ID` FOREIGN KEY (`order_id`) REFERENCES `sales_flat_order` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `FK_SALES_FLAT_ORDER_ITEM_STORE_ID_CORE_STORE_STORE_ID` FOREIGN KEY (`store_id`) REFERENCES `core_store` (`store_id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=853495 DEFAULT CHARSET=utf8 COMMENT='Sales Flat Order Item'



Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
sales_flat_order_item 0 PRIMARY 1 item_id A 602755 BTREE
sales_flat_order_item 1 IDX_SALES_FLAT_ORDER_ITEM_ORDER_ID 1 order_id A 602755 BTREE
sales_flat_order_item 1 IDX_SALES_FLAT_ORDER_ITEM_STORE_ID 1 store_id A 2 YES BTREE

---------------------------------------------------------------------------------------------------------------------------------------------------------------------


CREATE TABLE `catalog_product_entity_int` (
`value_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Value ID',
`entity_type_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Entity Type ID',
`attribute_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Attribute ID',
`store_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Store ID',
`entity_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Entity ID',
`value` int(11) DEFAULT NULL COMMENT 'Value',
PRIMARY KEY (`value_id`),
UNIQUE KEY `UNQ_CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID` (`entity_id`,`attribute_id`,`store_id`),
KEY `IDX_CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID` (`attribute_id`),
KEY `IDX_CATALOG_PRODUCT_ENTITY_INT_STORE_ID` (`store_id`),
KEY `IDX_CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID` (`entity_id`),
CONSTRAINT `FK_CATALOG_PRODUCT_ENTITY_INT_STORE_ID_CORE_STORE_STORE_ID` FOREIGN KEY (`store_id`) REFERENCES `core_store` (`store_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `FK_CAT_PRD_ENTT_INT_ATTR_ID_EAV_ATTR_ATTR_ID` FOREIGN KEY (`attribute_id`) REFERENCES `eav_attribute` (`attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `FK_CAT_PRD_ENTT_INT_ENTT_ID_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`entity_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=63944111 DEFAULT CHARSET=utf8 COMMENT='Catalog Product Integer Attribute Backend Table'


Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
catalog_product_entity_int 0 PRIMARY 1 value_id A 17889235 BTREE
catalog_product_entity_int 0 UNQ_CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID 1 entity_id A 2555605 BTREE
catalog_product_entity_int 0 UNQ_CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID 2 attribute_id A 17889235 BTREE
catalog_product_entity_int 0 UNQ_CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID 3 store_id A 17889235 BTREE
catalog_product_entity_int 1 IDX_CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID 1 attribute_id A 1564 BTREE
catalog_product_entity_int 1 IDX_CATALOG_PRODUCT_ENTITY_INT_STORE_ID 1 store_id A 2 BTREE
catalog_product_entity_int 1 IDX_CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID 1 entity_id A 2981539
BTREE

--------------------------------------------------------------------------------------------------------------------------------------------------------------------

CREATE TABLE `sales_order_single_item` (
`sales_order_single_item_id` int(11) NOT NULL AUTO_INCREMENT,
`id_item` int(11) DEFAULT NULL,
`id_order` int(11) DEFAULT NULL,
`id_invoice` int(11) DEFAULT NULL,
`code` varchar(255) DEFAULT NULL,
`id_wms_state` int(11) DEFAULT NULL,
`id_shipment` int(11) DEFAULT NULL,
`shipment_status` varchar(50) DEFAULT NULL,
`is_customer_notified` tinyint(1) DEFAULT '0' COMMENT 'Is Customer Notified',
`is_eligible_sc` tinyint(4) NOT NULL DEFAULT '0' COMMENT '0=>disable,1=>enable',
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
PRIMARY KEY (`sales_order_single_item_id`),
UNIQUE KEY `UNI_item_id_order_id_code` (`id_item`,`id_order`,`code`),
KEY `idx_notified` (`is_customer_notified`),
KEY `FK_sales_order_single_item_wms_state_wms_state_id` (`id_wms_state`),
KEY `IDX_id_order` (`id_order`),
CONSTRAINT `FK_sales_order_single_item_wms_state_wms_state_id` FOREIGN KEY (`id_wms_state`) REFERENCES `wms_state` (`wms_state_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=61437381 DEFAULT CHARSET=latin1


Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
sales_order_single_item 0 PRIMARY 1 sales_order_single_item_id A 1041430 BTREE
sales_order_single_item 0 UNI_item_id_order_id_code 1 id_item A 1041430 YES BTREE
sales_order_single_item 0 UNI_item_id_order_id_code 2 id_order A 1041430 YES BTREE
sales_order_single_item 0 UNI_item_id_order_id_code 3 code A 1041430 YES BTREE
sales_order_single_item 1 idx_notified 1 is_customer_notified A 2 YES BTREE
sales_order_single_item 1 FK_sales_order_single_item_wms_state_wms_state_id 1 id_wms_state A 94 YES BTREE
sales_order_single_item 1 IDX_id_order 1 id_order A 1041430 YES BTREE

--------------------------------------------------------------------------------------------------------------------------------------------------------------------

CREATE TABLE `sales_order_single_item` (
`sales_order_single_item_id` int(11) NOT NULL AUTO_INCREMENT,
`id_item` int(11) DEFAULT NULL,
`id_order` int(11) DEFAULT NULL,
`id_invoice` int(11) DEFAULT NULL,
`code` varchar(255) DEFAULT NULL,
`id_wms_state` int(11) DEFAULT NULL,
`id_shipment` int(11) DEFAULT NULL,
`shipment_status` varchar(50) DEFAULT NULL,
`is_customer_notified` tinyint(1) DEFAULT '0' COMMENT 'Is Customer Notified',
`is_eligible_sc` tinyint(4) NOT NULL DEFAULT '0' COMMENT '0=>disable,1=>enable',
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
PRIMARY KEY (`sales_order_single_item_id`),
UNIQUE KEY `UNI_item_id_order_id_code` (`id_item`,`id_order`,`code`),
KEY `idx_notified` (`is_customer_notified`),
KEY `FK_sales_order_single_item_wms_state_wms_state_id` (`id_wms_state`),
KEY `IDX_id_order` (`id_order`),
CONSTRAINT `FK_sales_order_single_item_wms_state_wms_state_id` FOREIGN KEY (`id_wms_state`) REFERENCES `wms_state` (`wms_state_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=61437381 DEFAULT CHARSET=latin1


Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
sales_order_single_item 0 PRIMARY 1 sales_order_single_item_id A 1041430 BTREE
sales_order_single_item 0 UNI_item_id_order_id_code 1 id_item A 1041430 YES BTREE
sales_order_single_item 0 UNI_item_id_order_id_code 2 id_order A 1041430 YES BTREE
sales_order_single_item 0 UNI_item_id_order_id_code 3 code A 1041430 YES BTREE
sales_order_single_item 1 idx_notified 1 is_customer_notified A 2 YES BTREE
sales_order_single_item 1 FK_sales_order_single_item_wms_state_wms_state_id 1 id_wms_state A 94 YES BTREE
sales_order_single_item 1 IDX_id_order 1 id_order A 1041430 YES BTREE

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------

CREATE TABLE `wms_tolexo_state_map` (
`wms_tolexo_state_map_id` int(11) NOT NULL AUTO_INCREMENT,
`id_wms_state` int(11) DEFAULT NULL,
`id_order_state` int(11) DEFAULT NULL,
`id_seller_state` int(11) DEFAULT NULL,
`id_invoice_state` int(11) DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
PRIMARY KEY (`wms_tolexo_state_map_id`),
UNIQUE KEY `FK_wms_tolexo_state_map_wms_state_wms_state_id` (`id_wms_state`),
KEY `FK_wms_tolexo_state_map_tolexo_state_order_state_id` (`id_order_state`),
KEY `FK_wms_tolexo_state_map_tolexo_state_seller_state_id` (`id_seller_state`),
KEY `FK_wms_tolexo_state_map_tolexo_state_invoice_state_id` (`id_invoice_state`),
CONSTRAINT `FK_wms_tolexo_state_map_tolexo_state_invoice_state_id` FOREIGN KEY (`id_invoice_state`) REFERENCES `tolexo_state` (`tolexo_state_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `FK_wms_tolexo_state_map_tolexo_state_order_state_id` FOREIGN KEY (`id_order_state`) REFERENCES `tolexo_state` (`tolexo_state_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `FK_wms_tolexo_state_map_tolexo_state_seller_state_id` FOREIGN KEY (`id_seller_state`) REFERENCES `tolexo_state` (`tolexo_state_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `FK_wms_tolexo_state_map_wms_state_wms_state_id` FOREIGN KEY (`id_wms_state`) REFERENCES `wms_state` (`wms_state_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=89 DEFAULT CHARSET=utf8


Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
wms_tolexo_state_map 0 PRIMARY 1 wms_tolexo_state_map_id A 85 BTREE
wms_tolexo_state_map 0 FK_wms_tolexo_state_map_wms_state_wms_state_id 1 id_wms_state A 85 YES BTREE
wms_tolexo_state_map 1 FK_wms_tolexo_state_map_tolexo_state_order_state_id 1 id_order_state A 21 YES BTREE
wms_tolexo_state_map 1 FK_wms_tolexo_state_map_tolexo_state_seller_state_id 1 id_seller_state A 28 YES BTREE
wms_tolexo_state_map 1 FK_wms_tolexo_state_map_tolexo_state_invoice_state_id 1 id_invoice_state A 17 YES BTREE

---------------------------------------------------------------------------------------------------------------------------------------------------------------------

CREATE TABLE `tolexo_state` (
`tolexo_state_id` int(11) NOT NULL AUTO_INCREMENT,
`state` varchar(50) DEFAULT NULL,
`object` enum('seller_center','invoice','order_item') DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
PRIMARY KEY (`tolexo_state_id`),
KEY `FK_tolexo_status_tolexo_system_id_tolexo_system` (`object`)
) ENGINE=InnoDB AUTO_INCREMENT=34 DEFAULT CHARSET=utf8

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
tolexo_state 0 PRIMARY 1 tolexo_state_id A 33 BTREE
tolexo_state 1 FK_tolexo_status_tolexo_system_id_tolexo_system 1 object A 6 YES BTREE

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

CREATE TABLE `vendor_vendordirectory_vendors` (
`vendor_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Vendor_id',
`gluser_id` int(10) DEFAULT NULL,
`created_at` datetime NOT NULL COMMENT 'Created_at',
`updated_at` datetime NOT NULL COMMENT 'Updated_at',
`company_name` varchar(255) NOT NULL COMMENT 'Company_name',
`display_name` varchar(255) NOT NULL COMMENT 'Display_name',
`salutation` int(11) DEFAULT NULL COMMENT 'Salutation',
`first_name` varchar(50) NOT NULL COMMENT 'First_name',
`last_name` varchar(50) NOT NULL COMMENT 'Last_name',
`email` varchar(255) NOT NULL COMMENT 'Email',
`contact_number` varchar(20) NOT NULL,
`mobile_number` varchar(20) DEFAULT NULL,
`url_key` varchar(255) DEFAULT NULL COMMENT 'Url_key',
`merchant_type` int(11) NOT NULL COMMENT 'Merchant_type',
`vendor_categories` varchar(255) DEFAULT NULL COMMENT 'Vendor_categories',
`vendor_brands1` varchar(255) DEFAULT NULL COMMENT 'Vendor_brands1',
`vendor_brands2` varchar(255) DEFAULT NULL COMMENT 'Vendor_brands2',
`vendor_brands3` varchar(255) DEFAULT NULL COMMENT 'Vendor_brands3',
`vendor_brands4` varchar(255) DEFAULT NULL COMMENT 'Vendor_brands4',
`vendor_brands5` varchar(255) DEFAULT NULL COMMENT 'Vendor_brands5',
`vendor_brands6` varchar(255) DEFAULT NULL COMMENT 'Vendor_brands6',
`vendor_brands7` varchar(255) DEFAULT NULL COMMENT 'Vendor_brands7',
`vendor_brands8` varchar(255) DEFAULT NULL COMMENT 'Vendor_brands8',
`vendor_brands9` varchar(255) DEFAULT NULL COMMENT 'Vendor_brands9',
`vendor_brands10` varchar(255) DEFAULT NULL COMMENT 'Vendor_brands10',
`auth_partner` int(11) DEFAULT NULL COMMENT 'Auth_partner',
`auth_brand1` varchar(255) DEFAULT NULL COMMENT 'Auth_brand1',
`auth_brand2` varchar(255) DEFAULT NULL COMMENT 'Auth_brand2',
`auth_brand3` varchar(255) DEFAULT NULL COMMENT 'Auth_brand3',
`auth_brand4` varchar(255) DEFAULT NULL COMMENT 'Auth_brand4',
`auth_brand5` varchar(255) DEFAULT NULL COMMENT 'Auth_brand5',
`private_label` int(11) DEFAULT NULL COMMENT 'Private_label',
`private_name` varchar(255) DEFAULT NULL COMMENT 'Private_name',
`address` varchar(255) NOT NULL COMMENT 'Address',
`city` varchar(100) NOT NULL COMMENT 'City',
`landmark` varchar(255) DEFAULT NULL COMMENT 'Landmark',
`state` varchar(50) NOT NULL COMMENT 'State',
`pincode` int(11) NOT NULL COMMENT 'Pincode',
`country` varchar(30) NOT NULL COMMENT 'Country',
`copy_bill` int(11) DEFAULT NULL COMMENT 'Copy_bill',
`bill_address` varchar(255) NOT NULL COMMENT 'Bill_address',
`bill_city` varchar(20) NOT NULL COMMENT 'Bill_city',
`bill_landmark` varchar(255) DEFAULT NULL COMMENT 'Bill_landmark',
`bill_state` varchar(50) NOT NULL COMMENT 'Bill_state',
`bill_pincode` int(11) NOT NULL COMMENT 'Bill_pincode',
`bill_country` varchar(30) NOT NULL COMMENT 'Bill_country',
`pan_number` varchar(255) NOT NULL COMMENT 'Pan_number',
`pan_copy` int(11) DEFAULT NULL COMMENT 'Pan_copy',
`tin_number` varchar(255) NOT NULL COMMENT 'Tin_number',
`tin_copy` int(11) DEFAULT NULL COMMENT 'Tin_copy',
`comp_reg_no` varchar(255) NOT NULL COMMENT 'Comp_reg_no',
`comp_reg_copy` int(11) NOT NULL COMMENT 'Comp_reg_copy',
`vtc_number` varchar(255) NOT NULL COMMENT 'Vtc_number',
`vtc_copy` int(11) DEFAULT NULL COMMENT 'Vtc_copy',
`bank_name` varchar(200) DEFAULT NULL,
`branch_name` varchar(200) DEFAULT NULL,
`beneficiary_name` varchar(200) DEFAULT NULL,
`acc_number` varchar(50) NOT NULL COMMENT 'Acc_number',
`acc_type` int(11) DEFAULT NULL COMMENT 'Acc_type',
`neft_code` varchar(255) NOT NULL COMMENT 'Neft_code',
`vendor_code` varchar(255) DEFAULT NULL COMMENT 'Vendor Code',
`uniware_vendor_sync` tinyint(1) DEFAULT '0' COMMENT 'Uniware Vendor Sync',
`dropship` varchar(20) DEFAULT NULL,
`wms_id` tinyint(4) NOT NULL DEFAULT '1' COMMENT 'Warehouse Management System',
`user_id` int(11) NOT NULL,
`sales_force_id` varchar(100) DEFAULT NULL,
`is_onboarding` enum('0','1') DEFAULT '0',
`enable_uniware` tinyint(1) DEFAULT '1' COMMENT 'Enable vendor for push in uniware',
`synced_at` datetime DEFAULT '2014-01-01 00:00:00',
PRIMARY KEY (`vendor_id`),
KEY `VENDOR_USER_ID` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=25391 DEFAULT CHARSET=utf8 COMMENT='vendor_vendordirectory_vendors'

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
vendor_vendordirectory_vendors 0 PRIMARY 1 vendor_id A 17402 BTREE
vendor_vendordirectory_vendors 1 VENDOR_USER_ID 1 user_id A 17402 BTREE

----------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE `sc_order_item_tags` (
`sc_order_item_tags_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Sc Order Item Id',
`id_item` int(10) unsigned NOT NULL COMMENT 'Id Order item',
`id_tags` int(10) NOT NULL COMMENT 'Id Tags',
`id_user` int(50) DEFAULT NULL COMMENT 'Id User',
`schedule_date` date DEFAULT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`sc_order_item_tags_id`),
KEY `sc_order_item_tags_id_item` (`id_item`),
KEY `sc_order_item_tags_id_tags` (`id_tags`),
KEY `sc_order_item_tags_id_user` (`id_user`)
) ENGINE=InnoDB AUTO_INCREMENT=35027 DEFAULT CHARSET=utf8 COMMENT='Sales Order Item Status History'

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
sc_order_item_tags 0 PRIMARY 1 sc_order_item_tags_id A 35325 BTREE
sc_order_item_tags 1 sc_order_item_tags_id_item 1 id_item A 35325 BTREE
sc_order_item_tags 1 sc_order_item_tags_id_tags 1 id_tags A 20 BTREE
sc_order_item_tags 1 sc_order_item_tags_id_user 1 id_user A 2207 YES BTREE
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------


CREATE TABLE `sc_order_item_tags` (
`sc_order_item_tags_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Sc Order Item Id',
`id_item` int(10) unsigned NOT NULL COMMENT 'Id Order item',
`id_tags` int(10) NOT NULL COMMENT 'Id Tags',
`id_user` int(50) DEFAULT NULL COMMENT 'Id User',
`schedule_date` date DEFAULT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`sc_order_item_tags_id`),
KEY `sc_order_item_tags_id_item` (`id_item`),
KEY `sc_order_item_tags_id_tags` (`id_tags`),
KEY `sc_order_item_tags_id_user` (`id_user`)
) ENGINE=InnoDB AUTO_INCREMENT=35029 DEFAULT CHARSET=utf8 COMMENT='Sales Order Item Status History'


Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
sc_order_item_tags 0 PRIMARY 1 sc_order_item_tags_id A 35326 BTREE
sc_order_item_tags 1 sc_order_item_tags_id_item 1 id_item A 35326 BTREE
sc_order_item_tags 1 sc_order_item_tags_id_tags 1 id_tags A 20 BTREE
sc_order_item_tags 1 sc_order_item_tags_id_user 1 id_user A 2207 YES BTREE

---------------------------------------------------------------------------------------------------------------------------------------------------------------------

CREATE TABLE `sales_flat_invoice` (
`entity_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Entity Id',
`store_id` smallint(5) unsigned DEFAULT NULL COMMENT 'Store Id',
`base_grand_total` decimal(12,4) DEFAULT NULL COMMENT 'Base Grand Total',
`shipping_tax_amount` decimal(12,4) DEFAULT NULL COMMENT 'Shipping Tax Amount',
`tax_amount` decimal(12,4) DEFAULT NULL COMMENT 'Tax Amount',
`base_tax_amount` decimal(12,4) DEFAULT NULL COMMENT 'Base Tax Amount',
`store_to_order_rate` decimal(12,4) DEFAULT NULL COMMENT 'Store To Order Rate',
`base_shipping_tax_amount` decimal(12,4) DEFAULT NULL COMMENT 'Base Shipping Tax Amount',
`base_discount_amount` decimal(12,4) DEFAULT NULL COMMENT 'Base Discount Amount',
`base_to_order_rate` decimal(12,4) DEFAULT NULL COMMENT 'Base To Order Rate',
`grand_total` decimal(12,4) DEFAULT NULL COMMENT 'Grand Total',
`shipping_amount` decimal(12,4) DEFAULT NULL COMMENT 'Shipping Amount',
`subtotal_incl_tax` decimal(12,4) DEFAULT NULL COMMENT 'Subtotal Incl Tax',
`base_subtotal_incl_tax` decimal(12,4) DEFAULT NULL COMMENT 'Base Subtotal Incl Tax',
`store_to_base_rate` decimal(12,4) DEFAULT NULL COMMENT 'Store To Base Rate',
`base_shipping_amount` decimal(12,4) DEFAULT NULL COMMENT 'Base Shipping Amount',
`total_qty` decimal(12,4) DEFAULT NULL COMMENT 'Total Qty',
`base_to_global_rate` decimal(12,4) DEFAULT NULL COMMENT 'Base To Global Rate',
`subtotal` decimal(12,4) DEFAULT NULL COMMENT 'Subtotal',
`base_subtotal` decimal(12,4) DEFAULT NULL COMMENT 'Base Subtotal',
`discount_amount` decimal(12,4) DEFAULT NULL COMMENT 'Discount Amount',
`billing_address_id` int(11) DEFAULT NULL COMMENT 'Billing Address Id',
`is_used_for_refund` smallint(5) unsigned DEFAULT NULL COMMENT 'Is Used For Refund',
`order_id` int(10) unsigned NOT NULL COMMENT 'Order Id',
`email_sent` smallint(5) unsigned DEFAULT NULL COMMENT 'Email Sent',
`can_void_flag` smallint(5) unsigned DEFAULT NULL COMMENT 'Can Void Flag',
`state` int(11) DEFAULT NULL COMMENT 'State',
`shipping_address_id` int(11) DEFAULT NULL COMMENT 'Shipping Address Id',
`store_currency_code` varchar(3) DEFAULT NULL COMMENT 'Store Currency Code',
`transaction_id` varchar(255) DEFAULT NULL COMMENT 'Transaction Id',
`order_currency_code` varchar(3) DEFAULT NULL COMMENT 'Order Currency Code',
`base_currency_code` varchar(3) DEFAULT NULL COMMENT 'Base Currency Code',
`global_currency_code` varchar(3) DEFAULT NULL COMMENT 'Global Currency Code',
`increment_id` varchar(50) DEFAULT NULL COMMENT 'Increment Id',
`created_at` timestamp NULL DEFAULT NULL COMMENT 'Created At',
`updated_at` timestamp NULL DEFAULT NULL COMMENT 'Updated At',
`hidden_tax_amount` decimal(12,4) DEFAULT NULL COMMENT 'Hidden Tax Amount',
`base_hidden_tax_amount` decimal(12,4) DEFAULT NULL COMMENT 'Base Hidden Tax Amount',
`shipping_hidden_tax_amount` decimal(12,4) DEFAULT NULL COMMENT 'Shipping Hidden Tax Amount',
`base_shipping_hidden_tax_amnt` decimal(12,4) DEFAULT NULL COMMENT 'Base Shipping Hidden Tax Amount',
`shipping_incl_tax` decimal(12,4) DEFAULT NULL COMMENT 'Shipping Incl Tax',
`base_shipping_incl_tax` decimal(12,4) DEFAULT NULL COMMENT 'Base Shipping Incl Tax',
`base_total_refunded` decimal(12,4) DEFAULT NULL COMMENT 'Base Total Refunded',
`discount_description` varchar(255) DEFAULT NULL COMMENT 'Discount Description',
PRIMARY KEY (`entity_id`),
UNIQUE KEY `UNQ_SALES_FLAT_INVOICE_INCREMENT_ID` (`increment_id`),
KEY `IDX_SALES_FLAT_INVOICE_STORE_ID` (`store_id`),
KEY `IDX_SALES_FLAT_INVOICE_GRAND_TOTAL` (`grand_total`),
KEY `IDX_SALES_FLAT_INVOICE_ORDER_ID` (`order_id`),
KEY `IDX_SALES_FLAT_INVOICE_STATE` (`state`),
KEY `IDX_SALES_FLAT_INVOICE_CREATED_AT` (`created_at`),
CONSTRAINT `FK_SALES_FLAT_INVOICE_ORDER_ID_SALES_FLAT_ORDER_ENTITY_ID` FOREIGN KEY (`order_id`) REFERENCES `sales_flat_order` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `FK_SALES_FLAT_INVOICE_STORE_ID_CORE_STORE_STORE_ID` FOREIGN KEY (`store_id`) REFERENCES `core_store` (`store_id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=457378 DEFAULT CHARSET=utf8 COMMENT='Sales Flat Invoice'


Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
sales_flat_invoice 0 PRIMARY 1 entity_id A 364850 BTREE
sales_flat_invoice 0 UNQ_SALES_FLAT_INVOICE_INCREMENT_ID 1 increment_id A 364850 YES BTREE
sales_flat_invoice 1 IDX_SALES_FLAT_INVOICE_STORE_ID 1 store_id A 2 YES BTREE
sales_flat_invoice 1 IDX_SALES_FLAT_INVOICE_GRAND_TOTAL 1 grand_total A 121616 YES BTREE
sales_flat_invoice 1 IDX_SALES_FLAT_INVOICE_ORDER_ID 1 order_id A 364850 BTREE
sales_flat_invoice 1 IDX_SALES_FLAT_INVOICE_STATE 1 state A 4 YES BTREE
sales_flat_invoice 1 IDX_SALES_FLAT_INVOICE_CREATED_AT 1 created_at A 364850 YES BTREE

----------------------------------------------------------------------------------------------------------------------------------------------------------------------


CREATE TABLE `sales_flat_shipment` (
`entity_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Entity Id',
`store_id` smallint(5) unsigned DEFAULT NULL COMMENT 'Store Id',
`total_weight` decimal(12,4) DEFAULT NULL COMMENT 'Total Weight',
`total_qty` decimal(12,4) DEFAULT NULL COMMENT 'Total Qty',
`email_sent` smallint(5) unsigned DEFAULT NULL COMMENT 'Email Sent',
`order_id` int(10) unsigned NOT NULL COMMENT 'Order Id',
`customer_id` int(11) DEFAULT NULL COMMENT 'Customer Id',
`shipping_address_id` int(11) DEFAULT NULL COMMENT 'Shipping Address Id',
`billing_address_id` int(11) DEFAULT NULL COMMENT 'Billing Address Id',
`shipment_status` int(11) DEFAULT NULL COMMENT 'Shipment Status',
`increment_id` varchar(50) DEFAULT NULL COMMENT 'Increment Id',
`created_at` timestamp NULL DEFAULT NULL COMMENT 'Created At',
`updated_at` timestamp NULL DEFAULT NULL COMMENT 'Updated At',
`packages` text COMMENT 'Packed Products in Packages',
`shipping_label` mediumblob COMMENT 'Shipping Label Content',
`dispatched_on` datetime DEFAULT NULL,
`delivered_on` datetime DEFAULT NULL,
`wms_shipment_status` varchar(60) DEFAULT NULL,
`shipment_code` varchar(50) DEFAULT NULL COMMENT 'Wms Shipment Code',
PRIMARY KEY (`entity_id`),
UNIQUE KEY `UNQ_SALES_FLAT_SHIPMENT_INCREMENT_ID` (`increment_id`),
KEY `IDX_SALES_FLAT_SHIPMENT_STORE_ID` (`store_id`),
KEY `IDX_SALES_FLAT_SHIPMENT_TOTAL_QTY` (`total_qty`),
KEY `IDX_SALES_FLAT_SHIPMENT_ORDER_ID` (`order_id`),
KEY `IDX_SALES_FLAT_SHIPMENT_CREATED_AT` (`created_at`),
KEY `IDX_SALES_FLAT_SHIPMENT_UPDATED_AT` (`updated_at`),
KEY `IDX_SALES_FLAT_SHIPMENT_SHIPMENT_CODE` (`shipment_code`),
CONSTRAINT `FK_SALES_FLAT_SHIPMENT_ORDER_ID_SALES_FLAT_ORDER_ENTITY_ID` FOREIGN KEY (`order_id`) REFERENCES `sales_flat_order` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `FK_SALES_FLAT_SHIPMENT_STORE_ID_CORE_STORE_STORE_ID` FOREIGN KEY (`store_id`) REFERENCES `core_store` (`store_id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=407998 DEFAULT CHARSET=utf8 COMMENT='Sales Flat Shipment'


Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
sales_flat_shipment 0 PRIMARY 1 entity_id A 340010 BTREE
sales_flat_shipment 0 UNQ_SALES_FLAT_SHIPMENT_INCREMENT_ID 1 increment_id A 340010 YES BTREE
sales_flat_shipment 1 IDX_SALES_FLAT_SHIPMENT_STORE_ID 1 store_id A 2 YES BTREE
sales_flat_shipment 1 IDX_SALES_FLAT_SHIPMENT_TOTAL_QTY 1 total_qty A 228 YES BTREE
sales_flat_shipment 1 IDX_SALES_FLAT_SHIPMENT_ORDER_ID 1 order_id A 340010 BTREE
sales_flat_shipment 1 IDX_SALES_FLAT_SHIPMENT_CREATED_AT 1 created_at A 340010 YES BTREE
sales_flat_shipment 1 IDX_SALES_FLAT_SHIPMENT_UPDATED_AT 1 updated_at A 340010 YES BTREE
sales_flat_shipment 1 IDX_SALES_FLAT_SHIPMENT_SHIPMENT_CODE 1 shipment_code A 340010 YES BTREE

-------------------------------------------------------------------------------------------------------------------------------------------------------------------

Options: ReplyQuote


Subject
Views
Written By
Posted
1908
January 18, 2016 04:49AM
794
January 22, 2016 06:29PM
Re: query performance optimization
1160
January 22, 2016 06:18AM
727
January 22, 2016 01:08PM
754
January 22, 2016 11:10PM


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.