Dashboard Slow Loading

-- 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.

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.

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?

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.

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.

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

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?

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 : But you have to replace the "#_" by your database price ; which will be in your case
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,

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.

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

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 :
Afterwards, the index could be more optimized by using a combination of file_type + file_ref_id.

Regards,

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:

Hi,

Jerome meant using such query:

Will there by any issue if I already added what jerome sent?
Meaning if I add the query you sent Nicolas, will it mess anything up?

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,

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.