mariaDB query/update very slow (timout) first time

Question

Maybe just forget everything below... After investigating, adding indexes we do get other results, and other queries becoming slow... We really helps is this: Another VM (all the same image) Open HeidiSQL, monitor the processes, explaining a query also might help. (No changes in the database) Then restart the machine, everything is fast. Just restarting without doing anything in HeidiSQL doesn't work.

In this database first some Temp(TMP) tables are filled (This are real tables!, not in memory temp tables). Then large stored-proc is executed to process the data from the temp tables to the other tables. Depending on the data in the TMP tables the query is very slow. (timeouts). Using HeidiSQL I figured out that one query was always busy.

After a client process kill the same scenario is fast, and stays fast. Even after restart of the machine the scenario stays fast..

In case of 4000 TMPproperty records and 1 TMPTransaction the query is fast. In case of 4000 TMPProperties and 100 TMPTransactions the query is very slow. Leading to timeouts in the client application. I can image it has something to do with the join between TMPPropertyValue and TMPTransaction, but why only the first time?

Someone an idea what is wrong in the query?

UPDATE TMPPropertyValue, PropertyValue AS PV
    INNER JOIN Object AS O ON PV.ObjectRowId = O.RowId
    INNER JOIN TMPObject AS TMPO ON O.ObjectId = TMPO.ObjectId
    INNER JOIN Transaction AS T ON T.RowId = PV.TransactionRowId
    INNER JOIN Datastore AS D ON P.DatastoreRowId = D.RowId, TMPTransaction AS TT
    SET TMPPropertyValue.Active = CASE
        WHEN TT.TransactionDateTime > T.TransactionDateTime THEN 1
        WHEN TT.TransactionDateTime < T.TransactionDateTime THEN 0
        ELSE
        CASE
            WHEN TT.DatastoreID > D.DatastoreId THEN 1
            ELSE 0
        END
    END 
    WHERE TMPO.RowId = TMPPropertyValue.ObjectRowId
        AND TMPPropertyValue.TransactionRowId = TT.RowId
        AND TMPPropertyValue.Active IS NULL
        AND PV.PropertyRowId = TMPPropertyValue.PropertyRowId
        AND (TT.DatastoreId <> D.DatastoreId OR TT.TransactionSeqNr <> T.TransactionSeqNr)
        AND TMPPropertyValue.IsNew = 1;

I can't see what I must to rewrite this query, or indexed or so are needed? (As c# developer)

Update: Killing the client, I managed keep the tmptables content and reproduce the slow query by creating a select statement, also replaced the joins and ',' joins and where with all inner joins.

mariadb version 10.3.11

SELECT * FROM TMPPropertyValue
    INNER JOIN TMPObject AS TMPO ON TMPPropertyValue.ObjectRowId = TMPO.RowId
    INNER JOIN Object AS O ON TMPO.ObjectId = O.ObjectId
    INNER JOIN PropertyValue AS PV ON PVA.ObjectRowId = O.RowId
    INNER JOIN Transaction AS T ON T.RowId = PVA.PerceptionRowId
    INNER JOIN Datastore AS D ON T.DatastoreRowId = D.RowId
     INNER JOIN TMPTransaction AS TT ON TMPPropertyValue.PerceptionRowId = TP.RowId
   WHERE PV.PropertyRowId = TMPPropertyValue.PropertyRowId
        AND (TP.DatastoreId <> D.DatastoreId OR TT.TransactionSeqNr <> T.TransactionSeqNr);

What really makes the query fast again is ommitting the JOIN with the Datastore table. This table is just 3 columns and about 10 records.
A index adding on the key and foreign key doesn't influence the result. (Transaction.DatastoreRowId and Datastore.RowId)

the select return 4004 record and 84/87 columns, depending on datastore join

CREATE TABLE `datastore` (
  `RowId` int(11) NOT NULL AUTO_INCREMENT,
  `DatastoreId` binary(16) NOT NULL,
  `IsSynchronizable` tinyint(1) NOT NULL,
  PRIMARY KEY (`RowId`),
  KEY `IDX_Datastore_DatastoreIdRowId` (`DatastoreId`,`RowId`),
  KEY `IDX_Datastore_RowIdDataStoreId` (`RowId`,`DatastoreId`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

CREATE TABLE `Transaction` (
  `RowId` int(11) NOT NULL AUTO_INCREMENT,
  `DatastoreRowId` int(11) NOT NULL,
  `TransactionSeqNr` int(11) NOT NULL DEFAULT 1,
  `------
  PRIMARY KEY (`RowId`),
  UNIQUE KEY `IX_PerceptionActive_ContextDatastoreTransSeqNr` (`ContextRowId`,`DatastoreRowId`,`TransactionSeqNr`),
  KEY `IDX_Transaction_TransactionDateTime` (`TransactionDateTime`),
  KEY `IX_Transaction_ContextPerceptionSeqNrTransactionDateTime` (`ContextRowId`,`PerceptionSeqNr`,`TransactionDateTime`),
  KEY `Transaction_Host` (`HostRowId`),
  KEY `FK_Transaction_Datastore` (`DatastoreRowId`),
  KEY `FK_Transaction_User` (`UserRowId`),
  KEY `FK_Transaction_ClassificationDomain` (`ClassificationDomainRowId`),
  CONSTRAINT `FK_Transaction_ClassificationDomain` FOREIGN KEY (`ClassificationDomainRowId`) REFERENCES `classificationdomain` (`RowId`),
  CONSTRAINT `FK_Transaction_Context` FOREIGN KEY (`ContextRowId`) REFERENCES `context` (`RowId`),
  CONSTRAINT `FK_Transaction_Datastore` FOREIGN KEY (`DatastoreRowId`) REFERENCES `datastore` (`RowId`),
  CONSTRAINT `FK_Transaction_Host` FOREIGN KEY (`HostRowId`) REFERENCES `host` (`RowId`),
  CONSTRAINT `FK_Transaction_User` FOREIGN KEY (`UserRowId`) REFERENCES `perceptionuser` (`RowId`)
) ENGINE=InnoDB AUTO_INCREMENT=2208 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

The explain is the same in both situations mariadb explain

Answer 1

What finally fixed the issue:

• Changed several queries in the stored-proc using better join/cross joins, no mixing etc
• Adding some indexes for the columns used in the joins.

But what finally did the fix: adding a "Force Index" for the specific query. (on the new index). (since it was sometimes slow and then after some investigating/trying out using heidisql fast)