查看mysql挂起情况,为什么此MySQL查询挂起？

SELECT *

FROM tbl_order_head AS o

INNER JOIN tbl_orders_log AS c

ON o.PAYMENT_TRANSACTION_LOG_ID=c.TRANSACTION_ID

WHERE o.VISUAL_ID = '77783';

tbl_order_head 67,000 (30 fields) records, tbl_orders_log 17000 (5 fields) records. I don't know if it would eventually return as I am running it on a live server and fear overloading.

I am doing similar queries and much more complex queries successfully.

tbl_orders_log

Field Type Comment

ID bigint(20) NOT NULL

TRANSACTION_ID varchar(1000) NULL

CREATED datetime NULL

AMENDED datetime NULL

PAYMENT_CARD_NUMBER varchar(255) NULL

PAYMENT_CARD_TYPE varchar(255) NULL

SESSION_ID varchar(255) NULL

TRANSACTION_TYPE varchar(255) NULL

TRANSACTION_VALUE varchar(255) NULL

LOG_DATA text NULL

Index Information

Indexes Columns Index_Type

PRIMARY ID Unique

tbl_order_head

CREATE TABLE `tbl_order_head` (

`ID` varchar(255) NOT NULL,

`VISUAL_ID` decimal(20,0) DEFAULT NULL,

`CREATED` datetime DEFAULT NULL,

`AMENDED` datetime DEFAULT NULL,

`CUSTOMER_ID` varchar(255) DEFAULT NULL,

`BILLING_ID` varchar(255) DEFAULT NULL,

`ORDER_LINES_ITEM_VALUE` varchar(20) DEFAULT NULL,

`DELIVERY_VALUE` varchar(20) DEFAULT NULL,

`ORDER_LINES_ITEM_TAX` varchar(20) DEFAULT NULL,

`DELIVERY_TAX` varchar(20) DEFAULT NULL,

`DELIVERY_ALLOCATED_ITEMS_VALUE` varchar(20) DEFAULT NULL,

`DELIVERY_ALLOCATED_ITEMS_TAX` varchar(20) DEFAULT NULL,

`DELIVERY_ALLOCATED_ITEMS_TAX_DEDUCTION` varchar(20) DEFAULT NULL,

`DELIVERY_TAX_DEDUCTION` varchar(20) DEFAULT NULL,

`LOYALTY_CARD_POINTS_EARNED` varchar(10) DEFAULT NULL,

`LOYALTY_CARD_POINTS_REDEEMED` varchar(10) DEFAULT NULL,

`LOYALTY_CARD_POINTS_REDEEMED_VALUE` varchar(20) DEFAULT NULL,

`VOUCHER_CODE` varchar(50) DEFAULT NULL,

`AFFILIATE_CODE` varchar(50) DEFAULT NULL,

`LOYALTY_CARD_NUMBER` varchar(209) DEFAULT NULL,

`REDEEM_LOYALTY_CARD_POINTS` varchar(1) DEFAULT NULL,

`SOURCE` varchar(50) DEFAULT NULL,

`SKU_DATA` text,

`SKU_TAX_DATA` text,

`DISCOUNT_DATA` text,

`PAYMENT_CARD_TYPE` varchar(6) DEFAULT NULL,

`PAYMENT_CARD_NUMBER` varchar(255) DEFAULT NULL,

`PAYMENT_CARD_START_MONTH` varchar(6) DEFAULT NULL,

`PAYMENT_CARD_EXPIRY_MONTH` varchar(6) DEFAULT NULL,

`PAYMENT_CARD_START_YEAR` varchar(6) DEFAULT NULL,

`PAYMENT_CARD_EXPIRY_YEAR` varchar(6) DEFAULT NULL,

`PAYMENT_CARD_ISSUE_NUMBER` varchar(3) DEFAULT NULL,

`PAYMENT_CARD_SECURITY_NUMBER` varchar(4) DEFAULT NULL,

`PAYMENT_CARD_NAME` varchar(50) DEFAULT NULL,

`PAYMENT_CARD_SAVE` varchar(1) DEFAULT NULL,

`PAYMENT_CARD_CHARGE_AMOUNT` varchar(10) DEFAULT NULL,

`SAVED_PAYMENT_CARD_ID` varchar(255) DEFAULT NULL,

`SAVED_PAYMENT_CARD_SECURITY_NUMBER` varchar(255) DEFAULT NULL,

`GIFT_VOUCHER_DATA` text,

`GIFT_VOUCHER_APPLIED_VALUE` varchar(10) DEFAULT NULL,

`LOYALTY_EARNED_SKU_DATA` text,

`LOYALTY_REDEMPTION_SKU_DATA` text,

`LOYALTY_REDEMPTION_DEDUCTED_SKU_DATA` text,

`PAYMENT_CARD_AUTH_CODE` varchar(10) DEFAULT NULL,

`PAYMENT_TRANSACTION_LOG_ID` text,

`CREATED_BY` varchar(20) DEFAULT NULL,

`BASKET_ID` varchar(255) DEFAULT NULL,

`SKU_DESCRIPTION_XREF` text,

`IP_TRANSACTION_NUMBER` varchar(255) DEFAULT NULL,

`MEMS_BESPOKE_DISCOUNT_DATA` text,

`IP_EXPORTED` varchar(255) DEFAULT NULL,

PRIMARY KEY (`ID`),

KEY `CUSTOMER_ID` (`CUSTOMER_ID`,`CREATED`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

Hi I have (with help)

rewritten the query

SELECT * FROM tbl_orders_log WHERE TRANSACTION_ID=(SELECT o.PAYMENT_TRANSACTION_LOG_ID FROM tbl_order_head AS o WHERE o.VISUAL_ID = '77783');

which executes instantly

解决方案

Because someone has locked one of the tables or a single row. This can happen, for example, if you have disabled auto commit (so you can rollback your modifications) in a session and forgot to commit there.

[EDIT] After you posted the tables definitions, you can see that the types of the two join columns are different. Now the question is: Which type will be up-/down-casted when you run the query? In your case, it might be better to cast the type of PAYMENT_TRANSACTION_LOG_ID to varchar, especially if you have an index on TRANSACTION_ID (which you should create for this query).

That way, a few rows (or even a single one) from the table tbl_order_head will be selected and then, a quick lookup happens in the table tbl_orders_log. Without this, the database will load all records from the log table and check each record for a match in the found order headers (plus casting every ID to the type in the header, etc).