MySQL get the current request information
Wednesday, November 23rd, 2011To trouble shoot an overly lengthy SQL one day, I had to use this command to see what query was taken ( literally ) forever to complete.
Log into mysql via command line and run this command while the query is running
mysql > SHOW FULL PROCESSLIST\G ( hit enter )
Here is an example of the potential output:
*************************** 1. row ***************************
Id: 14
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: NULL
Info: SHOW FULL PROCESSLIST
*************************** 2. row ***************************
Id: 16
User: local_bloom2
Host: localhost
db: local_bloom2
Command: Query
Time: 318
State: Sending data
Info: SELECT i.sku, sum(i.qty_ordered) quantity
FROM sales_flat_order_item i,
sales_flat_order o,
catalog_category_product c,
cataloginventory_stock_item s,
catalog_category_entity_varchar v,
catalog_category_entity e,
catalog_product_entity_varchar pv1,
eav_attribute_option_value ev1,
catalog_product_entity_varchar pv2,
catalog_product_entity_int pi
WHERE o.created_at >= adddate(CURDATE(),-14)
AND o.entity_id = i.order_id
AND i.product_id = s.product_id
AND s.is_in_stock = 1
AND i.product_id = c.product_id
AND c.category_id = e.entity_id
AND e.entity_id = v.entity_id
AND v.attribute_id = 33
AND v.value = 'Bath & Body'
AND i.product_id = pv1.entity_id
AND pv1.attribute_id = 641
AND (pv1.value = ev1.option_id or pv1.value like concat(ev1.option_id,',%') or pv1.value like concat('%,', ev1.option_id) or pv1.value like concat('%,',ev1.option_id,',%'))
AND (ev1.value = '35-54' or ev1.option_id = 85 or ev1.option_id = 90)
AND i.product_id = pv2.entity_id
AND ( pv2.attribute_id = 546
AND ( pv2.value = ''
or (exists
(select null
from eav_attribute_option_value ev2
where (
( pv2.value = ev2.option_id
or pv2.value like concat(ev2.option_id,',%')
or pv2.value like concat('%,', ev2.option_id)
or pv2.value like concat('%,',ev2.option_id,',%')
)
AND
('Normal/Combination' like concat('%',ev2.value,'%') or ev2.option_id = 165 or ev2.option_id = 487)
)
)
or not exists
(select null
from catalog_product_entity_varchar
where attribute_id = pv2.attribute_id
and entity_id = i.product_id)
)))
group by sku
order by 2 desc limit 5
2 rows in set (0.00 sec)