Slow Invoice Searches (~30S)

[Jonathan]

Invoice searches are doing some nasty temporary tables to disk making them extremely slow.  The temp table it's making is about ~80MB for me.

SELECT COUNT(*) AS `total` FROM ((SELECT * FROM (SELECT `invoices`.*, REPLACE(`invoices`.`id_format`, '{num}', `invoices`.`id_value`) AS `id_code`, `invoice_delivery`.`date_sent` AS `delivery_date_sent`, REPLACE(`clients`.`id_format`, '{num}', `clients`.`id_value`) AS `client_id_code`, `contacts`.`first_name` AS `client_first_name`, `contacts`.`last_name` AS `client_last_name`, `contacts`.`company` AS `client_company`, `contacts`.`address1` AS `client_address1`, `contacts`.`email` AS `client_email`, invoices.total-IFNULL(invoices.paid,0) AS `due` FROM `invoices` INNER JOIN `clients` ON `clients`.`id` = `invoices`.`client_id` INNER JOIN `client_groups` ON `client_groups`.`id` = `clients`.`client_group_id` INNER JOIN `contacts` ON `contacts`.`contact_type` = 'primary' AND `contacts`.`client_id` = `clients`.`id` LEFT JOIN `invoice_delivery` ON `invoice_delivery`.`date_sent` IS NOT NULL AND `invoice_delivery`.`invoice_id` = `invoices`.`id` WHERE `client_groups`.`company_id` = '1' GROUP BY `invoices`.`id`) AS `temp` WHERE CONVERT(temp.id_code USING utf8) LIKE '%460669%' OR `temp`.`client_id_code` LIKE '%460669%' OR `temp`.`client_company` LIKE '%460669%' OR CONCAT_WS(' ', temp.client_first_name, temp.client_first_name) LIKE '%460669%' OR `temp`.`client_address1` LIKE '%460669%' OR `temp`.`client_email` LIKE '%460669%')) AS `t_1670833049`;

MariaDB [blesta]> explain SELECT COUNT(*) AS `total` FROM ((SELECT * FROM (SELECT `invoices`.*, REPLACE(`invoices`.`id_format`, '{num}', `invoices`.`id_value`) AS `id_code`, `invoice_delivery`.`date_sent` AS `delivery_date_sent`, REPLACE(`clients`.`id_format`, '{num}', `clients`.`id_value`) AS `client_id_code`, `contacts`.`first_name` AS `client_first_name`, `contacts`.`last_name` AS `client_last_name`, `contacts`.`company` AS `client_company`, `contacts`.`address1` AS `client_address1`, `contacts`.`email` AS `client_email`, invoices.total-IFNULL(invoices.paid,0) AS `due` FROM `invoices` INNER JOIN `clients` ON `clients`.`id` = `invoices`.`client_id` INNER JOIN `client_groups` ON `client_groups`.`id` = `clients`.`client_group_id` INNER JOIN `contacts` ON `contacts`.`contact_type` = 'primary' AND `contacts`.`client_id` = `clients`.`id` LEFT JOIN `invoice_delivery` ON `invoice_delivery`.`date_sent` IS NOT NULL AND `invoice_delivery`.`invoice_id` = `invoices`.`id` WHERE `client_groups`.`company_id` = '1' GROUP BY `invoices`.`id`) AS `temp` WHERE CONVERT(temp.id_code USING utf8) LIKE '%460669%' OR `temp`.`client_id_code` LIKE '%460669%' OR `temp`.`client_company` LIKE '%460669%' OR CONCAT_WS(' ', temp.client_first_name, temp.client_first_name) LIKE '%460669%' OR `temp`.`client_address1` LIKE '%460669%' OR `temp`.`client_email` LIKE '%460669%')) AS `t_1670833049`;

+------+-------------+------------------+------+-------------------------+-----------------+---------+-------------------------+-------+----------------------------------------------+

| id   | select_type | table            | type | possible_keys           | key             | key_len | ref                     | rows  | Extra                                        |

+------+-------------+------------------+------+-------------------------+-----------------+---------+-------------------------+-------+----------------------------------------------+

|    1 | PRIMARY     | <derived3>       | ALL  | NULL                    | NULL            | NULL    | NULL                    | 86328 | Using where                                  |

|    3 | DERIVED     | client_groups    | ref  | PRIMARY,company_id      | company_id      | 4       | const                   |     1 | Using index; Using temporary; Using filesort |

|    3 | DERIVED     | clients          | ref  | PRIMARY,client_group_id | client_group_id | 4       | blesta.client_groups.id |  9592 |                                              |

|    3 | DERIVED     | contacts         | ref  | contact_type,client_id  | client_id       | 5       | blesta.clients.id,const |     1 | Using index condition                        |

|    3 | DERIVED     | invoices         | ref  | client_id               | client_id       | 4       | blesta.clients.id       |     9 |                                              |

|    3 | DERIVED     | invoice_delivery | ref  | invoice_id              | invoice_id      | 4       | blesta.invoices.id      |     1 | Using where                                  |

+------+-------------+------------------+------+-------------------------+-----------------+---------+-------------------------+-------+----------------------------------------------+

It's making some pretty massive temporary tables on disk.  The searches are taking ~30 seconds for a direct match invoice number.

[Paul]

If it was searching just for the invoice number it would be fast, but it looks like its doing a lot of joins and sub queries. Cody will likely need to look at the query to see if it can be optimized.

 

This does highlight the need for more advanced filtering, which we plan to add under Billing > Invoices. The thing about searches is that if you limit the result set by specifying multiple criteria it's going to be faster.