One of my e-commerce clients has been experiencing extremely long delay in the credit card processing step of the checkout process, I’ve look into this and narrowed it down to the following line of code:

$paypalipn_query = tep_db_query(“select o.orders_status,p.* from ” . TABLE_ORDERS . ” o LEFT JOIN ” . TABLE_PAYPALIPN_TXN . ” p on p.item_number = o.orders_id AND o.customers_id = ‘” . (int)$customer_id . “‘ order by o.date_purchased desc limit 1”);

I’ve disabled PayPal IPN and commented out that line so checkouts are speedy and quick now. But I have no idea why this single line of mysql query takes about a minute and a half to return. I suspect the table is messed up, the orders table (TABLE_ORDERS) is probably okay coz the rest of the site uses that table a lot, so maybe the paypalipn_txn table is messed up, but I have no idea what to look for or how to fix it… It really isn’t very large… If you have an idea of what’s messed up, please leave a comment… 🙂

Technorati Tags: MySQL