If not already done, load the model. You can use the session variable for the model that is valid for the duration of the connection. Alternatively, you can use the model handle previously set. For the option to set the user name, you can set it to NULL.

The following example uses the session variable.

mysql> CALL sys.ML_MODEL_LOAD(@model, NULL);

The following example uses the model handle.

mysql> CALL sys.ML_MODEL_LOAD('recommendation_use_case', NULL);

Make predictions for the test dataset by using the ML_PREDICT_TABLE routine.

mysql> CALL sys.ML_PREDICT_TABLE(table_name, model_handle, output_table_name), [options]);

Replace table_name, model_handle, and output_table_name with your own values. Add options as needed.

The following example runs ML_PREDICT_TABLE on the testing dataset previously created and sets the topk parameter to 2, so only two similar items are generated.

mysql> CALL sys.ML_PREDICT_TABLE('recommendation_data.testing_dataset', @model, 'recommendation_data.similar_item_recommendations', JSON_OBJECT('recommend', 'items_to_items', 'topk', 2));

Where:

Query the output table to review the top two similar items for each item in the output table.

mysql> SELECT * from similar_item_recommendations;
+---------+---------+--------+----------------------------------------------------------------------------+
| user_id | item_id | rating | ml_results                                                                 |
+---------+---------+--------+----------------------------------------------------------------------------+
| 1       | 2       |    4.0 | {"predictions": {"item_id": ["14", "10"], "similarity": [0.9831, 0.965]}}  |
| 1       | 4       |    7.0 | {"predictions": {"item_id": ["9", "6"], "similarity": [0.6838, 0.6444]}}   |
| 1       | 6       |    1.5 | {"predictions": {"item_id": ["8", "17"], "similarity": [0.8991, 0.8412]}}  |
| 1       | 8       |    3.5 | {"predictions": {"item_id": ["6", "17"], "similarity": [0.8991, 0.7942]}}  |
| 10      | 18      |    1.5 | {"predictions": {"item_id": ["16", "12"], "similarity": [0.9869, 0.9464]}} |
| 10      | 2       |    6.5 | {"predictions": {"item_id": ["14", "10"], "similarity": [0.9831, 0.965]}}  |
| 10      | 5       |    3.0 | {"predictions": {"item_id": ["16", "2"], "similarity": [0.9036, 0.8586]}}  |
| 10      | 6       |    5.5 | {"predictions": {"item_id": ["8", "17"], "similarity": [0.8991, 0.8412]}}  |
| 2       | 1       |    5.0 | {"predictions": {"item_id": ["15", "17"], "similarity": [0.8462, 0.7966]}} |
| 2       | 3       |    8.0 | {"predictions": {"item_id": ["19", "13"], "similarity": [0.9826, 0.8851]}} |
| 2       | 5       |    2.5 | {"predictions": {"item_id": ["16", "2"], "similarity": [0.9036, 0.8586]}}  |
| 2       | 7       |    6.5 | {"predictions": {"item_id": ["11", "15"], "similarity": [0.6959, 0.6724]}} |
| 3       | 18      |    7.0 | {"predictions": {"item_id": ["16", "12"], "similarity": [0.9869, 0.9464]}} |
| 3       | 2       |    3.5 | {"predictions": {"item_id": ["14", "10"], "similarity": [0.9831, 0.965]}}  |
| 3       | 5       |    6.5 | {"predictions": {"item_id": ["16", "2"], "similarity": [0.9036, 0.8586]}}  |
| 3       | 8       |    2.5 | {"predictions": {"item_id": ["6", "17"], "similarity": [0.8991, 0.7942]}}  |
| 4       | 1       |    5.5 | {"predictions": {"item_id": ["15", "17"], "similarity": [0.8462, 0.7966]}} |
| 4       | 3       |    8.5 | {"predictions": {"item_id": ["19", "13"], "similarity": [0.9826, 0.8851]}} |
| 4       | 6       |    2.0 | {"predictions": {"item_id": ["8", "17"], "similarity": [0.8991, 0.8412]}}  |
| 4       | 7       |    5.5 | {"predictions": {"item_id": ["11", "15"], "similarity": [0.6959, 0.6724]}} |
| 5       | 12      |    5.0 | {"predictions": {"item_id": ["18", "16"], "similarity": [0.9464, 0.9454]}} |
| 5       | 2       |    7.0 | {"predictions": {"item_id": ["14", "10"], "similarity": [0.9831, 0.965]}}  |
| 5       | 4       |    1.5 | {"predictions": {"item_id": ["9", "6"], "similarity": [0.6838, 0.6444]}}   |
| 5       | 6       |    4.0 | {"predictions": {"item_id": ["8", "17"], "similarity": [0.8991, 0.8412]}}  |
| 6       | 3       |    6.0 | {"predictions": {"item_id": ["19", "13"], "similarity": [0.9826, 0.8851]}} |
| 6       | 5       |    1.5 | {"predictions": {"item_id": ["16", "2"], "similarity": [0.9036, 0.8586]}}  |
| 6       | 7       |    4.5 | {"predictions": {"item_id": ["11", "15"], "similarity": [0.6959, 0.6724]}} |
| 6       | 8       |    7.0 | {"predictions": {"item_id": ["6", "17"], "similarity": [0.8991, 0.7942]}}  |
| 7       | 1       |    6.5 | {"predictions": {"item_id": ["15", "17"], "similarity": [0.8462, 0.7966]}} |
| 7       | 4       |    3.0 | {"predictions": {"item_id": ["9", "6"], "similarity": [0.6838, 0.6444]}}   |
| 7       | 5       |    5.5 | {"predictions": {"item_id": ["16", "2"], "similarity": [0.9036, 0.8586]}}  |
| 7       | 9       |    8.0 | {"predictions": {"item_id": ["1", "4"], "similarity": [0.7721, 0.6838]}}   |
| 8       | 2       |    8.5 | {"predictions": {"item_id": ["14", "10"], "similarity": [0.9831, 0.965]}}  |
| 8       | 4       |    2.5 | {"predictions": {"item_id": ["9", "6"], "similarity": [0.6838, 0.6444]}}   |
| 8       | 6       |    5.0 | {"predictions": {"item_id": ["8", "17"], "similarity": [0.8991, 0.8412]}}  |
| 8       | 9       |    3.5 | {"predictions": {"item_id": ["1", "4"], "similarity": [0.7721, 0.6838]}}   |
| 9       | 1       |    5.0 | {"predictions": {"item_id": ["15", "17"], "similarity": [0.8462, 0.7966]}} |
| 9       | 3       |    8.0 | {"predictions": {"item_id": ["19", "13"], "similarity": [0.9826, 0.8851]}} |
| 9       | 7       |    2.5 | {"predictions": {"item_id": ["11", "15"], "similarity": [0.6959, 0.6724]}} |
| 9       | 8       |    5.5 | {"predictions": {"item_id": ["6", "17"], "similarity": [0.8991, 0.7942]}}  |
+---------+---------+--------+----------------------------------------------------------------------------+
40 rows in set (0.0401 sec)

