{"id":16416825,"date":"2023-01-28T11:21:01","date_gmt":"2023-01-28T11:21:01","guid":{"rendered":"https:\/\/wordpress.org\/support\/topic\/database-optimizations\/"},"modified":"2023-01-28T11:21:01","modified_gmt":"2023-01-28T11:21:01","slug":"database-optimizations","status":"publish","type":"topic","link":"https:\/\/wordpress.org\/support\/topic\/database-optimizations\/","title":{"rendered":"Database optimizations"},"content":{"rendered":"\n

Hi,<\/p>\n\n\n\n

I noticed some problems with database queries on my server and I am writing how I fixed them. The blog I am managing has lots of posts.<\/p>\n\n\n\n

These queries were reported as slow by slow logger in MySQL.<\/p>\n\n\n\n

UPDATE wp_swp_analytics<\/code> SET post_id<\/code> = 0, date<\/code> = ‘2023-01-27’, facebook<\/code> = ‘10659’, pinterest<\/code> = ‘1235’, total_shares<\/code> = ‘11894’ WHERE post_id<\/code> = 0 AND date<\/code> = ‘2023-01-27’ — These can be removed from SET: post_id<\/code> = 0, date<\/code> = ‘2023-01-27’,<\/p>\n\n\n\n

SELECT * FROM wp_3_swp_analytics WHERE post_id = 18980 && date = ‘2023-01-27’ — Word “AND” should be used instead of deprecated “&&”.<\/p>\n\n\n\n

The slow queries where fixed by creating an index:
ALTER TABLE wp_swp_analytics<\/code> CHANGE date<\/code> date<\/code> DATE NOT NULL DEFAULT ‘0001-01-01’; — 0000-00-00 is not a supported value
ALTER TABLE wp_swp_analytics<\/code> ADD INDEX post_id__date<\/code> (post_id<\/code>, date<\/code>); — Can be made date__post_id is both columns are always included in the WHERE filter in query
— ALTER TABLE wp_swp_analytics<\/code> ADD UNIQUE post_id__date<\/code> (post_id<\/code>, date<\/code>); — This did not work because multiple rows exists for certain post_id and date; I do not think this is OK. “INSERT INTO … ON DUPLICATE KEY UPDATE …” can be used here. If ID is generated, than it can be removed and date+post_id can be defined as a primary key.<\/p>\n\n\n\n

Best regards,
D.<\/p>\n\n\n\n

<\/p>\n\n\n\n

<\/p>\n\n\n\n

<\/p>\n","protected":false},"template":"","class_list":["post-16416825","topic","type-topic","status-publish","hentry"],"jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/wordpress.org\/support\/wp-json\/wp\/v2\/topic\/16416825","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/wordpress.org\/support\/wp-json\/wp\/v2\/topic"}],"about":[{"href":"https:\/\/wordpress.org\/support\/wp-json\/wp\/v2\/types\/topic"}],"version-history":[{"count":0,"href":"https:\/\/wordpress.org\/support\/wp-json\/wp\/v2\/topic\/16416825\/revisions"}],"wp:attachment":[{"href":"https:\/\/wordpress.org\/support\/wp-json\/wp\/v2\/media?parent=16416825"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}