Search code examples
mysqlindexinggroup-bykeyinner-join

MySQL slow query when using GROUP BY

I'm using 2 tables to join packages to shippings:

packages

CREATE TABLE `packages` (
  `package` int(11) NOT NULL,
  `shipping` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

ALTER TABLE `packages`
  ADD PRIMARY KEY (`package`,`shipping`);
COMMIT;

shippings

CREATE TABLE `shippings` (
  `id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

ALTER TABLE `shippings`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `shippings`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;COMMIT;

the problem is when I try to join packages to shippings and GROUP BY e.g. for counting packages in each shipping:

SELECT s.id,COUNT(*) packages
FROM shippings s
INNER JOIN packages p ON s.id=p.shipping
GROUP BY s.id

with 5K shippings and 10K packages records the query take more than 10s to execute, maybe I'm missing some keys or indexes?

Solution

  • You might want to add an index for the packages.shipping. The primary key index that you have will not be used in the query as it has the package as the first level.

    Alternatively you can change the order of the primary key columns in the packages-table, but that depends on what other queries use the table.