What can I do to optimise this MariaDB query?

Question

The query has been anonymised for sharing on this site; the comparative example is that we are a book seller who needs to cross-reference new books, against an archive of ALL books and identify category matches. Individually, each of the following queries returns the correct data, but the sheer volume of data being handled, results in an extremely slow query.

CREATE OR REPLACE TEMPORARY TABLE warehouse.tmp_newBooks
(INDEX rowID (_rowid), INDEX joinIdx (publisherID, storePostcode, authorName, publisherReference, bookTitle)) AS
SELECT
    d._rowid,
    d.linkID,
    cs.publisherID,
    d.publisherReference,
    d._createdDate,
    d.authorName,
    d.address,
    d.storePostcode,
    d.bookTitle
FROM library.books d
JOIN library.publishers cs ON d.publishersID = cs._rowid
WHERE DATE(d._createdDate) = DATE(DATE_ADD(NOW(),INTERVAL -1 DAY))
     AND d.available = 'Y'
    AND d.linkID IS NULL;
    
    
CREATE OR REPLACE TEMPORARY TABLE warehouse.tmp_allBooks
(INDEX rowID (_rowid), INDEX joinIdx (publisherID, storePostcode, authorName, publisherReference, bookTitle)) AS
SELECT
    d._rowid,
    d.linkID,
    cs.publisherID,
    d.publisherReference,
    d._createdDate,
    d.authorName,
    d.address,
    d.storePostcode,
    d.bookTitle
FROM library.books d
JOIN library.publishers cs ON d.publishersID = cs._rowid;

SELECT 
    d._rowid,
    d.linkID,
    case 
        when d.bookTitle = dd.bookTitle AND IFNULL(d.storePostcode,'') <> IFNULL(dd.storePostcode,'') AND d.authorName = dd.authorName then 'CATEGORY A'
        when d.publisherReference = dd.publisherReference AND IFNULL(d.storePostcode,'') <> IFNULL(dd.storePostcode,'') AND d.authorName <> dd.authorName  then 'CATEGORY B'
        WHEN d.storePostcode = dd.storePostcode AND d.authorName = dd.authorName AND d.publisherReference <> dd.publisherReference then 'CATEGORY C'
        when d.storePostcode = dd.storePostcode AND d.authorName = dd.authorName AND d.publisherReference = dd.publisherReference AND d.bookTitle = dd.bookTitle then 'CATEGORY D'
        when d.storePostcode = dd.storePostcode AND d.authorName = dd.authorName AND d.publisherReference = dd.publisherReference AND d.bookTitle <> dd.bookTitle then 'CATEGORY E'
        WHEN d.storePostcode = dd.storePostcode AND d.authorName = dd.authorName AND d.publisherReference = dd.publisherReference then 'CATEGORY F'
        when d.authorName = dd.authorName AND d.publisherReference = dd.publisherReference  then 'CATEGORY G'
        when d.publisherReference = dd.publisherReference AND d.storePostcode = dd.storePostcode AND d.authorName <> dd.authorName  then 'CATEGORY H'
        when d.publisherReference = dd.publisherReference AND d.storePostcode <> dd.storePostcode AND d.authorName <> dd.authorName  then 'CATEGORY I'
        when d.bookTitle = dd.bookTitle AND d.storePostcode = dd.storePostcode AND d.authorName <> dd.authorName then 'CATEGORY J'
        when d.bookTitle = dd.bookTitle AND d.storePostcode = dd.storePostcode then 'CATEGORY K'
        when d.bookTitle = dd.bookTitle AND d.storePostcode = dd.storePostcode AND d.authorName = dd.authorName then 'CATEGORY L'
        when d.bookTitle = dd.bookTitle AND d.authorName = dd.authorName AND d.storePostcode <> dd.storePostcode then 'CATEGORY M'
        when d.bookTitle = dd.bookTitle AND d.authorName <> dd.authorName AND d.storePostcode <> dd.storePostcode then 'CATEGORY N'
        END AS 'Result'
FROM warehouse.tmp_allBooks dd 
LEFT JOIN warehouse.tmp_newBooks d ON
    d._rowid <> dd._rowid AND
    d.publisherID = dd.publisherID AND
   ((d.storePostcode = dd.storePostcode AND d.authorName = dd.authorName) or d.publisherReference = dd.publisherReference OR d.bookTitle = dd.bookTitle)
GROUP BY d._rowid;

tmp_newBooks contains 626 rows tmp_allBooks contains 44,697 rows when limited for testing the select query takes 15 minutes to run, if it doesn't crash

In the production environment, tmp_allBooks would contain a little over 4 million rows

An explain on the last query has

select_type	table	type	possible_keys	key	key_len	ref	rows	extra
SIMPLE	dd	ALL	(null)	(null)	(null)	(null)	44424	Using temporary; Using filesort
SIMPLE	d	ref	joinIdx	joinIdx	5	warehouse.dd.branchID	1	Using where

Answer 1

This is not Sargable;

DATE(d._createdDate) = DATE(DATE_ADD(NOW(),INTERVAL -1 DAY))

change to

d._createdDate >= CURDATE() - INTERVAL 1 DAY

Index:

library:  INDEX(available, link_id, _createdDate)

Are you sure about LEFT?

LEFT JOIN warehouse.tmp_newBooks d ON
    d._rowid <> dd._rowid AND ...
GROUP BY d._rowid

When the LEFT JOIN does not find a matching row in the "right" table (d), it will deliver all NULLs for those columns. Was that intentional? Or did you need a plain JOIN?

And the GROUP BY will have a row with NULL for _rowid; are you expecting that?

And, I think, the CASE will be NULL.

Not clear:

SELECT ..., cs.publisherID, ...
    ...
    WHERE d.publishersID = cs._rowid

Recommend being consistent on "row" versus "publisher". (And maybe this inconsistency is leading to unnecessary sluggishness?

Once the above is resolve, maybe you can avoid the TEMPORARY TABLEs and simply JOIN to publisher in the final SELECT.

key=joinIdx and key_len=5 probably implies that only the first column of that index was being used.

OR is hard to optimize. A possible optimization for

  (d.storePostcode = dd.storePostcode AND d.authorName = dd.authorName)
or d.publisherReference = dd.publisherReference
OR d.bookTitle = dd.bookTitle
order by  `group_wallets_transactions`.`created_at` desc

is to use UNION DISTINCT:

( SELECT ...
     WHERE (d.storePostcode = dd.storePostcode AND d.authorName = dd.authorName)
) UNION DISTINCT (
( SELECT ...
     WHERE d.publisherReference = dd.publisherReference
) UNION DISTINCT (
( SELECT ...
     WHERE d.bookTitle = dd.bookTitle
)
order by  `group_wallets_transactions`.`created_at` desc