Posting in the Magento forums has been disabled pending the implementation of a new and improved forum solution which should better serve the community.

For new questions please post at magento.stackexchange.com, the community-run support site for the Magento community. We will be providing updates on the new forum solution soon. For questions or concerns please email community@magento.com.

Magento Forum

Querying the database for billing AND shipping address on an order
 
snucky
Jr. Member
 
Total Posts:  5
Joined:  2009-10-11
 

Hi there! I am stuck with this one database query in that it consumes ALL of the CPU power and takes forever to execute. It is almost funny. Here is the query that causes problems in full and then I’ll have a few more remarks:

SELECT `order_items`.*, `e`.`grand_total`, `e`.`shipping_amount`, `e`.`increment_id`, `_table_firstname`.`value` AS `firstname`, `_table_lastname`.`value` AS `lastname`, CONCAT(_table_firstname.value," "_table_lastname.value) AS `custname`, `_table_email`.`email`, `_table_shipping_address_id`.`value` AS `shipping_address_id`
, `
_table_street`.`value` AS `street`, `_table_city`.`value` AS `city`, `_table_state`.`value` AS `state`, `_table_zipcode`.`value` AS `zipcode`, `_table_phone`.`value` AS `phone`
, `
_table_billing_address_id`.`value` AS `billing_address_id`, `_table_b_street`.`value` AS `b_street`, `_table_b_city`.`value` AS `b_city`, `_table_b_state`.`value` AS `b_state`, `_table_b_zipcode`.`value` AS `b_zipcode`
, `
_table_shipping`.`value` AS `shippingFROM `sales_flat_order_item` AS `order_items`
 
INNER JOIN `sales_order` AS `eON e.entity_id order_items.order_id AND `e`.created_at BETWEEN '2009-04-19 08:00:00' AND '2009-10-19 07:59:59'
 
INNER JOIN `customer_entity_varchar` AS `_table_firstnameON (_table_firstname.entity_id e.customer_id) AND (_table_firstname.attribute_id='1')
 
INNER JOIN `customer_entity_varchar` AS `_table_lastnameON (_table_lastname.entity_id e.customer_id) AND (_table_lastname.attribute_id='2')
 
INNER JOIN `customer_entity` AS `_table_emailON (_table_email.entity_id e.customer_id)

 
INNER JOIN `sales_order_int` AS `_table_shipping_address_idON (_table_shipping_address_id.entity_id e.entity_id) AND (_table_shipping_address_id.attribute_id='216')
 
INNER JOIN `sales_order_entity_varchar` AS `_table_streetON (_table_street.entity_id _table_shipping_address_id.value) AND (_table_street.attribute_id='222')
 
INNER JOIN `sales_order_entity_varchar` AS `_table_cityON (_table_city.entity_id _table_shipping_address_id.value) AND (_table_city.attribute_id='227')
 
INNER JOIN `sales_order_entity_varchar` AS `_table_stateON (_table_state.entity_id _table_shipping_address_id.value) AND (_table_state.attribute_id='228')
 
INNER JOIN `sales_order_entity_varchar` AS `_table_zipcodeON (_table_zipcode.entity_id _table_shipping_address_id.value) AND (_table_zipcode.attribute_id='229')
 
INNER JOIN `sales_order_entity_varchar` AS `_table_phoneON (_table_phone.entity_id _table_shipping_address_id.value) AND (_table_phone.attribute_id='230')

 
INNER JOIN `sales_order_int` AS `_table_billing_address_idON (_table_billing_address_id.entity_id e.entity_id) AND (_table_billing_address_id.attribute_id='217')
 
INNER JOIN `sales_order_entity_varchar` AS `_table_b_streetON (_table_b_street.entity_id _table_billing_address_id.value) AND (_table_b_street.attribute_id='222')
 
INNER JOIN `sales_order_entity_varchar` AS `_table_b_cityON (_table_b_city.entity_id _table_billing_address_id.value) AND (_table_b_city.attribute_id='227')
 
