While optimizing some indexes I detected a curious case in the slow_query_log.
These are not the actual tablenames but for simplicity:
# Thread_id: 1293932 Schema: xx QC_hit: No
# Query_time: 0.205087 Lock_time: 0.000046 Rows_sent: 0 Rows_examined: 1
# Rows_affected: 1 Bytes_sent: 52
UPDATE language SET lastUpdate = current_timestamp() WHERE languageID = '12';
Table 'language' has exactly 12 rows. It has only a few columns. It has only 1 index: the primary key on languageID (INT). This is thus the simplest table you can imagine. Why is this update statement taking 0.2 seconds?
For reference, select statements on rows of 10k-100k's of rows are quite fast:
# Thread_id: 1294423 Schema: xx QC_hit: No
# Query_time: 0.111802 Lock_time: 0.000266 Rows_sent: 0 Rows_examined: 14279
# Rows_affected: 0 Bytes_sent: 50
INSERT INTO session (sessionID, personID, lastDate)
SELECT 8, a.personID, current_timestamp() FROM personA a INNER JOIN personP p on p.personID = a.personID LEFT OUTER JOIN view_bigViewB ON view_bigViewB.personID = a.personID WHERE view_bigViewB.someValue = 'x' ;
Above query is simplified a lot, and please do not pay too much atention to it, but let's say this is really a dramatic big data set that is being processed in only 0.1sec with full table scans. It is an INSERT that did not insert any rows, so no write operation was needed.
Of course, a write operation is more costly but 0.2s seems just too slow. How can I analyze what exactly is making up this .2 seconds?
Using MariadDb 10.3.27
SHOW CREATE TABLE
CREATE TABLE `language`
(
`languageID` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(60) COLLATE utf8mb4_unicode_ci NOT NULL,
`lastUpdate` datetime DEFAULT NULL,
PRIMARY KEY (`languageID`)
)
ENGINE=InnoDB
AUTO_INCREMENT=16
DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Output of innodb-parameters: https://pastebin.com/ReGHc1xp
SHOW CREATE TABLE
. It sounds like you are using MyISAM instead of InnoDB.