Dashboard Slow Loading

  • Posts: 303
  • Thank you received: 18
6 years 6 months ago #291909

-- HikaShop version -- : 3.4.0
-- Joomla version -- : 3.8.6

For a while now, our dashboard takes an abnormal amount of time to load -- sometimes more than 20 seconds. Is there a particular database table that might be slowing it down? Large order or email log table? I've been trying to locate a fix but haven't had much luck.

The dashboard is the only page to load slowly. The rest of the backend has minimal loading times.

Please Log in or Create an account to join the conversation.

  • Posts: 82863
  • Thank you received: 13372
  • MODERATOR
6 years 6 months ago #291917

Hi,

Turn on the "debug mode" setting of the Joomla configuration and look at the bottom of the dashboard page.
You'll get all the queries on the page with the timings for each one.
That way, you can see which query is slow and then deduce from that which table is too big and / or which query could be optimized.

Please Log in or Create an account to join the conversation.

  • Posts: 303
  • Thank you received: 18
6 years 6 months ago #291961

I turned on the debugging option and i see several duplicate entries and a few calls that are taking the majority of the load time. Here is an example:



However, i'm unsure how to resolve the errors. Seems to be related to the hikashop tables though. Not sure what the "NO INDEX KEY COULD BE USED" error is, but it seems that it forces the entire table to be indexed which takes a ton of time. Any ways to quickly resolve this? Surely it's a common problem?

Attachments:

Please Log in or Create an account to join the conversation.

  • Posts: 82863
  • Thank you received: 13372
  • MODERATOR
6 years 6 months ago #292009

Hi,

For some reasons it looks like it's not able to use the index available on the file_type column of the hikashop_file table. Can you check that you have that index ?
superuser.com/questions/766451/view-exis...xes-using-phpmyadmin
It's not a common problem.
Database and mysql queries optimization is not a simple matter. It depends on how much data you have in each table of the Mysql query, how the query is constructed and how the tables are made to store the data.
A query might be optimized for a specific case which might end up bein detrimental to another case.

Can you check whether you have the "dashboard legacy" setting activated or not ?
Changing that setting could well help circumvent the problem.

Please Log in or Create an account to join the conversation.

  • Posts: 303
  • Thank you received: 18
6 years 6 months ago #292044

Here's the structure for that table:



Also, the legacy checkout option is turned off. However, it is also slow with it turned on as well.

Attachments:

Please Log in or Create an account to join the conversation.

  • Posts: 82863
  • Thank you received: 13372
  • MODERATOR
6 years 6 months ago #292069

Hi,

The legacy checkout option won't change anything regarding the dashboard. I was talking about the "dashboard legacy" setting. Please check the correct setting.

Regarding the table structure, just below the area of your screenshot, you must have an "index" link you can click on and it will then expand to display the indexes on that table:
monosnap.com/file/PVtqs7dhl3Yu0gqw5b4V54kEbu8VGc

Please Log in or Create an account to join the conversation.

  • Posts: 303
  • Thank you received: 18
6 years 6 months ago #292113

Woops. Meant same behavior whether dashboard legacy is turned on or off.

As for the index, here's the expanded screenshot.



Seems that there is only one index. Your screenshot shows two. Is there any easy way to add this in to correct the issue?

Attachments:

Please Log in or Create an account to join the conversation.

  • Posts: 26158
  • Thank you received: 4028
  • MODERATOR
6 years 6 months ago #292118

Hello,

Regarding the query details, it looks like the "order product" table is right now the element to optimize.
It might be interesting to add an index on the "product_id" to speed up some queries ; you have a very high number of entries in that table in your side.

The "generic" query to add the index is :

ALTER TABLE `#__hikashop_order_product` ADD INDEX `product_id` (`product_id`);
But you have to replace the "#_" by your database price ; which will be in your case
ALTER TABLE `jml_hikashop_order_product` ADD INDEX `product_id` (`product_id`);

Please let us know if it improve the query and you can give us a new screenshot of the debug so we can compare values.

Regards,

Jerome - Obsidev.com
HikaMarket & HikaSerial developer / HikaShop core dev team.

Also helping the HikaShop support team when having some time or couldn't sleep.
By the way, do not send me private message, use the "contact us" form instead.

Please Log in or Create an account to join the conversation.

  • Posts: 303
  • Thank you received: 18
6 years 6 months ago #292183

Maybe it helped a little. Hard to tell as the page still takes quite a while to load.



As far as the database results, the key was successfully added in the jml_hikashop_order_product table


Any other suggestions? I can give database access and super user access to the backend of the website if necessary.

Attachments:

Please Log in or Create an account to join the conversation.

  • Posts: 303
  • Thank you received: 18
6 years 6 months ago #292184

Also, here is the database call that is taking the most time:

Attachments:

Please Log in or Create an account to join the conversation.

  • Posts: 26158
  • Thank you received: 4028
  • MODERATOR
6 years 6 months ago #292185

Hello,

I don't remember in which version exactly but we added an index in the hikashop_file table ; but just in the database definition and not via the update system.
So, it could explain why you do not have the index for that.

The "generic" query to add the index is :

ALTER TABLE `#__hikashop_file` ADD INDEX `file_type` (`file_type`);

Afterwards, the index could be more optimized by using a combination of file_type + file_ref_id.

Regards,

Jerome - Obsidev.com
HikaMarket & HikaSerial developer / HikaShop core dev team.

Also helping the HikaShop support team when having some time or couldn't sleep.
By the way, do not send me private message, use the "contact us" form instead.

Please Log in or Create an account to join the conversation.

  • Posts: 303
  • Thank you received: 18
6 years 6 months ago #292202

Much better! However, I'm not well versed in the database management.

By using a combination of file_type + file_ref_id, do you mean to generate another index called file_type with:

ALTER TABLE `#__hikashop_file` ADD INDEX `file_ref_id` (`file_ref_id`);

Please Log in or Create an account to join the conversation.

  • Posts: 82863
  • Thank you received: 13372
  • MODERATOR
6 years 6 months ago #292214

Hi,

Jerome meant using such query:

ALTER TABLE `#__hikashop_file` ADD INDEX `file_ref_id` (`file_ref_id`, `file_type`);

The following user(s) said Thank You: mojooutdoors-holden

Please Log in or Create an account to join the conversation.

  • Posts: 303
  • Thank you received: 18
6 years 6 months ago #292260

Will there by any issue if I already added what jerome sent?

ALTER TABLE `#__hikashop_file` ADD INDEX `file_type` (`file_type`);

Meaning if I add the query you sent Nicolas, will it mess anything up?

Please Log in or Create an account to join the conversation.

  • Posts: 26158
  • Thank you received: 4028
  • MODERATOR
6 years 6 months ago #292269

Hello,

You can have multiple index in your tables (as long as the index name is unique).
But it is possible to add several time an index on the same columns, it won't bother mysql (but it will tell you that it's useless).

Regards,

Jerome - Obsidev.com
HikaMarket & HikaSerial developer / HikaShop core dev team.

Also helping the HikaShop support team when having some time or couldn't sleep.
By the way, do not send me private message, use the "contact us" form instead.
The following user(s) said Thank You: mojooutdoors-holden

Please Log in or Create an account to join the conversation.

  • Posts: 303
  • Thank you received: 18
6 years 6 months ago #292438

Thanks guys. Sorry for the lack of knowledge of SQL tables. Pretty much my first experience with them.

Dashboard loading time went from +10 seconds down to just 2-3 seconds.

Please Log in or Create an account to join the conversation.

Time to create page: 0.111 seconds
Powered by Kunena Forum