Review the recommended similar items in the ml_results column next to item_id. For example, for item 2, items 14 and 10 are the top items predicted to be most similar. Review the similarity values in the ml_results column next to similarity to review the how similar each item is. For example, item 14 has a similarity value of 0.9831 to item 2, and item 10 has a similarity value of 0.965.

Alternatively, if you do not want to generate an entire table of similar items, you can run ML_PREDICT_ROW to specify an item to recommend similar items for.

mysql> SELECT sys.ML_PREDICT_ROW(input_data, model_handle), [options]);

Replace input_data and model_handle with your own values. Add options as needed.

The following example runs ML_PREDICT_ROW and specifies item 2 with a limit of two recommended similar items.

mysql> SELECT sys.ML_PREDICT_ROW('{"item_id": "2"}', @model,  JSON_OBJECT('recommend', 'items_to_items', 'topk', 2));
+-----------------------------------------------------------------------------------------------------------+
| sys.ML_PREDICT_ROW('{"item_id": "2"}', @model,  JSON_OBJECT('recommend', 'items_to_items', 'topk', 2))    |
+-----------------------------------------------------------------------------------------------------------+
| {"item_id": "2", "ml_results": {"predictions": {"item_id": ["14", "10"], "similarity": [0.9831, 0.965]}}} |
+-----------------------------------------------------------------------------------------------------------+
1 row in set (0.8227 sec)

The similar items of 14 and 10 and similarity values are the same as the one in the output table previously created.