I am searching here since days/hours but I found no solution. Dolibarr is pretty fast and also there are a lot of records, display of the lists is really fast.
There is only one exception, /commande/list.php sometimes, not always, needs 10-15 seconds to display the order list, litmited to 20, currently ~ 11000 orders in the system.
For normal the list is display in less than a second, but from time to time it needa 10-15 secs. After this slow one, it is again within one sec. I found no trigger when this long one happens.
Marisdb slow query log show this a lot of times:
Database changed
MariaDB [dolibarrdemo01]> EXPLAIN SELECT sum((CASE WHEN f.type=2 THEN -1 ELSE 1 END) * fd.qty) as count FROM llx_facturedet as fd JOIN llx_facture as f ON fd.fk_facture = f.rowid JOIN llx_element_element as el ON ((el.fk_target = f.rowid AND el.targettype = 'facture' AND sourcetype = 'commande') OR (el.fk_source = f.rowid AND el.targettype = 'commande' AND sourcetype = 'facture')) JOIN llx_commande as c ON el.fk_source = c.rowid WHERE c.fk_statut IN (1,2) AND f.fk_statut > 0 AND fd.fk_product = 87;
+------+-------------+-------+--------+--------------------------------------------------------+---------------------------+---------+-----------------------------+-------+--------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+--------+--------------------------------------------------------+---------------------------+---------+-----------------------------+-------+--------------------------------------------------------------+
| 1 | SIMPLE | f | ALL | PRIMARY,idx_facture_fk_statut | NULL | NULL | NULL | 21467 | Using where |
| 1 | SIMPLE | fd | ref | idx_facturedet_fk_facture,idx_facturedet_fk_product | idx_facturedet_fk_facture | 4 | dolibarrdemo01.f.rowid | 1 | Using where |
| 1 | SIMPLE | el | index | idx_element_element_idx1,idx_element_element_fk_target | idx_element_element_idx1 | 524 | NULL | 23079 | Using where; Using index; Using join buffer (flat, BNL join) |
| 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 4 | dolibarrdemo01.el.fk_source | 1 | Using where |
+------+-------------+-------+--------+--------------------------------------------------------+---------------------------+---------+-----------------------------+-------+--------------------------------------------------------------+
4 rows in set (0,002 sec)
This is from the load_stats_commande class in products class. I am no SQL specialist, but for me this looks like no index is used on line 1.
Okay. If you have a mysqldump file then you could easily in another mariadb (or mysql) instance start a database from that file - at least if you were running the mariadb container.
I use that functionality here to run my developer setup from containers based on a Dolibarr database backup file.
But of course of the database are TB big that will take some time to do
Sorry moving away from 20.0.4 is no way currently. Need to find the problem in 20.0.4, especially the above problem, where it looks like for me that a key is missing. already checked some diffs between 20.0.4 and 22.x, but nothing found.
I finally found out, that it is really this query, from time to time needing 5 secs?
MariaDB [dolibarr]> SELECT sum((CASE WHEN f.type=2 THEN -1 ELSE 1 END) * fd.qty) as count FROM llx_facturedet as fd JOIN llx_facture as f ON fd.fk_facture = f.rowid JOIN llx_element_element as el ON ((el.fk_target = f.rowid AND el.targettype = 'facture' AND sourcetype = 'commande') OR (el.fk_source = f.rowid AND el.targettype = 'commande' AND sourcetype = 'facture')) JOIN llx_commande as c ON el.fk_source = c.rowid WHERE c.fk_statut IN (1,2) AND f.fk_statut > 0 AND fd.fk_product = 6;
+-------+
| count |
+-------+
| 11 |
+-------+
1 row in set (5,531 sec)
MariaDB [dolibarr]> SELECT sum((CASE WHEN f.type=2 THEN -1 ELSE 1 END) * fd.qty) as count FROM llx_facturedet as fd JOIN llx_facture as f ON fd.fk_facture = f.rowid JOIN llx_element_element as el ON ((el.fk_target = f.rowid AND el.targettype = 'facture' AND sourcetype = 'commande') OR (el.fk_source = f.rowid AND el.targettype = 'commande' AND sourcetype = 'facture')) JOIN llx_commande as c ON el.fk_source = c.rowid WHERE c.fk_statut IN (1,2) AND f.fk_statut > 0 AND fd.fk_product = 623;
+-------+
| count |
+-------+
| 1 |
+-------+
1 row in set (0,422 sec)
MariaDB [dolibarr]> SELECT sum((CASE WHEN f.type=2 THEN -1 ELSE 1 END) * fd.qty) as count FROM llx_facturedet as fd JOIN llx_facture as f ON fd.fk_facture = f.rowid JOIN llx_element_element as el ON ((el.fk_target = f.rowid AND el.targettype = 'facture' AND sourcetype = 'commande') OR (el.fk_source = f.rowid AND el.targettype = 'commande' AND sourcetype = 'facture')) JOIN llx_commande as c ON el.fk_source = c.rowid WHERE c.fk_statut IN (1,2) AND f.fk_statut > 0 AND fd.fk_product = 6;
+-------+
| count |
+-------+
| 11 |
+-------+
1 row in set (0,000 sec)
--- product.class.php 2025-03-21 12:19:54.268000000 +0100
+++ product.class.php.UNIONJOIN 2025-10-03 13:34:54.114942563 +0200
@@ -3279,16 +3279,33 @@
// If stock decrease is on invoice validation, the theoretical stock continue to
// count the orders lines in theoretical stock when some are already removed by invoice validation.
+ //
+ // THIS QUERY is brutal slow because of the OR in join. Thanks to thumbs in #maria pointing this out
+ // we change from JOIN with OR to UNION stapeling 2 queries. Test reduces the scanned records from
+ // > 22000 in our case to 534. Now speed problems for displaying commande/list.php and product list
+ // should be gone.
+ //
if ($forVirtualStock && getDolGlobalString('STOCK_CALCULATE_ON_BILL')) {
if (getDolGlobalString('DECREASE_ONLY_UNINVOICEDPRODUCTS')) {
// If option DECREASE_ONLY_UNINVOICEDPRODUCTS is on, we make a compensation but only if order not yet invoice.
$adeduire = 0;
$sql = "SELECT SUM(".$this->db->ifsql('f.type=2', -1, 1)." * fd.qty) as count FROM ".$this->db->prefix()."facturedet as fd ";
$sql .= " JOIN ".$this->db->prefix()."facture as f ON fd.fk_facture = f.rowid";
- $sql .= " JOIN ".$this->db->prefix()."element_element as el ON ((el.fk_target = f.rowid AND el.targettype = 'facture' AND sourcetype = 'commande') OR (el.fk_source = f.rowid AND el.targettype = 'commande' AND sourcetype = 'facture'))";
+ $sql .= " JOIN ".$this->db->prefix()."element_element as el ON el.fk_target = f.rowid AND el.targettype = 'facture' AND sourcetype = 'commande' ";
$sql .= " JOIN ".$this->db->prefix()."commande as c ON el.fk_source = c.rowid";
$sql .= " WHERE c.fk_statut IN (".$this->db->sanitize($filtrestatut).") AND c.facture = 0 AND fd.fk_product = ".((int) $this->id);
+ $sql .= " UNION ";
+
+ $sql .= "SELECT SUM(".$this->db->ifsql('f.type=2', -1, 1)." * fd.qty) as count FROM ".$this->db->prefix()."facturedet as fd ";
+ $sql .= " JOIN ".$this->db->prefix()."facture as f ON fd.fk_facture = f.rowid";
+ $sql .= " JOIN ".$this->db->prefix()."element_element as el ON el.fk_source = f.rowid AND el.targettype = 'commande' AND sourcetype = 'facture' ";
+ $sql .= " JOIN ".$this->db->prefix()."commande as c ON el.fk_source = c.rowid";
+ $sql .= " WHERE c.fk_statut IN (".$this->db->sanitize($filtrestatut).") AND c.facture = 0 AND fd.fk_product = ".((int) $this->id);
+
+
+
+
dol_syslog(__METHOD__.":: sql $sql", LOG_NOTICE);
$resql = $this->db->query($sql);
if ($resql) {
@@ -3307,7 +3324,15 @@
$adeduire = 0;
$sql = "SELECT sum(".$this->db->ifsql('f.type=2', -1, 1)." * fd.qty) as count FROM ".MAIN_DB_PREFIX."facturedet as fd ";
$sql .= " JOIN ".MAIN_DB_PREFIX."facture as f ON fd.fk_facture = f.rowid";
- $sql .= " JOIN ".MAIN_DB_PREFIX."element_element as el ON ((el.fk_target = f.rowid AND el.targettype = 'facture' AND sourcetype = 'commande') OR (el.fk_source = f.rowid AND el.targettype = 'commande' AND sourcetype = 'facture'))";
+ $sql .= " JOIN ".MAIN_DB_PREFIX."element_element as el ON el.fk_target = f.rowid AND el.targettype = 'facture' AND sourcetype = 'commande' ";
+ $sql .= " JOIN ".MAIN_DB_PREFIX."commande as c ON el.fk_source = c.rowid";
+ $sql .= " WHERE c.fk_statut IN (".$this->db->sanitize($filtrestatut).") AND f.fk_statut > ".Facture::STATUS_DRAFT." AND fd.fk_product = ".((int) $this->id);
+
+ $sql .= " UNION ";
+
+ $sql .= "SELECT sum(".$this->db->ifsql('f.type=2', -1, 1)." * fd.qty) as count FROM ".MAIN_DB_PREFIX."facturedet as fd ";
+ $sql .= " JOIN ".MAIN_DB_PREFIX."facture as f ON fd.fk_facture = f.rowid";
+ $sql .= " JOIN ".MAIN_DB_PREFIX."element_element as el ON el.fk_source = f.rowid AND el.targettype = 'commande' AND sourcetype = 'facture' ";
$sql .= " JOIN ".MAIN_DB_PREFIX."commande as c ON el.fk_source = c.rowid";
$sql .= " WHERE c.fk_statut IN (".$this->db->sanitize($filtrestatut).") AND f.fk_statut > ".Facture::STATUS_DRAFT." AND fd.fk_product = ".((int) $this->id);
