SELECT t1.TagID AS TagID1
,t2.TagID AS TagID2
FROM TagMap AS t1
JOIN TagMap AS t2 ON t1.ArticleID = t2.ArticleID AND t1.TagID <> t2.TagID
GROUP BY t1.TagID, t2.TagID
UPDATED: index on ArticleID may help to improve query performance
You need to use \G as in SHOW CREATE TABLE options \G
mysql> show create table mysql.db \G
*************************** 1. row ***************************
Create Table: CREATE TABLE `db` (
`Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
`Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`User` char(32) COLLATE ...
NO! That's the wrong solution to a memory issue.
The buffer_pool is a "cache"; it does not have to be bigger than the dataset.
InnoDB is faster than MyISAM; this point, alone, may doom your conversion to disaster.
The 1000 comes from 4*255, where the 4 comes from using ut8mb4 (good) and WP's default of using VARCHAR(255) (not good). Check your data, ...
INSERT INTO Pairs
SELECT DISTINCT NULL, t1.KeyID, t1.ValueID, t2.KeyID, t2.ValueID
FROM ItemKeyValues t1
JOIN ItemKeyValues t2 USING (ItemID)
WHERE t1.KeyID < t2.KeyID;
INSERT INTO ItemPairs
SELECT t1.PairID, t2.ItemID
FROM Pairs t1
JOIN ItemKeyValues t2 ON t1.Key1 = t2.KeyID
AND t1.Value1 = t2.ValueID
JOIN ItemKeyValues t3 ON t1....
where log_time + interval sources.retention_days day < current_timestamp;
is not "sargable". Rearrange it so that the column is on one side of an operator, then make sure there is an index (which you do):
WHERE data_sources.log_time <
NOW() - INTERVAL sources.retention_days DAY
data_sources: (source_id, log_time)
Adding an index to data_sources(source_id, log_time) solved the problem.
create index index_data_sources_source_id_log_time on data_sources(source_id, log_time);
This brought the execution time down to a couple of seconds.
Apparently the calculation of interval sources.retention_days day < current_timestamp doesn't have such a massive performance ...
InnoDB. Period. Full stop.
Even 10 records per second, all day, is not a problem. Just do the UPDATEs or INSERTs when you need to.
If, on the other hand, you are rebuilding the entire table, then say so; there are other techniques.
You mentioned subtyping, but want is the relevance?
Please provide SHOW CREATE TABLE, even if still being sketched out. ...
First rule of *nix admin is: Logs are your friends
Looking at logfile:
2020-05-15 14:41:00 1108 [ERROR] Event Scheduler: [phpmyadmin@localhost].[integrity.DELETE_EXPIRED_SESSIONS] execution failed, user no longer has EVENT privilege.
2020-05-15 14:41:00 1108 [ERROR] Event Scheduler: [phpmyadmin@localhost][integrity.DELETE_EXPIRED_SESSIONS] Access denied ...