What indexes to improve performance of JOIN and GROUP BY

Question

I have setup some tables and ran a query. However in my explain it would appear the SQL results in a temporary table being generated ( I assume this is because of the GROUP BY)

I have added some indexes to speed up the query but wondering if there was a way to stop the use of a temporary table and if there is any other way I can speed my query up using indexes?

CartData

CREATE TABLE `cartdata` (
    `IDCartData` INT(11) NOT NULL AUTO_INCREMENT,
    `CartOrderref` VARCHAR(25) NOT NULL DEFAULT '',
    `UserID` INT(11) NOT NULL DEFAULT '0',
    `LastUpdate` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE 
    CURRENT_TIMESTAMP,
    `ManualContactName` VARCHAR(100) NOT NULL DEFAULT '',
    `ManualOrderConfirmationEmail` VARCHAR(100) NOT NULL DEFAULT '',
    PRIMARY KEY (`IDCartData`),
    INDEX `CartOrderref` (`CartOrderref`)
)

CartSplitData

    CREATE TABLE `cartsplitdata` (
        `IDCartSupplierData` INT(11) NOT NULL AUTO_INCREMENT,
        `IDCartData` INT(11) NOT NULL DEFAULT '0',
        `supplierid` INT(11) NOT NULL DEFAULT '0',
        `DeliveryDate` DATE NOT NULL DEFAULT '2000-01-01',
        `AccountNumber` VARCHAR(50) NOT NULL DEFAULT '',
        `ManualOrderref` VARCHAR(50) NOT NULL DEFAULT '',
        `lastupdate` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (`IDCartSupplierData`),
        INDEX `cartdatasupplierid` (`IDCartData`, `supplierid`)
    )

My sample query

        EXPLAIN SELECT max(CartData.idCartDATA) AS idCartDATA , CartData.*, CartSplitData.*
        FROM CartData
        JOIN CartSplitData ON CartSplitData.IDCartDATA = CartDATA.IDCartData
        WHERE  CartData.CartOrderref = 'XXXXXXXXX'
        group by CartSplitData.SUPPLIERID

Explain of query results

Your query doesn't make sense. The `group by` and `select` are incompatible. — Gordon Linoff, Oct 25 '18 at 11:33
Please see: https://stackoverflow.com/questions/41887460/select-list-is-not-in-group-by-clause-and-contains-nonaggregated-column-inc — Madhur Bhaiya, Oct 25 '18 at 11:34
@GordonLinoff Yes... I can see that now.... I have a flaw in my logic and appreciate you spotting this. — user1002794, Oct 25 '18 at 11:49
The answer further down actually does achieves exactly what I wanted so glad I didn't delete the request! — user1002794, Oct 25 '18 at 14:05

score 5 · Answer 1 · answered Oct 25 '18 at 12:30

Pro tip Avoid SELECT * or SELECT table.* in performance-sensitive queries. Instead select, by name, the columns you actually need to use

Pro tip MySQL has a notorious nonstandard extension to GROUP BY which you are using, and possibly misusing. Read this. https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.html If you followed the first pro tip, following the second would be much easier.

Pro tip Avoid "throwing in" lots of single column indexes in hopes of accelerating your queries. Instead, create indexes, often compound indexes, matching the needs of your actual query. Read this https://use-the-index-luke.com .

Pro tip Using temporary; using filesort appearing in EXPLAIN output is not necessarily bad. It simply means that the query engine has to cache a partial result set before returning it. The temporary thing isn't an actual table, it's a RAM structure. If it is so big it swamps RAM, MySQL will spill it to disk. But yours isn't.

All that being said, let's refactor your query. I guess you want to retrieve the rows with the largest idCartDATA value for each CartSplitData.SUPPLIERID.

So let's write that as a subquery.

                  SELECT max(IDCartDATA) AS IDCartDATA, SUPPLIERID
                    FROM CartSplitData
                   GROUP BY SUPPLIERID

This query can be sped up, dramatically, by putting a compound index on CartSplitData: (SUPPLIERID, IDCartDATA).

Next, let's rewrite your main query to find the rows matching the ids in that subquery.

SELECT CartData.*             /* * hammers performance */
       CartSplitData.*        /* * hammers performance */
  FROM CartData
  JOIN CartSplitData ON CartSplitData.IDCartDATA = CartDATA.IDCartData
  JOIN (
                  SELECT max(IDCartDATA) AS IDCartDATA, SUPPLIERID
                    FROM CartSplitData
                   GROUP BY SUPPLIERID
       )x ON x.SUPPLIERID = CartSplitData.SUPPLIERID
         AND x.IDCartData = CartSplitData.IDCartData
 WHERE CartData.CartOrderref = 'XXXXXXXXX'

Your index on CartData.CartOrderref will help this outer query, as will the compound index created ^^^.

I generally avoid .* SELECTS for clarity but in this instance I didn't as I was in a rush. However now that I know it can impact performance I will alias hence forth. — user1002794, Oct 25 '18 at 13:08
Thanks very much for your full example. I will implement your suggestion. — user1002794, Oct 25 '18 at 13:09

What indexes to improve performance of JOIN and GROUP BY

1 Answers1

Linked