Prestashop: SQL performance improvement in WebserviceRequest

[DelecroixQuentin]

Describe the bug

https://github.com/PrestaShop/PrestaShop/blob/develop/classes/webservice/WebserviceRequest.php#L1734

(Validate::isFloat(pSQL($filterValue)) ? 'LIKE' : '=')
The ternary condition is inverted, it uses a LIKE statement instead of a = statement for int and float and uses a = statement for strings.

By the way, is it useful to use a LIKE statement here ?
Since in line 1703 the presence of % is checked for the use of a LIKE statement. If in $filterValue there is no %, whether $filterValue is an int, float or string, we can use a simple =

Expected behavior

Use = "42" instead of LIKE "42" when $filterValue is an int or a float

Additional information

• PrestaShop version: 1.7.6.5
• PHP version: 7.2

[prestashop-issue-bot[bot]]

Thanks for opening this issue! We will help you to keep its state consistent

[florine2623]

Hi @DelecroixQuentin ,
Thanks for your suggestion.
I will ping our dev team to let them know.
Please stay tuned !

[florine2623]

Hi @PrestaShop/prestashop-core-developers ,

Could you take a look at @DelecroixQuentin ? It might be interesting :)

Thanks!

[Kioob]

I add some background, the resulting query will be much more faster with this fix :

MariaDB [www72]> explain SELECT DISTINCT main.`id_stock_available` FROM `ps_stock_available` AS main
    -> WHERE 1 AND ((main.id_shop = 1 OR (id_shop = 0 AND id_shop_group=1)))  AND `main`.`id_product_attribute` LIKE "0"
    ->  AND `main`.`id_product` LIKE "16050";
+------+-------------+-------+-------+------------------------------------------------------------------------+------------------+---------+------+-------+--------------------------+
| id   | select_type | table | type  | possible_keys                                                          | key              | key_len | ref  | rows  | Extra                    |
+------+-------------+-------+-------+------------------------------------------------------------------------+------------------+---------+------+-------+--------------------------+
|    1 | SIMPLE      | main  | index | product_sqlstock,id_shop,id_shop_group,id_product,id_product_attribute | product_sqlstock | 16      | NULL | 87164 | Using where; Using index |
+------+-------------+-------+-------+------------------------------------------------------------------------+------------------+---------+------+-------+--------------------------+

Current : 87164 lines analyzed.

MariaDB [www72]> explain SELECT DISTINCT main.`id_stock_available` FROM `ps_stock_available` AS main WHERE 1 AND ((main.id_shop = 1 OR (id_shop = 0 AND id_shop_group=1)))  AND `main`.`id_product_attribute` LIKE "0"  AND `main`.`id_product` = "16050";
+------+-------------+-------+------+------------------------------------------------------------------------+------------------+---------+-------+------+--------------------------+
| id   | select_type | table | type | possible_keys                                                          | key              | key_len | ref   | rows | Extra                    |
+------+-------------+-------+------+------------------------------------------------------------------------+------------------+---------+-------+------+--------------------------+
|    1 | SIMPLE      | main  | ref  | product_sqlstock,id_shop,id_shop_group,id_product,id_product_attribute | product_sqlstock | 4       | const |    6 | Using where; Using index |
+------+-------------+-------+------+------------------------------------------------------------------------+------------------+---------+-------+------+--------------------------+

With the fix : only 6 lines analyzed.

[matks]

Well, looks like the mistake is obvious thanks to @DelecroixQuentin and @Kioob insights. Would you be willing to do a PR to modify this ? This would credit you as contributors. If I do the PR, you will not be credited.

[DelecroixQuentin]

Hi @matks,

I will do the PR this week asap. Thanks !

[DelecroixQuentin]

PR done: #19249

[matks]

Fixed by https://github.com/PrestaShop/PrestaShop/pull/19249

@Kioob it will be better in next version 😉 thanks to @DelecroixQuentin