Query optimization needed

[Devrishi Shandilya]

Hi Friends,

I have a query, which is taking too much time (15 minutes) to execute. Need help to optimize this.



SELECT sfo.entity_id AS order_id, CASE WHEN LOCATE('delivered', GROUP_CONCAT(sfo_1.status)) THEN 'repeat-a' WHEN GROUP_CONCAT(sfo_1.status) <> ''
THEN 'repeat' END AS `repeat`, IF(LOCATE('-R', sfo.increment_id), 'alternate', NULL) AS alternate, sfq.orig_order_id, sfo.purchase_from, sfo.customer_email,
sfo.status, sfoa.telephone, sfo.relation_parent_id, sfo.increment_id, sfoi.utm_source FROM sales_flat_order sfo
INNER JOIN sales_flat_order_address sfoa ON sfo.entity_id=sfoa.parent_id
LEFT JOIN sales_flat_order_address sfoa_1 ON
(sfoa.email=sfoa_1.email OR sfoa.telephone=sfoa_1.telephone) AND sfoa_1.parent_id < sfo.entity_id AND sfoa_1.address_type = 'billing'
LEFT JOIN sales_flat_quote sfq ON sfo.quote_id=sfq.entity_id LEFT JOIN sales_flat_order AS sfo_1 ON sfoa_1.parent_id = sfo_1.entity_id
LEFT JOIN sales_flat_order_info AS sfoi ON sfo.entity_id = sfoi.order_id WHERE sfoa.address_type='billing' AND sfo.entity_id IN
(439461,439462,439463,439464,439465,439466,439467,439468,439469,439470,439471,439472,439473,439474,439475,439476,439477,439478,439479,
439480,439481,439482,439483,439484,439485,439486,439489,439492) GROUP BY sfo.entity_id

+----+-------------+--------+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------+---------+--------------------------+--------+------------------------------------------------+
| id | select_type | table  | type   | possible_keys                                                                                                                                                                                                                                                                                   | key                                    | key_len | ref                      | rows   | Extra                                          |
+----+-------------+--------+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------+---------+--------------------------+--------+------------------------------------------------+
|  1 | SIMPLE      | sfo    | range  | PRIMARY,UNQ_SALES_FLAT_ORDER_INCREMENT_ID,IDX_SALES_FLAT_ORDER_STATUS,IDX_SALES_FLAT_ORDER_STATE,IDX_SALES_FLAT_ORDER_STORE_ID,IDX_SALES_FLAT_ORDER_CREATED_AT,IDX_SALES_FLAT_ORDER_CUSTOMER_ID,IDX_SALES_FLAT_ORDER_EXT_ORDER_ID,IDX_SALES_FLAT_ORDER_QUOTE_ID,IDX_SALES_FLAT_ORDER_UPDATED_AT | PRIMARY                                | 4       | NULL                     |     28 | Using where                                    |
|  1 | SIMPLE      | sfoa   | ref    | IDX_SALES_FLAT_ORDER_ADDRESS_PARENT_ID                                                                                                                                                                                                                                                          | IDX_SALES_FLAT_ORDER_ADDRESS_PARENT_ID | 5       | magento.sfo.entity_id    |      1 | Using where                                    |
|  1 | SIMPLE      | sfoa_1 | ALL    | IDX_SALES_FLAT_ORDER_ADDRESS_PARENT_ID                                                                                                                                                                                                                                                          | NULL                                   | NULL    | NULL                     | 861502 | Range checked for each record (index map: 0x2) |
|  1 | SIMPLE      | sfq    | eq_ref | PRIMARY                                                                                                                                                                                                                                                                                         | PRIMARY                                | 4       | magento.sfo.quote_id     |      1 | Using where                                    |
|  1 | SIMPLE      | sfo_1  | eq_ref | PRIMARY                                                                                                                                                                                                                                                                                         | PRIMARY                                | 4       | magento.sfoa_1.parent_id |      1 | NULL                                           |
|  1 | SIMPLE      | sfoi   | ALL    | PRIMARY                                                                                                                                                                                                                                                                                         | NULL                                   | NULL    | NULL                     | 180518 | Range checked for each record (index map: 0x1) |
+----+-------------+--------+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------+---------+--------------------------+--------+------------------------------------------------+

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=443849 DEFAULT CHARSET=utf8 COMMENT='Sales Flat Order'






