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, 2020 at 5:20

2 Answers 2


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?


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!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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