Orders Controller delays

[cliffconway]

I am experiencing long delays in the BO Orders function (300,000 + orders). Switching on Debug I notice that the SQL statement generated by the php AdminController includes the "SQL_NO_CACHE" clause. I am aware that the database access statement in the controller ($db->executeS($sql, true, false);) has a parameter related to caching (currently set to false). I am aware that removing the no cache clause may have other ramifications. However, I have set the cache parameter (in the relevant executeS code in the controller statement) to true and still the order SQL queries have the "SQL_NO_CACHE" clause inserted. Why does this caching parameter appear not to work?

If I am on the wrong track, related to delays, then any other useful info that can help me remove the "SQL_NO_CACHE" for my experimentation would be very much appreciated.

Thanks in advance

[EvaF]

I had a similiar problem with a loong response time. In my case it was caused by ps_log table.
You could check, if your problem is not caused  also by this table:
1) do a copy of table ps_log
2) empty the table ps_log
3) try to work with BO Orders and compare delaying

4a) if it doesn't help - return back ps_log from a copy
4b) if it helps - return back ps_log from a copy, reduce it's size ( =delete obsolete logs) , check neccesarity of  your logs( if they exist) and create a new index ps_log at least for these four colums (severity, error_code, object_type, object_id)

[cliffconway]

Thanks EvaF for your fast response.

The ps_logs had 3,500 records only. I cleared them out and still long delays on the order query. I will try indexing as recommended

[EvaF]

3500 logs are quite a little...
Indexing helps only for the cases that object_id is specified - (unfortunatelly it is  related approx 5% logs)
But in your cases I would try to do steps 1)-3) and would check the types of new logs.
(if the new logs will contain "image..." then write)

[cliffconway]

When I cleared out I tried steps 1 to 3. Still slow. I have set up indexes as suggested. Still slow order query.

Given that there are now only 2 logs in the table (and originally only 3500 records) I am sure that the delay is not, in any major way, related to the ps_logs table.

I have taken the orders generated SQL statement related to the orders query and run it in phpMyAdmin and MySQL workbench. Removing the "SQL_NO_CACHE" improves the times dramatically  (after the first run which obviously builds the cache). So this clause seems to be the root of the issue.

Thanks again

[EvaF]

you are welcome

 

 

Edited October 19, 2020 by EvaF (see edit history)