Jump to content

Welcome, Guest!

By registering with us, you'll be able to discuss, share and private message with other members of our community.

  • 0
30knees

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

Question

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'

];

}

 

Share this post


Link to post
Share on other sites

4 answers to this question

Recommended Posts

  • 0

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

  • Thanks 1

Share this post


Link to post
Share on other sites
  • 0

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.

Share this post


Link to post
Share on other sites
  • 0

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 by 30knees

Share this post


Link to post
Share on other sites
  • 0
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.

  • Like 1

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...