CREATE TABLE `sales_flat_order_address` (
`entity_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Entity Id',
`parent_id` int(10) unsigned DEFAULT NULL COMMENT 'Parent Id',
`customer_address_id` int(11) DEFAULT NULL COMMENT 'Customer Address Id',
`quote_address_id` int(11) DEFAULT NULL COMMENT 'Quote Address Id',
`region_id` int(11) DEFAULT NULL COMMENT 'Region Id',
`customer_id` int(11) DEFAULT NULL COMMENT 'Customer Id',
`fax` varchar(255) DEFAULT NULL COMMENT 'Fax',
`region` varchar(255) DEFAULT NULL COMMENT 'Region',
`postcode` varchar(255) DEFAULT NULL COMMENT 'Postcode',
`lastname` varchar(255) DEFAULT NULL COMMENT 'Lastname',
`street` varchar(255) DEFAULT NULL COMMENT 'Street',
`city` varchar(255) DEFAULT NULL COMMENT 'City',
`email` varchar(255) DEFAULT NULL COMMENT 'Email',
`telephone` varchar(255) DEFAULT NULL COMMENT 'Telephone',
`country_id` varchar(2) DEFAULT NULL COMMENT 'Country Id',
`firstname` varchar(255) DEFAULT NULL COMMENT 'Firstname',
`address_type` varchar(255) DEFAULT NULL COMMENT 'Address Type',
`prefix` varchar(255) DEFAULT NULL COMMENT 'Prefix',
`middlename` varchar(255) DEFAULT NULL COMMENT 'Middlename',
`suffix` varchar(255) DEFAULT NULL COMMENT 'Suffix',
`company` varchar(255) DEFAULT NULL COMMENT 'Company',
`vat_id` text COMMENT 'Vat Id',
`vat_is_valid` smallint(6) DEFAULT NULL COMMENT 'Vat Is Valid',
`vat_request_id` text COMMENT 'Vat Request Id',
`vat_request_date` text COMMENT 'Vat Request Date',
`vat_request_success` smallint(6) DEFAULT NULL COMMENT 'Vat Request Success',
PRIMARY KEY (`entity_id`),
KEY `IDX_SALES_FLAT_ORDER_ADDRESS_PARENT_ID` (`parent_id`),
CONSTRAINT `FK_SALES_FLAT_ORDER_ADDRESS_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=875397 DEFAULT CHARSET=utf8 COMMENT='Sales Flat Order Address'




CREATE TABLE `sales_flat_quote` (
`entity_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Entity Id',
`store_id` smallint(5) unsigned NOT NULL DEFAULT '0' 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',
`converted_at` timestamp NULL DEFAULT NULL COMMENT 'Converted At',
`is_active` smallint(5) unsigned DEFAULT '1' COMMENT 'Is Active',
`is_virtual` smallint(5) unsigned DEFAULT '0' COMMENT 'Is Virtual',
`is_multi_shipping` smallint(5) unsigned DEFAULT '0' COMMENT 'Is Multi Shipping',
`items_count` int(10) unsigned DEFAULT '0' COMMENT 'Items Count',
`items_qty` decimal(12,4) DEFAULT '0.0000' COMMENT 'Items Qty',
`orig_order_id` int(10) unsigned DEFAULT '0' COMMENT 'Orig Order Id',
`store_to_base_rate` decimal(12,4) DEFAULT '0.0000' COMMENT 'Store To Base Rate',
`store_to_quote_rate` decimal(12,4) DEFAULT '0.0000' COMMENT 'Store To Quote Rate',
`base_currency_code` varchar(255) DEFAULT NULL COMMENT 'Base Currency Code',
`store_currency_code` varchar(255) DEFAULT NULL COMMENT 'Store Currency Code',
`quote_currency_code` varchar(255) DEFAULT NULL COMMENT 'Quote Currency Code',
`grand_total` decimal(12,4) DEFAULT '0.0000' COMMENT 'Grand Total',
`base_grand_total` decimal(12,4) DEFAULT '0.0000' COMMENT 'Base Grand Total',
`checkout_method` varchar(255) DEFAULT NULL COMMENT 'Checkout Method',
`customer_id` int(10) unsigned DEFAULT '0' COMMENT 'Customer Id',
`customer_tax_class_id` int(10) unsigned DEFAULT '0' COMMENT 'Customer Tax Class Id',
`customer_group_id` int(10) unsigned DEFAULT '0' COMMENT 'Customer Group Id',
`customer_email` varchar(255) DEFAULT NULL COMMENT 'Customer Email',
`customer_prefix` varchar(40) DEFAULT NULL COMMENT 'Customer Prefix',
`customer_firstname` varchar(255) DEFAULT NULL COMMENT 'Customer Firstname',
`customer_middlename` varchar(40) DEFAULT NULL COMMENT 'Customer Middlename',
`customer_lastname` varchar(255) DEFAULT NULL COMMENT 'Customer Lastname',
`customer_suffix` varchar(40) DEFAULT NULL COMMENT 'Customer Suffix',
`customer_dob` datetime DEFAULT NULL COMMENT 'Customer Dob',
`customer_note` varchar(255) DEFAULT NULL COMMENT 'Customer Note',
`customer_note_notify` smallint(5) unsigned DEFAULT '1' COMMENT 'Customer Note Notify',
`customer_is_guest` smallint(5) unsigned DEFAULT '0' COMMENT 'Customer Is Guest',
`remote_ip` varchar(32) DEFAULT NULL COMMENT 'Remote Ip',
`applied_rule_ids` varchar(255) DEFAULT NULL COMMENT 'Applied Rule Ids',
`reserved_order_id` varchar(64) DEFAULT NULL COMMENT 'Reserved Order Id',
`password_hash` varchar(255) DEFAULT NULL COMMENT 'Password Hash',
`coupon_code` varchar(255) DEFAULT NULL COMMENT 'Coupon Code',
`global_currency_code` varchar(255) DEFAULT NULL COMMENT 'Global Currency Code',
`base_to_global_rate` decimal(12,4) DEFAULT NULL COMMENT 'Base To Global Rate',
`base_to_quote_rate` decimal(12,4) DEFAULT NULL COMMENT 'Base To Quote Rate',
`customer_taxvat` varchar(255) DEFAULT NULL COMMENT 'Customer Taxvat',
`customer_gender` varchar(255) DEFAULT NULL COMMENT 'Customer Gender',
`subtotal` decimal(12,4) DEFAULT NULL COMMENT 'Subtotal',
`base_subtotal` decimal(12,4) DEFAULT NULL COMMENT 'Base Subtotal',
`subtotal_with_discount` decimal(12,4) DEFAULT NULL COMMENT 'Subtotal With Discount',
`base_subtotal_with_discount` decimal(12,4) DEFAULT NULL COMMENT 'Base Subtotal With Discount',
`is_changed` int(10) unsigned DEFAULT NULL COMMENT 'Is Changed',
`trigger_recollect` smallint(6) NOT NULL DEFAULT '0' COMMENT 'Trigger Recollect',
`ext_shipping_info` text COMMENT 'Ext Shipping Info',
`gift_message_id` int(11) DEFAULT NULL COMMENT 'Gift Message Id',
`is_persistent` smallint(5) unsigned DEFAULT '0' COMMENT 'Is Quote Persistent',
PRIMARY KEY (`entity_id`),
KEY `IDX_SALES_FLAT_QUOTE_CUSTOMER_ID_STORE_ID_IS_ACTIVE` (`customer_id`,`store_id`,`is_active`),
KEY `IDX_SALES_FLAT_QUOTE_STORE_ID` (`store_id`),
CONSTRAINT `FK_SALES_FLAT_QUOTE_STORE_ID_CORE_STORE_STORE_ID` FOREIGN KEY (`store_id`) REFERENCES `core_store` (`store_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1269083 DEFAULT CHARSET=utf8 COMMENT='Sales Flat Quote'




CREATE TABLE `sales_flat_order_info` (
`order_id` varchar(50) NOT NULL DEFAULT '',
`affiliate_code` varchar(255) DEFAULT NULL,
`utm_source` varchar(255) DEFAULT NULL,
`placed_from` int(10) DEFAULT NULL COMMENT 'Backend \nApp\nWeb',
`created_by` int(10) DEFAULT NULL COMMENT 'Edited - Order created by Customer Care\nCorporate - Order created by a specific Admin\nAlternate - Order created by backend script\nCusomer - Order created by normal customer',
`repeat_sequence` int(10) DEFAULT NULL COMMENT 'First - First Order of that customer\nRepeat - One order already placed by that customer\nRepat-A - One order already delivered to that customer',
`fresh_or_reorder` int(10) DEFAULT NULL COMMENT 'Reorder - By clicking on Reorder button from Admin\nFresh - Without clicking Reorder button by Admin / Customer',
`purchase_admin_id` int(10) DEFAULT NULL COMMENT 'Store NULL or admin_id that are designated as Corporate Admin (defined in core_config_data table)',
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`order_id`),
KEY `fk_placed_from_1_idx` (`placed_from`),
KEY `fk_created_by_1_idx` (`created_by`),
KEY `fk_repeat_sequence_1_idx` (`repeat_sequence`),
KEY `fk_fresh_or_reorder_1_idx` (`fresh_or_reorder`),
CONSTRAINT `fk_created_by_1` FOREIGN KEY (`created_by`) REFERENCES `sales_flat_order_tag_master` (`tag_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_fresh_or_reorder_1` FOREIGN KEY (`fresh_or_reorder`) REFERENCES `sales_flat_order_tag_master` (`tag_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_placed_from_1` FOREIGN KEY (`placed_from`) REFERENCES `sales_flat_order_tag_master` (`tag_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_repeat_sequence_1` FOREIGN KEY (`repeat_sequence`) REFERENCES `sales_flat_order_tag_master` (`tag_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1



Edited 1 time(s). Last edit at 11/05/2015 11:21PM by Peter Brawley.