Prestashop: Search by product reference causes full table scan

[Gamesh]

Is your feature request related to a problem? Please describe.
Searches by product reference are slow on large product table because it issues full table scan

explain SELECT `reference`
        FROM `product` p
        WHERE p.reference = "85450055675" LIMIT 1'
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | p     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 365442 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+

Describe the solution you'd like
product.reference column is VARCHAR(64) which is perfect for indexing, we should add an index on that column so imports and searches can benefit form it
same query but with index created

explain SELECT `reference`
    ->         FROM `product` p
    ->         WHERE p.reference = "85450055675" LIMIT 1;
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | p     | NULL       | ref  | reference     | reference | 195     | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+

as you can see it only scanned one row instead of all rows
I would even consider adding an index for supplier_reference

if proposal sounds reasonable i can open a PR

[khouloudbelguith]

Hi @Gamesh,

Thanks for your report.
Ping @PrestaShop/prestashop-product-team what do you think?
@Gamesh, Would you be willing to make a pull request on GitHub with your code suggestion?
https://github.com/PrestaShop/PrestaShop/tree/develop
Thank you!

[marionf]

@matks Do you think this could be done during the remigration of product page ?

[Gamesh]

@Gamesh, Would you be willing to make a pull request on GitHub with your code suggestion?

@khouloudbelguith yes, as i wrote:

if proposal sounds reasonable i can open a PR

as I understand I need to modify install-dev/data/db_structure.sql to add the index

But do I need to add the DB migration to the 1.7.7.1.sql file in install-dev/upgrade/sql?

[matks]

@matks Do you think this could be done during the remigration of product page ?

It is not related directly to the BO product page, but @Gamesh suggestions make sense.

@Gamesh indeed SQL schema modifications are done in the folder install-dev/upgrade, you can make a PR with your suggestion in 1.7.7.0.sql file as 1.7.7.0 has not been released yet.

@PrestaShop/prestashop-core-developers should'nt we have a full look at all SQL tables and, following @Gamesh idea, add indexes in columns than now look eligible for it ?

[ttoine]

+1 for indexes in tables

[Progi1984]

Fixed by #17914