Mollie payment module - orders.php loading slowly - database code

[30knees]

The latest Mollie payment module for PS 1.6 makes orders.php load very slowly. Someone reported a similar problem with PS 1.7 and the Mollie module. He wrote:

Quote

 

After investigation I found that the query for the AdminOrders page was slightly changed. Running the full query took over 4000ms. After removal of the extra LEFT JOIN it runs only 200ms (see code below)

' LEFT JOIN ' . DB_PREFIX . 'mollie_payments mol ON mol.order_reference = a.reference';
Best to add an extra index on the SQL script?

After adding an index to the order_reference column of table ps_mollie_payments, the problem is solved. Query time reduced to 220ms.

 

 

If I found the right code section in the Mollie module in this file https://github.com/mollie/PrestaShop/blob/master/mollie.php this is what the code looks like. Does anyone see at a quick glance what the problem might be / how to edit it?

public function hookActionAdminOrdersListingFieldsModifier($params)

{

if (isset($params['select'])) {

$params['select'] = rtrim($params['select'], ' ,') . ' ,mol.`transaction_id`';

}

if (isset($params['join'])) {

$params['join'] .= ' LEFT JOIN `' . _DB_PREFIX_ . 'mollie_payments` mol ON mol.`order_reference` = a.`reference`';

}

$params['fields']['order_id'] = [

'title' => $this->l('Resend payment link'),

'align' => 'text-center',

'class' => 'fixed-width-xs',

'orderby' => false,

'search' => false,

'remove_onclick' => true,

'callback_object' => 'mollie',

'callback' => 'resendOrderPaymentLink'

];

}

 

[datakick]

The code is perfectly fine, there's nothing wrong here. It just adds additional table to the query that fetches orders. 

The problem is that your database select bad plan to execute this query. The solution is to help the database -- create index that it can use to optimally retrieve data. Something like this:

CREATE INDEX mp_order_reference ON tb_mollie_payments(order_reference);

With such index the query will run much faster, probably

[30knees]

Super, thanks! I shared that with Mollie and asked where I'd put it. I'll share the answer here, as I know some other users also use Mollie.

[30knees]

Mollie replied, there already is an index. So it must be something else. Anyways, thanks for your help! It's so weird that that the page takes a couple of seconds to load, also when filtering by status. Only after the first load does it load quickly for a while.

Quote

 

There is already an index on order_reference column:

$sql[] = 'CREATE TABLE IF NOT EXISTS '._DB_PREFIX_.'mollie_payments (
transaction_id VARCHAR(64) NOT NULL PRIMARY KEY,
cart_id INT(64),
order_id INT(64),
order_reference VARCHAR(191),
method VARCHAR(128) NOT NULL,
bank_status VARCHAR(64) NOT NULL,
created_at DATETIME NOT NULL,
updated_at DATETIME DEFAULT NULL,
INDEX (cart_id, order_reference)
) ENGINE='.MYSQL_ENGINE.' DEFAULT CHARSET=utf8;';

In https://github.com/mollie/PrestaShop/blob/master/src/Install/DatabaseTableInstaller.php , so this index will not change anything.

 

 

Edited January 11, 2021 by 30knees

[datakick]

INDEX (cart_id, order_reference)

That's composed index, based on two columns. This index would be used only if the join contained cart_id column as well.

But in this case, the join contains order_reference only, so db will happily ignore it.

It's possible that there is bug in php code -- maybe the join should contain cart_id.

Or if the php code is ok, then there should be standalone index with this order_reference column only.

[30knees]

Thanks, they fixed everything and now it's working well. Your suggestion put them right on track! Hurray for datakick, so nice you're on board. 🙂