3
SELECT t1.TagID AS TagID1
,t2.TagID AS TagID2
,COUNT(1)
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
2
You need to use \G as in SHOW CREATE TABLE options \G
EXAMPLE
mysql> show create table mysql.db \G
*************************** 1. row ***************************
Table: db
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 ...
2
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, ...
1
normally it would be build, when you added the index
But you can OTIMIZE TABLE yourtablename
The fulltext index is only fully re-organized when an OPTIMIZE TABLE statement is run.
1
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....
1
Plan A:
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
Also needed:
data_sources: (source_id, log_time)
Plan ...
1
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 ...
1
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. ...
1
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 ...
Only top voted, non community-wiki answers of a minimum length are eligible
