{"id":1331,"date":"2016-01-11T17:56:08","date_gmt":"2016-01-11T22:56:08","guid":{"rendered":"http:\/\/osric.com\/chris\/accidental-developer\/?p=1331"},"modified":"2016-01-11T17:56:08","modified_gmt":"2016-01-11T22:56:08","slug":"mysql-date_add-and-date_sub-functions-running-against-millions-of-rows","status":"publish","type":"post","link":"https:\/\/osric.com\/chris\/accidental-developer\/2016\/01\/mysql-date_add-and-date_sub-functions-running-against-millions-of-rows\/","title":{"rendered":"MySQL date_add and date_sub functions running against millions of rows"},"content":{"rendered":"

One of my servers runs a query once a week to remove all rows from a Syslog table (>20,000,000 rows) in a MySQL database that are older than 60 days. This was running terribly slowly and interfering with other tasks on the server.<\/p>\n

Although the original query used a DELETE statement I’ve used SELECT statements in the examples below.<\/p>\n

SELECT COUNT(*)
\nFROM SystemEvents
\nWHERE ReceivedAt < DATE_SUB(NOW(), INTERVAL 60 DAY);<\/code><\/p>\n

That selects about 900,000 rows and takes about 45 seconds.<\/p>\n

SELECT COUNT(*)
\nFROM SystemEvents
\nWHERE ReceivedAt < DATE_ADD(CURRENT_DATE, INTERVAL -60 DAY);<\/code><\/p>\n

Likewise takes about 48 seconds.<\/p>\n

Is MySQL running a function every time it makes a comparison? I decided to try using a hard-coded date to find out:<\/p>\n

SELECT COUNT(*)
\nFROM SystemEvents
\nWHERE ReceivedAt < '2015-11-12 12:00:00';<\/code><\/p>\n

6 seconds! Much faster.<\/p>\n

I created a user-defined variable:
\nSET @sixty_days_ago = DATE_SUB(NOW(), INTERVAL 60 DAY);<\/p>\n

Then ran the query:
\nSELECT COUNT(*)
\nFROM SystemEvents
\nWHERE ReceivedAt < @sixty_days_ago;<\/code><\/p>\n

12 seconds. No 6 seconds, but still a fraction of the original time!<\/p>\n","protected":false},"excerpt":{"rendered":"

One of my servers runs a query once a week to remove all rows from a Syslog table (>20,000,000 rows) in a MySQL database that are older than 60 days. This was running terribly slowly and interfering with other tasks on the server. Although the original query used a DELETE statement I’ve used SELECT statements … Continue reading MySQL date_add and date_sub functions running against millions of rows<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[381,232],"tags":[382,383,36],"class_list":["post-1331","post","type-post","status-publish","format-standard","hentry","category-mysql","category-tips-tricks","tag-date_add","tag-date_sub","tag-mysql"],"_links":{"self":[{"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/posts\/1331","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/comments?post=1331"}],"version-history":[{"count":3,"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/posts\/1331\/revisions"}],"predecessor-version":[{"id":1338,"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/posts\/1331\/revisions\/1338"}],"wp:attachment":[{"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/media?parent=1331"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/categories?post=1331"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/tags?post=1331"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}