Why does this MySQL Query hang?

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
TRA开发者_StackOverflow中文版NSACTION_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.

This document might help.

[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).

Have you run EXPLAIN on your query to see the query plan?

EXPLAIN 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'

Your join might result in millions/billions of rows being examined. Are your tables correctly indexed for this join?

I think it should be o.TRANSACTION_ID and c.PAYMENT_TRANSACTION_LOG_ID instead of c.TRANSACTION_ID and o.PAYMENT_TRANSACTION_LOG_ID. Then the query should look like this:

SELECT * FROM tbl_order_head o 
INNER JOIN tbl_orders_log c 
ON c.PAYMENT_TRANSACTION_LOG_ID=o.TRANSACTION_ID 
WHERE o.VISUAL_ID = '77783';