INNER JOIN `sales_order_entity_varchar` AS `_table_b_stateON (_table_b_state.entity_id _table_billing_address_id.value) AND (_table_b_state.attribute_id='228')
 
INNER JOIN `sales_order_entity_varchar` AS `_table_b_zipcodeON (_table_b_zipcode.entity_id _table_billing_address_id.value) AND (_table_b_zipcode.attribute_id='229')

 
INNER JOIN `sales_order_varchar` AS `_table_shippingON (_table_shipping.entity_id e.entity_id) AND (_table_shipping.attribute_id='208');

The billing and shipping address both need to inner-join to sales_order_int (with attributes 216 and 217). If I am to join to the table only once the query runs just fine !! I commented out the following:

SELECT
..
#, `_table_billing_address_id`.`value` AS `billing_address_id`, `_table_b_street`.`value` AS `b_street`, `_table_b_city`.`value` AS `b_city`, `_table_b_state`.`value` AS `b_state`, `_table_b_zipcode`.`value` AS `b_zipcode`
..
 
#INNER JOIN `sales_order_int` AS `_table_billing_address_id` ON (_table_billing_address_id.entity_id = e.entity_id) AND (_table_billing_address_id.attribute_id='217')
 #INNER JOIN `sales_order_entity_varchar` AS `_table_b_street` ON (_table_b_street.entity_id = _table_billing_address_id.value) AND (_table_b_street.attribute_id='222')
 #INNER JOIN `sales_order_entity_varchar` AS `_table_b_city` ON (_table_b_city.entity_id = _table_billing_address_id.value) AND (_table_b_city.attribute_id='227')
 #INNER JOIN `sales_order_entity_varchar` AS `_table_b_state` ON (_table_b_state.entity_id = _table_billing_address_id.value) AND (_table_b_state.attribute_id='228')
 #INNER JOIN `sales_order_entity_varchar` AS `_table_b_zipcode` ON (_table_b_zipcode.entity_id = _table_billing_address_id.value) AND (_table_b_zipcode.attribute_id='229')

and it runs fine! It’s not that the joins to the billing address info were wrong - if I comment out the analogous part for the shipping address, the billing address will show just fine.

Anybody have any suggestions of how to run this query better? By the way, I AM using the joinAttributes method to join the EAV tables. That’s how I got the query.

Thanks a lot in advance!

 
Magento Community Magento Community
Magento Community
Magento Community
 
snucky
Jr. Member
 
Total Posts:  5
Joined:  2009-10-11
 

By the way, I created a new index on the two columns that are needed in the join to sales_order_int

ALTER TABLE `sales_order_intADD INDEX `report_200910` ( `entity_id` , `attribute_id` ) ;

and it uses that new Index in the query now. Here’s the query explained:

