, I need your help and knowledge to be able to improve this view that I present in MySQL. It's taking a long time and I want to reduce load times.
I have little handling of Sql and T-Sql. Could you help me with the format of this view by applying good practices or improving the design of it?
Greetings and thanks!
SELECT
ENCA.ID AS ID,
ENCA.FECHA AS DATE,
CONCAT(P.SKU,'-',P.NOMRBE,' ',P.MARCA_REAL) AS PRODUCT,
(SELECT U.EMAIL FROM USERS U WHERE U.ID=ENCA.ID_USER) AS EMAIL_USER,
C.DESCRIPCION AS CHANEL,
PDV.NOMBRE AS NAME_PDV,
DIST.NOMBRE AS NAME_DISTRIBUTION,
CAT.DESCRIPCION AS CATEGORY,
RTA_MIX.ID_PDV AS ID_PDV,
RTA_MIX.PRECIO_MIN AS MIN_PRICE,
RTA_MIX.PRECIO_MAX AS MAX_PRICE,
RTA_MIX.ANSWER AS ANSWER,
TIPO_CLIENTE.DESCRIPCION AS REGION,
RTA_ENC.ANSWER AS POP,
ENCA.ID AS ID_RELEASE,
(SELECT U.USUARIO_NOMBRE FROM USERS U WHERE U.ID=ENCA.ID_USER) AS RELEVANT,
TIPO_PDV.DESCRIPTION AS TYPEPDV,
CONCAT(PRODUCTO.SKU,' (',PRODUCTO.MARCA_REAL,' ',PRODUCTO.NOMBRE,')') AS PRODUCT,
CONCAT(ENCA.FECHA,'-',PDV.NOMBRE) AS DATEPDV
FROM
ENCABEZADO_RESULTADO_ENCUESTA ENCA
JOIN RESPUESTA_MIX RTA_MIX
ON ENCA.ID=RTA_MIX.ID_ENCABEZADO
JOIN PRODUCTO P
ON P.ID=RTA_MIX.ID_PROCDUCTO
JOIN CANAL C
ON C.ID=ENCA.ID_CANAL
JOIN CATEGORIA CAT
ON CAT.ID=P.ID_CATEGORIA
JOIN PDV_TRADICIONAL PDV
ON PDV.ID=RTA_MIX.ID_PDV
JOIN DISTRIBUIDOR DIST
ON DIST.ID=PDV.ID_DISTRIBUIDOR
JOIN RESPUESYA_ENCUESTA RTA_ENC
ON RTA_ENC.ID_ENCABEZADO=ENCA.ID AND RTA_ENC.ID_TIPO_ENCUESTA='41'
JOIN TIPO_CLIENTE TIPO_CLIENTE
ON TIPO_CLIENTE.ID=PDV.ID_TIPO_CLIENTE
JOIN TIPO_PDV TIPO_PDV
ON TIPO_PDV.ID = RTA_MIX.ID_TIPO_PDV
JOIN PRODUCTO PRODUCTO
ON PROFUCTO.ID=RTA_MIX.ID_PRODUCTO
WHERE
ENCA.ID_PROCESO = 1 AND ENCA.ID_CANAL = 1
UNION ALL
SELECT
ENCA.ID AS ID,
ENCA.FECHA AS DATE,
CONCAT(P.SKU,'-',P.NOMRBE,' ',P.MARCA_REAL) AS PRODUCT,
(SELECT U.EMAIL FROM USERS U WHERE U.ID=ENCA.ID_USER) AS EMAIL_USER,
C.DESCRIPCION AS CANAL,
CONCAT(PDV.NOMBRE,'-',PDV.CODIGO_CLIENTE) AS NAME_PDV,
CAD.DESCRIPCION AS NAME_DIST,
CAT.DESCRIPCION AS CATEGORY,
RTA_MIX.ID_PDV AS ID_PDV,
RTA_MIX.PRECIO_MIN AS MIN_PRICE,
RTA_MIX.PRECIO_MAX AS MAX_PRICE,
RTA_MIX.ANSWER AS ANSWER,
CADENA.DESCRIPCION AS REGION,
RTA_ENC.ANSWER AS POP,
ENCA.ID AS ID_RELEASE,
(SELECT U.USUARIO_NOMBRE FROM USERS U WHERE U.ID=ENCA.ID_USER) AS RELEVANT,
TIPO_PDV.DESCRIPCION AS PDVTYPE,
CONCAT(PRODUCTO.SKU,'(',PRODUCTO.MARCA_REAL,' ',PRODUCTO.NOMBRE,')') AS PROD,
CONCAT(ENCA.FECHA,'-',PDV.NOMBRE) AS DATEPDV
FROM
ENCABEZADO_RESULTADO_ENCUESTA ENCA
JOIN RESPUESTA_MIX RTA_MIX
ON ENCA.ID = RTA_MIX.ID_ENCABEZADO
JOIN PRODUCTO P
ON P.ID=RTA_MIX.ID_PRODUCTO
JOIN CANAL C
ON C.ID=ENCA.ID_CANAL
JOIN CATEGORIA CAT
ON CAT.ID=P.ID_CATEGORIA
JOIN PDV_MODERNO PDV
ON PDV.ID=RTA_MIX.ID_PDV
JOIN CADENA CAD
ON CAD.ID=PDV.ID_CADENA
JOIN RESPUESTA_ENCUESTA RTA_ENC
ON RTA_ENC.ID_ENCABEZADO = ENCA.ID AND RTA_ENC.ID_TIPO_ENCUESTA='41'
JOIN CADENA
ON CADENA.ID=PDV.ID_CADENA
JOIN TIPO_PDV
ON TIPO_PDV.ID=RTA_MIX.ID_TIPO_PDV
JOIN PRODUCTO
ON PRODUCTO.ID=RTA_MIX.ID_PRODUCTO
WHERE
ENCA.ID_PROCESO = 1 AND ENCA.ID_CANAL = 0