Question :
I have recently installed a new module(paypal express) in Oxid 6.0.x backend.
After installation, the backend menu point “orders”, in my backend, is timing out. Cause the query needs too long. I am not able to view orders (which is a CMS vendor resource).
I already tried to debug it with our managed hoster support who doubled our ram to increased mysql buffer size as well as php timouts etc. within the php.ini.
After the hoster php.ini change I still see the query with status “Copying to tmp table” even so the hoster has increased the query buffers by more then double
Question:
I am really not sure what the problem is. Does someone have an Idea what else I might try?
I cannot imagine that this one small join needs so much buffer space and time to excecute. Especially since the join to payppaypalpluspayment is the same as to oxv_oxpayments_de which is instant and on the same primary key.
This is the query I see running in processlist
Showing rows 0 – 0 (1 total, Query took 200.9683 seconds.) Has already decreased from about 10 min after the php.ini change
select count(*)
from `oxorder`
LEFT JOIN `oxv_oxpayments_de` AS `payments`
on `payments`.oxid=oxorder.oxpaymenttype
LEFT JOIN `oxv_oxpayments_de` AS pluspayments
ON pluspayments.oxid = oxorder.oxpaymenttype
LEFT JOIN payppaypalpluspayment
ON payppaypalpluspayment.OXORDERID = oxorder.OXID
LEFT JOIN payppaypalpluspui
ON payppaypalpluspui.OXPAYMENTID = payppaypalpluspayment.OXPAYMENTID
where 1 and ( oxorder.oxfolder = 'ORDERFOLDER_NEW' )
count(*) Result 150000
The problem may be this join, the others are instant results
rows 0 – 0 (1 total, Query took 153.2391 seconds.)
Select count(*) from `oxorder`
LEFT JOIN payppaypalpluspayment
ON payppaypalpluspayment.OXORDERID = oxorder.OXID
EXPLAIN
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE oxorder index NULL MAINIDX 10 NULL 146861 Using index
1 SIMPLE payppaypalpluspayment index NULL OXORDERID 32 NULL 2630 Using where; Using index; Using join buffer (flat, BNL join)
1 SIMPLE payppaypalpluspui ALL NULL NULL NULL NULL 519 Using where
This explain changed after adding indexes in
oxorder.OXPAYMENTTYPE;
oxorder.oxfolder;
payppaypalpluspui.OXPAYMENTID;
payppaypalpluspayment.OXPAYMENTID;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE oxorder ref OXFOLDER OXFOLDER 98 const 73450 Using index condition
1 SIMPLE payppaypalpluspayment ALL NULL NULL NULL NULL 2634 Using where; Using join buffer (flat, BNL join)
1 SIMPLE payppaypalpluspui ref OXPAYMENTID OXPAYMENTID 34 payppaypalpluspayment.OXPAYMENTID 1 Using where; Using index
Which seems more strange even so its a litle faster (still 170s)
After this count selects the backend is doing the same query with select oxorder.* and one column from payppaypalpluspui (takes about 600 seconds)
The UI has then already timed out. When I delete the last two joins and try it manually on the server the query takes < 1sec
dbs
CREATE TABLE `payppaypalpluspayment` (
`OXID` char(32) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL COMMENT 'Payment oxid id',
`OXORDERID` char(32) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL COMMENT 'Order id',
`OXSALEID` varchar(32) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL DEFAULT '' COMMENT 'PayPal Plus payment sale id',
`OXPAYMENTID` varchar(32) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL DEFAULT '' COMMENT 'PayPal Plus payment id',
`OXSTATUS` varchar(32) NOT NULL DEFAULT '' COMMENT 'PayPal Plus payment status',
`OXDATECREATED` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Payment creation date',
`OXTOTAL` double NOT NULL DEFAULT '0' COMMENT 'Total payment amount',
`OXCURRENCY` varchar(32) NOT NULL DEFAULT '' COMMENT 'Payment currency',
`OXPAYMENTOBJECT` blob NOT NULL COMMENT 'Serialized payment object',
PRIMARY KEY (`OXID`),
UNIQUE KEY `OXORDERID` (`OXORDERID`),
UNIQUE KEY `OXSALEID` (`OXSALEID`)
KEY `OXPAYMENTID` (`OXPAYMENTID`) <<< added this index
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='PayPal Plus payment data model'
Rows 3,228 InnoDB utf8_general_ci 11.4 MiB
CREATE TABLE `oxorder` (
`OXID` char(32) NOT NULL COMMENT 'Order id',
`OXSHOPID` int(11) NOT NULL DEFAULT '1' COMMENT 'Shop id (oxshops)',
`OXUSERID` char(32) NOT NULL DEFAULT '' COMMENT 'User id (oxuser)',
`OXORDERDATE` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Order date',
`OXORDERNR` varchar(16) NOT NULL COMMENT 'Order number',
.....
PRIMARY KEY (`OXID`),
KEY `MAINIDX` (`OXSHOPID`,`OXSTORNO`,`OXORDERDATE`),
KEY `OXORDERNR` (`OXORDERNR`)
KEY `OXPAYMENTTYPE` (`OXPAYMENTTYPE`), <<<< added this index
KEY `OXFOLDER` (`OXFOLDER`) <<<< added this index
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Shop orders information'
149,068 InnoDB utf8_general_ci 258.1 MiB
CREATE TABLE `payppaypalpluspui` (
`OXID` char(32) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL COMMENT 'Payment oxid id',
`OXPAYMENTID` varchar(32) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL DEFAULT '' COMMENT 'PayPal Plus payment id',
`OXREFERENCENUMBER` varchar(255) NOT NULL DEFAULT '' COMMENT 'PayPal Plus PuI reference_number',
`OXBANKNAME` varchar(255) NOT NULL DEFAULT '' COMMENT 'PayPal Plus PuI banking instruction bank name',
`OXACCOUNTHOLDER` varchar(255) NOT NULL DEFAULT '' COMMENT 'PayPal Plus PuI banking instruction account holder',
`OXIBAN` varchar(255) NOT NULL DEFAULT '' COMMENT 'PayPal Plus PuI banking instruction IBAN',
`OXBIC` varchar(255) NOT NULL DEFAULT '' COMMENT 'PayPal Plus PuI banking instruction BIC',
`OXDUEDATE` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'PayPal Plus PuI due date',
`OXTOTAL` double NOT NULL DEFAULT '0' COMMENT 'PayPal Plus PuI Total invoice amount',
`OXCURRENCY` varchar(32) NOT NULL DEFAULT '' COMMENT 'PayPal Plus PuI invoice currency',
`OXPUIOBJECT` text NOT NULL COMMENT 'JSON representation of the payment instructions',
PRIMARY KEY (`OXID`)
KEY `OXPAYMENTID` (`OXPAYMENTID`) <<<< added this index
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='PayPal Plus Pay upon Invoice data model'
Rows 655 InnoDB utf8_general_ci 1.5 MiB
innodb buffer pool = 8 GiB
RAM 10 GiB
Answer :
Clearly, they were missing some indexes. Shame on them.
First, is 150K the correct answer? Keep in mind that all the JOINs are done first, thereby exploding the number of rows, then the COUNT is done. Also, do you really need LEFT JOIN instead of JOIN?
What kind of value is in VARCHAR(32)? Some kind of hash? For large tables, that can make the query I/O-bound. Please provide the table sizes and the value of innodb_buffer_pool_size and how much RAM you have. With those, I can judge whether this is a real problem, or whether the tables are not yet big enough to cause trouble.
If the VARCHAR(32) are hex strings, then packing them and putting them into BINARY(16) would save a lot of space.
payppaypalpluspayment has 3 Unique keys. That is generally unreasonable. But I guess you have no control over that?
DOUBLE for money? Ouch. (This is an oft-discussed topic on this forum.)
Please pass your indexes and my comments on to Oxid.
Plus (very important): Use the same CHARACTER SET and COLLATION for any columns that you are JOINing on. (See oysteing’s answer in your dup Question.)