Estoy intentando unir dos tablas, products y products_markets. Mientras que products tiene menos de un millón de registros, product_markets está más cerca de los 20 millones de registros. Los datos se han modificado, por lo que puede haber un error tipográfico o dos en las tablas de creación de esquemas:

CREATE TABLE `products_markets` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `product_id` int(10) unsigned NOT NULL,
  `country_code_id` int(10) unsigned NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_index` (`product_id`,`country_code_id`)
) ENGINE=InnoDB AUTO_INCREMENT=21052102 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `products` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `manufacturer_id` int(10) unsigned NOT NULL,
  `department_id` int(10) unsigned NOT NULL,
  `code` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `popularity` int(11) DEFAULT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `value` bigint(20) unsigned NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `products_code_unique` (`code`),
  KEY `products_department_id_foreign` (`department_id`),
  KEY `products_manufacturer_id_foreign` (`manufacturer_id`),
  CONSTRAINT `products_department_id_foreign`
       FOREIGN KEY (`department_id`) REFERENCES `departments` (`id`),
  CONSTRAINT `products_manufacturer_id_foreign`
       FOREIGN KEY (`manufacturer_id`) REFERENCES `manufacturers` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=731563 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Estoy tratando de devolver 50 registros de los productos más populares disponibles en un país específico y me encuentro con tiempos de alrededor de 50 segundos, lo que parece más alto de lo esperado.

Probé algunas consultas diferentes sin éxito:

select  `products_markets`.`product_id`
    from  products_markets
    left join  
        ( SELECT  products.id, products.popularity
            from  products
        ) p  ON p.id = products_markets.product_id
    where products_markets.country_code_id = 121
    order by  `popularity` desc, `p`.`id` asc
    limit  50 

Y

select  `products`.*
    from  `products`
    where  products.id in (
        SELECT  product_id
            from  products_markets
            where  products_markets.country_code_id = 121
                          )
    group by  `products`.`name`, `products`.`manufacturer_id`
    order by  `popularity` desc, `products`.`id` asc
    limit  50 

La explicación de esta consulta es:

id  select_type  table              type possible_keys key           key_len refs             rows              extra
1   PRIMARY      products           ALL  PRIMARY       NULL          NULL    NULL             623848            Using temporary; Using filesort
1   PRIMARY      products_markets   ref  unique_index  unique_index  4       main.products.id 14                Using where; Using index; FirstMatch(products)

Una opción que me entretiene es dividir products_markets en tablas individuales para cada país para reducir la consulta. Intenté agregar más memoria al servidor sin mucho éxito. ¿Alguien puede identificar algo notoriamente incorrecto en el diseño / consulta de la base de datos?

¿Qué otras opciones están disponibles para hacer que esta consulta sea una fracción de sus ~ 50 segundos actuales?

3
Alex Harris 24 ago. 2016 a las 19:41

2 respuestas

La mejor respuesta

Deshazte de id en products_markets y agrega

PRIMARY KEY(country_code_id, product_id)

Luego, elimine la clave UNIQUE a menos que sea necesaria para alguna otra consulta.

Esto reducirá significativamente la huella de disco de esa mesa grande, lo que potencialmente acelerará todas las consultas que la toquen.

Y ayudará con la reformulación sugerida por Hamaza.

1
Rick James 24 ago. 2016 a las 18:33

Pruebe lo que quise decir con esta consulta: primero seleccione todos los productos del país especificado de la tabla products_market, luego seleccione esos productos de la tabla de productos por popularidad y limítelos a 50. Trate de no hacer productos. * Y seleccione solo los campos cuyo se necesitan datos.

select  products_markets.product_id, products_markets.county_code_id,
        products.*
    from  products_markets,products
    where  products_markets.country_code_id = 121
      and  products_markets.product_id=products.id
    group by  `products`.`name`, `products`.`manufacturer_id`
    order by  `products_markets.popularity` desc, `products`.`id` asc
    limit  50
0
Rick James 24 ago. 2016 a las 18:24