Create MySQL view between one-to-many tables

Question

I have the following two tables, a product has many products names:

+----------+
| products |
+----------+
| id | sku |
+----+-----+
|  1 | 123 |
|  2 | 456 |
|  3 | 789 |
+----+-----+

+-------------------------------+
| product_names                 |
+------------+--------+---------+
| product_id | locale | name    |
+------------+--------+---------+
|          1 |     en |   paper |
|          1 |     it |   carta |
|          2 |     en |    rock |
|          2 |     it |   sasso |
|          3 |     en | scissor |
|          3 |     it | forbice |
+------------+--------+---------+

What I need, if possible in MySQL, is to create a view that generate a virtual table like this (locales are fixed):

+------------------------------+
| products_with_names          |
+----------+---------+---------+
| id | sku | name_en | name_it |
+----+-----+---------+---------+
|  1 | 123 |   paper |   carta |
|  2 | 456 |    rock |   sasso |
|  3 | 789 | scissor | forbice |
+----+-----+---------+---------+

Is possible to create this kind of view? Is it a good idea in terms of performance?

see https://stackoverflow.com/a/62712027/5193536 you can do it as view — nbk, Sep 07 '20 at 23:13

score 1 · Answer 1 · answered Sep 07 '20 at 23:23

Mysql Views do not really offer any performance increases - The underlying queries that make up the view are run when the View is queried and any issues with will still exists.

Views in Mysql have only a few real uses that I know of , which are mainly to do with hiding / reformatting the tables for ease of use for those not familiar with the schema

eg. Limiting the view of a table by hiding columns Renaming columns to hide poor design issues ie nonsensical column names. Joining tables as one View for the user

So there's no problem in creating the View if you want to simplify things , but if performance is your main concern then your best option is a to correctly index tables used in Views for best performance as views cannot be indexed.

Ok, thanks! Do you also know how to create the view that I need? — Pioz, Sep 07 '20 at 23:26

Bill Karwin · Accepted Answer · 2020-09-08T00:17:23.760

You can do this by joining to the product names twice:

CREATE OR REPLACE VIEW products_with_names 
AS
  SELECT p.id, p.sku, e.name AS name_en, i.name AS name_it
  FROM products AS p
  LEFT OUTER JOIN product_names AS en ON en.product_id = p.id AND en.locale='en'
  LEFT OUTER JOIN product_names AS it ON it.product_id = p.id AND it.locale='it';

Or by using a pivot-style projection:

CREATE OR REPLACE VIEW products_with_names 
AS
  SELECT p.id, p.sku, 
    MAX(CASE n.local WHEN 'en' THEN n.name END) AS name_en,
    MAX(CASE n.local WHEN 'it' THEN n.name END) AS name_it
  FROM products AS p
  LEFT OUTER JOIN product_names AS n ON en.product_id = p.id
  GROUP BY p.id;

You might ask, what if I add more locales after defining the view? How can the view return as many columns as the number of distinct locals?

That is not possible in SQL. Any query must have all the columns defined at the time it is prepared, which is before it has read any data.

You must specify the locales in the query when you define the view. If you add a new locale, you must redefine the view with a new column.

Thanks, what do you think is better in terms of performance: double join or pivot-style projection? — Pioz, Sep 08 '20 at 05:56
It depends on indexes, number of rows of data, your server specifications, etc. Basically, either style could be better for your case. As a software engineer on this project, you should take it as your responsibility to test both solutions with your data on your server to make that determination. — Bill Karwin, Sep 08 '20 at 06:39

Create MySQL view between one-to-many tables

2 Answers2