{"id":319,"date":"2006-02-23T09:45:24","date_gmt":"2006-02-23T15:45:24","guid":{"rendered":"http:\/\/www.mooreds.com\/wordpress\/?p=319"},"modified":"2006-09-27T09:44:46","modified_gmt":"2006-09-27T15:44:46","slug":"mysql-performance-and-doing-calculations-on-varchar-columns","status":"publish","type":"post","link":"https:\/\/www.mooreds.com\/wordpress\/archives\/319","title":{"rendered":"MySQL performance and doing calculations on varchar columns"},"content":{"rendered":"<p>MySQL, along with other features designed to make it easy to use, tries to do the right thing regarding strings.  When you perform a math calculation on a column or columns that are of type <code>varchar<\/code>, MySQL automatically conversts that string to a number (empty strings are treated as zero.): <a href=\"http:\/\/dev.mysql.com\/doc\/refman\/4.1\/en\/cast-functions.html\"><br \/>\n<\/a><\/p>\n<blockquote><p><a href=\"http:\/\/dev.mysql.com\/doc\/refman\/4.1\/en\/cast-functions.html\"> To cast a string to a numeric value in numeric context, you normally do not have to do anything other than to use the string value as though it were a number[.]<\/a><\/p><\/blockquote>\n<p>However, this translation, convenient as it may be, is not free. A client of mine had a query that was running calculations against two such columns.  After indexing and trying to simplify the query, we were still seeing query execution times of 2+ seconds (all times are quoted for MySQL 4.1, on my relativly slow personal laptop).<\/p>\n<p>The solution appears to be to change the type of the columns <a href=\"http:\/\/dev.mysql.com\/doc\/refman\/4.1\/en\/alter-table.html\">using the alter table syntax<\/a> to type double.  After doing so and running <code>analyze table mytable<\/code>, I was seeing query execution times of 0.2 seconds for the same query on the same box.  Fantastic.<\/p>\n<p>I am not sure if this result was due to not having to do several string conversions for each row returned by the query, or the fact that: <a href=\"http:\/\/dev.mysql.com\/doc\/refman\/4.1\/en\/mysql-indexes.html\"><br \/>\n<\/a><\/p>\n<blockquote><p><a href=\"http:\/\/dev.mysql.com\/doc\/refman\/4.1\/en\/mysql-indexes.html\">In some cases, a query can be optimized to retrieve values without consulting the data rows. If a query uses only columns from a table that are numeric and that form a leftmost prefix for some key, the selected values may be retrieved from the index tree for greater speed[.]<\/a><\/p><\/blockquote>\n<p>Regardless of the cause, if you&#8217;re doing some complicated calculations on columns, consider making them numbers.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>MySQL, along with other features designed to make it easy to use, tries to do the right thing regarding strings. When you perform a math calculation on a column or [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[13,6],"tags":[],"class_list":["post-319","post","type-post","status-publish","format-standard","hentry","category-databases","category-programming"],"_links":{"self":[{"href":"https:\/\/www.mooreds.com\/wordpress\/wp-json\/wp\/v2\/posts\/319","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.mooreds.com\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.mooreds.com\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.mooreds.com\/wordpress\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.mooreds.com\/wordpress\/wp-json\/wp\/v2\/comments?post=319"}],"version-history":[{"count":0,"href":"https:\/\/www.mooreds.com\/wordpress\/wp-json\/wp\/v2\/posts\/319\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.mooreds.com\/wordpress\/wp-json\/wp\/v2\/media?parent=319"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.mooreds.com\/wordpress\/wp-json\/wp\/v2\/categories?post=319"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.mooreds.com\/wordpress\/wp-json\/wp\/v2\/tags?post=319"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}