Implementing Coupon Template Retrieval by Product ID in MySQL: Fuzzy Search vs Multi‑Value Index

When merchants need to search for products by name in a limited‑size catalog, a simple fuzzy search using WHERE columnName LIKE '%inputName%' can be sufficient, but this technique is often misused for more complex scenarios such as locating coupon templates associated with a given product ID.

In a coupon‑template management backend, each template may be linked to multiple products (1:N relationship). Two common implementation options are:

Using Elasticsearch for full‑text search.

Using MySQL directly.

For small data volumes and simple conditions, the MySQL solution is preferable. The article presents two MySQL‑based approaches.

Solution 1: Fuzzy Search

Store the related product IDs as a comma‑separated string in related_product_ids and query with LIKE:

WHERE related_product_Ids LIKE '%${inputProductId}%'

This can return false positives (e.g., searching for 11 matches 110 , 111 , 112 ). To reduce errors, add a trailing comma to the stored string and adjust the query:

WHERE related_product_Ids LIKE '%${inputProductId},%'

Even with this tweak, searching for 12 still matches 110,111,112 . The final improvement surrounds the list with leading and trailing commas:

,110,111,112,

WHERE related_product_Ids LIKE '%,${inputProductId},%'

While more reliable, this method remains a workaround.

Solution 2: Multi‑Value Index

MySQL 8.0 supports multi‑value indexes on JSON columns, offering better performance and accuracy.

What is a Multi‑Value Index?

A regular index maps one row to one index entry; a multi‑value index creates one index entry for each element of a JSON array, allowing direct lookup of any individual value.

How to Use It

1) Create a JSON column to store the product IDs:

ALTER TABLE coupon_template ADD COLUMN related_product_ids JSON DEFAULT NULL;

2) Insert a JSON array:

INSERT INTO coupon_template (related_product_ids) VALUES ('[110,111,112]');

3) Add a multi‑value index on the JSON array elements:

ALTER TABLE coupon_template ADD INDEX `relatedProductIdsIndex` ((CAST(json_extract(`related_product_ids`, '$[*]') AS UNSIGNED ARRAY)));

The index expression extracts each element (using $[*] ) and casts it to an unsigned array, generating separate index entries for 110, 111, and 112.

For a JSON object, the expression would target a specific key, e.g., $.ids . An alternative syntax using the arrow operator creates a secondary index without explicit json_extract :

ALTER TABLE coupon_template ADD INDEX ids_ext_index ((CAST(ids_ext->'$.ids' AS UNSIGNED ARRAY)));

Querying the Multi‑Value Index

Use the MEMBER OF operator to test membership:

SELECT * FROM coupon_template WHERE 110 MEMBER OF (ids_ext->'$.ids');

EXPLAIN shows that MySQL utilizes the created index, confirming efficient execution.

Conclusion

MySQL 8.0, released in 2018, introduces JSON types, window functions, multi‑value indexes, and instant online DDL. Since MySQL 5.7 reached end‑of‑life in late 2023, migrating to 8.0 is recommended to take advantage of these features and improve query reliability for scenarios like coupon‑template retrieval.