+----+-------------+----------------------------+--------+-----------------------------------------------------------------------+----------------------------------+---------+----------------------------------------------------------+------+--------------------------+
id select_type table                      type   possible_keys                                                         key                              key_len ref                                                      rows Extra                    |
+----+-------------+----------------------------+--------+-----------------------------------------------------------------------+----------------------------------+---------+----------------------------------------------------------+------+--------------------------+
|  
SIMPLE      _table_shipping            ref    FK_sales_order_varchar_attribute,FK_sales_order_varchar               FK_sales_order_varchar_attribute 2       | const                                                    |   48 |                          | 
|  
SIMPLE      e                          eq_ref PRIMARY,IDX_CUSTOMER                                                  PRIMARY                          4       aptalent_ecom_demo1._table_shipping.entity_id            |    Using where              
|  
SIMPLE      _table_shipping_address_id ref    FK_sales_order_int_attribute,FK_sales_order_int,report_200910         report_200910                    6       aptalent_ecom_demo1.e.entity_id,const                    |    Using where              
|  
SIMPLE      order_items                ref    IDX_ORDER                                                             IDX_ORDER                        4       aptalent_ecom_demo1._table_shipping_address_id.entity_id |    Using where              
|  
SIMPLE      _table_firstname           ref    FK_CUSTOMER_VARCHAR_ATTRIBUTE,FK_CUSTOMER_VARCHAR_ENTITY,IDX_VALUE    IDX_VALUE                        6       aptalent_ecom_demo1.e.customer_id,const                  |    Using whereUsing index 
|  
SIMPLE      _table_lastname            ref    FK_CUSTOMER_VARCHAR_ATTRIBUTE,FK_CUSTOMER_VARCHAR_ENTITY,IDX_VALUE    IDX_VALUE                        6       aptalent_ecom_demo1.e.customer_id,const                  |    Using whereUsing index 
|  
SIMPLE      _table_email               eq_ref PRIMARY                                                               PRIMARY                          4       aptalent_ecom_demo1.e.customer_id                        |    Using where              
|  
SIMPLE      _table_street              ref    FK_sales_order_entity_varchar_attribute,FK_sales_order_entity_varchar FK_sales_order_entity_varchar    4       aptalent_ecom_demo1._table_shipping_address_id.value     |   13 Using where              
|  
SIMPLE      _table_city                ref    FK_sales_order_entity_varchar_attribute,FK_sales_order_entity_varchar FK_sales_order_entity_varchar    4       aptalent_ecom_demo1._table_shipping_address_id.value     |   13 Using where              
|  
SIMPLE      _table_state               ref    FK_sales_order_entity_varchar_attribute,FK_sales_order_entity_varchar FK_sales_order_entity_varchar    4       aptalent_ecom_demo1._table_shipping_address_id.value     |   13 Using where              
|  
SIMPLE      _table_zipcode             ref    FK_sales_order_entity_varchar_attribute,FK_sales_order_entity_varchar FK_sales_order_entity_varchar    4       aptalent_ecom_demo1._table_shipping_address_id.value     |   13 Using where              
|  
SIMPLE      _table_phone               ref    FK_sales_order_entity_varchar_attribute,FK_sales_order_entity_varchar FK_sales_order_entity_varchar    4       aptalent_ecom_demo1._table_shipping_address_id.value     |   13 Using where              
+----+-------------+----------------------------+--------+-----------------------------------------------------------------------+----------------------------------+---------+----------------------------------------------------------+------+--------------------------+
 
Magento Community Magento Community
Magento Community
Magento Community
 
ipascual
Member
 
Avatar
Total Posts:  32
Joined:  2009-07-13
London
 

Hi! I got the same issue, try this two queries:

1) create a temporary table with all customer data

CREATE TEMPORARY TABLE customreports_customer_totals
SELECT order_id
prefixfirstnamelastnamecompanystreetpostcoderegioncountry_idtelephone
FROM sales_flat_order_item
INNER JOIN sales_flat_quote_item
ON sales_flat_order_item
.quote_item_id sales_flat_quote_item.item_id
INNER JOIN sales_flat_quote_address
ON sales_flat_quote_item
.quote_id sales_flat_quote_address.quote_id
INNER JOIN sales_order ON sales_flat_order_item
.order_id sales_order.entity_id
WHERE  sales_flat_quote_address
.address_type 'billing'
AND sales_flat_order_item.product_type <> 'configurable'
AND sales_order.created_at >= '2009-11-07 23:00:00'
AND sales_order.created_at <= '2009-11-09 22:59:59'    
GROUP BY order_id;

2) use the temporary table, for example: calculate totals joining the customer data

SELECT `e` . * ,`q` . * , `_table_state`.`value` AS `state` , COUNTe.entity_id ) AS `orders_count`
FROM `sales_order` AS `e`
INNER JOIN `sales_order_varchar` AS `_table_stateON _table_state.entity_id e.entity_id )
INNER JOIN customreports_customer_totals AS `qON (
e.entity_id q.order_id
)
WHERE (e.entity_type_id '11')
AND (
_table_state.value != 'canceled')
AND (
e.created_at >= '2009-11-7 23:00:00' AND e.created_at <= '2009-11-9 22:59:59')
GROUP BY `e`.`customer_id`
ORDER BY e.entity_id;

All the best!
Nacho

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top