Hot answers tagged

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