All my skus have the following format: XY-NAME-NUMBER, e.g BC-FLUID-0003.

Now when I try to search that sku via catalog search in the frontend, I get all kinds of results, much more than I'd like to have. I enabled the SQL-Query Log and found out, that Magento changes my sku in the search to "bc fluid* 0003*" and therefore finds products which are also named something with fluid or also end with 0003.

What I want is to search for BC-FLUID-0003*, so without taking away the hyphens.

How could I do that?

  • Are you using Elasticsearch or MySQL?? – Rus0 Aug 6 at 5:20

This is because the internal handling of the parameter by Magento 2 Query Builder. The "-" symbol is prohibited in a search query (in Magento\Framework\Search\Adapter\Mysql\Query\Builder\Match class). So it produces three small queries, in your case the final query looks similar to this:

SELECT `main_select`.`entity_id`, SUM(score) AS `relevance` FROM (SELECT DISTINCT  `eav_index`.`entity_id`, (((0)) * 1 + LEAST((MATCH (data_index) AGAINST ('bc fluid* 0003*' IN BOOLEAN MODE)), 1000000) * POW(2, search_weight)) AS `score` FROM `catalog_product_index_eav` AS `eav_index`
 INNER JOIN `catalogsearch_fulltext_scope1` AS `search_index` ON eav_index.entity_id = search_index.entity_id
 INNER JOIN `catalog_eav_attribute` AS `cea` ON search_index.attribute_id = cea.attribute_id
 INNER JOIN `cataloginventory_stock_status` AS `stock_index` ON stock_index.product_id = eav_index.entity_id AND `stock_index`.`website_id` = 0 AND `stock_index`.`stock_status` = 1 AND `stock_index`.`stock_id` = 1 WHERE (eav_index.store_id = '1') AND (`eav_index`.`attribute_id` = 96 AND `eav_index`.`value` in ('3', '4') AND `eav_index`.`store_id` = '1') AND (MATCH (data_index) AGAINST ('bc fluid* 0003* fn' IN BOOLEAN MODE))) AS `main_select` GROUP BY `entity_id` ORDER BY `relevance` DESC, `entity_id` DESC
 LIMIT 10000

To be able to find the product by SKU, I would suggest to make an around plugin like in this tread: How to get specific sku product on search in magento 2?

| improve this answer | |

May be it seems mysql configuration issue. You can add below line in /etc/mysql/mysql.conf.d/mysqld.cnf file


Hope this will help you!

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.