Optimizar la combinación externa completa de MySQL para una gran cantidad de datos

Tenemos las siguientes tablas de mysql (simplificadas para ir directamente al grano)

CREATE TABLE `MONTH_RAW_EVENTS` ( `idEvent` int(11) unsigned NOT NULL, `city` varchar(45) NOT NULL, `country` varchar(45) NOT NULL, `ts` datetime NOT NULL, `idClient` varchar(45) NOT NULL, `event_category` varchar(45) NOT NULL, ... bunch of other fields PRIMARY KEY (`idEvent`), KEY `idx_city` (`city`), KEY `idx_country` (`country`), KEY `idClient` (`idClient`), ) ENGINE=InnoDB; CREATE TABLE `comstacktion_table` ( `idClient` int(11) unsigned DEFAULT NULL, `city` varchar(200) DEFAULT NULL, `month` int(2) DEFAULT NULL, `year` int(4) DEFAULT NULL, `events_profile` int(10) unsigned NOT NULL DEFAULT '0', `events_others` int(10) unsigned NOT NULL DEFAULT '0', `events_total` int(10) unsigned NOT NULL DEFAULT '0', KEY `idx_month` (`month`), KEY `idx_year` (`year`), KEY `idx_idClient` (`idClient`), KEY `idx_city` (`city`) ) ENGINE=InnoDB; 

MONTH_RAW_EVENTS contiene casi 20 millones de filas en las que el usuario realiza acciones en un website, tiene un tamaño de casi 4 GB

comstacktion_table tiene un resumen de clientes / ciudades por cada mes, lo usamos para mostrar statistics en un website en time real

Procesamos las statistics (de la primera tabla a la segunda) una vez por mes, y estamos tratando de optimizar una consulta que realice dicha operación (ya que hasta ahora estamos procesando todo en PHP, lo que requiere mucho time)

Aquí está la consulta que surgió, que parece hacer el trabajo cuando se usan subsets pequeños de datos, el problema que toma más de 6 horas en procesarse para el set completo de datos

 INSERT INTO comstacktion_table (idClient,city,month,year,events_profile,events_others) SELECT IFNULL(OTHERS.idClient,AP.idClient) as idClient, IF(IFNULL(OTHERS.city,AP.city)='','Others',IFNULL(OTHERS.city,AP.city)) as city, 01,2014, IFNULL(AP.cnt,0) as events_profile, IFNULL(OTHERS.cnt,0) as events_others FROM ( SELECT idClient,CONCAT(city,', ',country) as city,count(*) as cnt FROM `MONTH_RAW_EVENTS` WHERE `ts`>'2014-01-01 00:00:00' AND `ts`<='2014-01-31 23:59:59' AND `event_category`!='CLIENT PROFILE' GROUP BY idClient,city ) as OTHERS LEFT JOIN ( SELECT idClient,CONCAT(city,', ',country) as city,count(*) as cnt FROM `MONTH_RAW_EVENTS` WHERE `ts`>'2014-01-01 00:00:00' AND `ts`<='2014-01-31 23:59:59' AND `event_category`='CLIENT PROFILE' GROUP BY idClient,city ) as CLIPROFILE ON CLIPROFILE.city=OTHERS.city and CLIPROFILE.idClient=OTHERS.idClient UNION SELECT IFNULL(OTHERS.idClient,CLIPROFILE.idClient) as idClient, IF(IFNULL(OTHERS.city,CLIPROFILE.city)='','Others',IFNULL(OTHERS.city,CLIPROFILE.city)) as city, 01,2014, IFNULL(CLIPROFILE.cnt,0) as events_profile, IFNULL(OTHERS.cnt,0) as events_others FROM ( SELECT idClient,CONCAT(city,', ',country) as city,count(*) as cnt FROM `MONTH_RAW_EVENTS` WHERE `ts`>'2014-01-01 00:00:00' AND `ts`<='2014-01-31 23:59:59' AND `event_category`!='CLIENT PROFILE' GROUP BY idClient,city ) as OTHERS RIGHT JOIN ( SELECT idClient,CONCAT(city,', ',country) as city,count(*) as cnt FROM `MONTH_RAW_EVENTS` WHERE `ts`>'2014-01-01 00:00:00' AND `ts`<='2014-01-31 23:59:59' AND `event_category`='CLIENT PROFILE' GROUP BY idClient,city ) as CLIPROFILE ON CLIPROFILE.city=OTHERS.city and CLIPROFILE.idClient=OTHERS.idClient 

Lo que estamos tratando de hacer es un FULL Outer Join en Mysql por lo que el esquema básico de la consulta es como el propuesto aquí

¿Cómo podemos optimizar la consulta? hemos estado probando diferentes índices, cambiando cosas, pero después de 8 horas aún no hemos terminado de correr,

El server MySQL es una máquina dedicada Percona MySQL 5.5 con 2cpu, 2GB ram y disco SSD, optimizamos la configuration de dicho server usando herramientas Percona,

Cualquier ayuda sería muy apreciada,

Gracias

Está haciendo una UNIÓN que da como resultado un procesamiento DISTINTO.

Por lo general, es mejor reescribir un Full Join para un Left Join más las filas que no coinciden de un Right Join (si es apropiado 1: n join)

 OTHERS LEFT JOIN CLIPROFILE ON CLIPROFILE.city=OTHERS.city and CLIPROFILE.idClient=OTHERS.idClient union all OTHERS RIGHT JOIN CLIPROFILE ON CLIPROFILE.city=OTHERS.city and CLIPROFILE.idClient=OTHERS.idClient WHERE OTHERS.idClient IS NULL 

Además, puede materializar los resultados de las tablas derivadas en tablas temporales antes de unirlas, por lo tanto, el cálculo solo se realiza una vez (no sé si el optimizador de MySQL es lo suficientemente inteligente como para hacerlo automáticamente).

Además, podría ser más eficiente agrupar y unir en ciudad / país como columnas separadas y hacer CONCAT (ciudad, ',', país) como ciudad en el paso exterior.

    Intereting Posts