{"id":437,"date":"2010-01-31T09:40:25","date_gmt":"2010-01-31T09:40:25","guid":{"rendered":"https:\/\/wade.one\/blog\/?p=437"},"modified":"2010-01-31T09:40:25","modified_gmt":"2010-01-31T09:40:25","slug":"mysql-and-binary16-the-reasonsbenefitsdrawbacks-mysql","status":"publish","type":"post","link":"https:\/\/wade.one\/blog\/2010\/01\/31\/mysql-and-binary16-the-reasonsbenefitsdrawbacks-mysql\/","title":{"rendered":"MySQL and Binary(16) &#8211; The Reasons\/Benefits\/Drawbacks (#mysql)"},"content":{"rendered":"<p>I recently posted an article about using BINARY(16) for storing MD5&#8217;s as unique identifiers instead of simple integer ID&#8217;s (usually auto increment); in that article I touched on one of the benefits, reducing JOIN&#8217;s, but there are other reasons for doing it too, so I thought I&#8217;d post an article discussing purely the reasons behind using BINARY(16).<\/p>\n<p>As I discussed in my previous article, an MD5 string is actually a hexadecimal number capable of storing values as large as 340,282,366,920,938,463,463,374,607,431,768,211,456. MySQL doesn&#8217;t have any efficient integer field for storing numbers this big so you have two choices for storage, a CHAR(32) or a BINARY(16). If you convert a hexadecimal MD5 into a unhexed character string, it will become 16 bytes rather than 32. MySQL handily has a feature built in for this called <a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.0\/en\/string-functions.html#function_unhex\" target=\"_blank\">UNHEX<\/a>.<\/p>\n<p>So, why use binary(16) as a unique field for data storage? Databases like MySQL have superb functionality such as JOIN, allowing you to query one table and &#8220;join&#8221; the results of that query to another table. However, when you get to 10&#8217;s, 100&#8217;s or even 1000&#8217;s of millions of rows of data, JOIN&#8217;s become expensive, especially when the join only exists because you need an ID field from one table to query against on another. From tests at work, replacing a JOIN by using a binary(16) unique identifier has seen noticeable improvements to speed, noticeable here being human noticeable, not iterate it a million times and you&#8217;ll see 1.5 as opposed to 1.9 seconds noticeable.<\/p>\n<p>The main benefits include:<\/p>\n<ul>\n<li>Fast queries against any table where you know the formula that was used to create the MD5 binary(16) using human-readable English and no integers.<\/li>\n<li>Complete disassociation of relational data values<\/li>\n<li>Ability to use INSERT IGNORE to avoid duplicate data without having to use overly large indexes<\/li>\n<li>More unique values than even a BIGINT.<\/li>\n<\/ul>\n<p>The main drawbacks include:<\/p>\n<ul>\n<li>12 bytes more storage for the ID (INT is 4 bytes)<\/li>\n<li>No auto-incrementation<\/li>\n<li>Completely unreadable to humans when the data is in BINARY(16) form.<\/li>\n<\/ul>\n<p>One thing I just mentioned was disassociation of relational data values. What does this mean exactly? Well it means exactly the same as what people do now with MySQL and unique integer ID&#8217;s to be honest! The difference here is you can query against it without those pesky JOIN&#8217;s a lot of the time. For example, say you are storing every town in the UK in a database and how they link together (i.e. if there is a direct route from one to another.) You&#8217;d have a table named towns probably, with a unique ID and the town name. You&#8217;d then have a separate table with 2 columns, both columns would store a town ID which would basically mean &#8220;this town has a direct route to this town.&#8221; If you were to use integers as the town&#8217;s unique ID, every time you wanted to get the town&#8217;s linked to said town, you&#8217;d have to query against the towns table first to get the town ID you want to get links to, then again to get the names of the towns that link to it.<br \/>\nIf you were to use a binary(16) representation of the town you could scrap the first join, instead you could query by saying &#8220;get me any towns that link to UNHEX(MD5(&#8216;Town Name&#8217;))&#8221;. You&#8217;d still have to do the second join to get the town names, but you&#8217;ve instantly dropped a JOIN and simplified the whole experience as you can now query more naturally.<br \/>\nBasically, all you&#8217;re doing is replacing any place in your database that is a string that is usually more than 16 characters in length with a binary(16) of it, then storing the strings elsewhere for when you actually need to read the output. This effectively gives you a look-up table that can contain any string whatsoever and a database that stores relationships of strings without requiring special tables and integers for every string.<\/p>\n<p>As a note, a table with 100 million rows of data with two columns &#8211; BINARY(16), TEXT &#8211; to look-up the textual value of a binary(16) string takes 0.0019 seconds for us and having that table of text has meant we&#8217;ve severely de-duped our database as the data we store often is identical, even when the source is completely different. Even if we do a WHERE BINARY(16) IN (list,of,values), the time sticks at 0.0019 up to the maximum test I&#8217;ve done so far which is 100 MD5&#8217;s.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>I recently posted an article about using BINARY(16) for storing MD5&#8217;s as unique identifiers instead of simple integer ID&#8217;s (usually auto increment); in that article I touched on one of the benefits, reducing JOIN&#8217;s, but there are other reasons for doing it too, so I thought I&#8217;d post an article discussing purely the reasons behind &#8230; <a href=\"https:\/\/wade.one\/blog\/2010\/01\/31\/mysql-and-binary16-the-reasonsbenefitsdrawbacks-mysql\/\" class=\"more-link\">Read More<span class=\"screen-reader-text\"> &#8220;MySQL and Binary(16) &#8211; The Reasons\/Benefits\/Drawbacks (#mysql)&#8221;<\/span> &raquo;<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[13],"tags":[22],"class_list":["post-437","post","type-post","status-publish","format-standard","hentry","category-programming","tag-php-conference-2010"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.4 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>MySQL and Binary(16) - The Reasons\/Benefits\/Drawbacks (#mysql) - wade.one<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/wade.one\/blog\/2010\/01\/31\/mysql-and-binary16-the-reasonsbenefitsdrawbacks-mysql\/\" \/>\n<meta property=\"og:locale\" content=\"en_GB\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"MySQL and Binary(16) - The Reasons\/Benefits\/Drawbacks (#mysql) - wade.one\" \/>\n<meta property=\"og:description\" content=\"I recently posted an article about using BINARY(16) for storing MD5&#8217;s as unique identifiers instead of simple integer ID&#8217;s (usually auto increment); in that article I touched on one of the benefits, reducing JOIN&#8217;s, but there are other reasons for doing it too, so I thought I&#8217;d post an article discussing purely the reasons behind ... Read More &quot;MySQL and Binary(16) &#8211; The Reasons\/Benefits\/Drawbacks (#mysql)&quot; &raquo;\" \/>\n<meta property=\"og:url\" content=\"https:\/\/wade.one\/blog\/2010\/01\/31\/mysql-and-binary16-the-reasonsbenefitsdrawbacks-mysql\/\" \/>\n<meta property=\"og:site_name\" content=\"wade.one\" \/>\n<meta property=\"article:published_time\" content=\"2010-01-31T09:40:25+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/wade.one\/blog\/wp-content\/uploads\/2015\/02\/Wade-Logo-cropped.png\" \/>\n\t<meta property=\"og:image:width\" content=\"1518\" \/>\n\t<meta property=\"og:image:height\" content=\"1506\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Wade\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@wadewomersley\" \/>\n<meta name=\"twitter:site\" content=\"@wadewomersley\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Wade\" \/>\n\t<meta name=\"twitter:label2\" content=\"Estimated reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/wade.one\\\/blog\\\/2010\\\/01\\\/31\\\/mysql-and-binary16-the-reasonsbenefitsdrawbacks-mysql\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/wade.one\\\/blog\\\/2010\\\/01\\\/31\\\/mysql-and-binary16-the-reasonsbenefitsdrawbacks-mysql\\\/\"},\"author\":{\"name\":\"Wade\",\"@id\":\"https:\\\/\\\/wade.one\\\/blog\\\/#\\\/schema\\\/person\\\/f9dedd948575256e77a44aa1417f63de\"},\"headline\":\"MySQL and Binary(16) &#8211; The Reasons\\\/Benefits\\\/Drawbacks (#mysql)\",\"datePublished\":\"2010-01-31T09:40:25+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/wade.one\\\/blog\\\/2010\\\/01\\\/31\\\/mysql-and-binary16-the-reasonsbenefitsdrawbacks-mysql\\\/\"},\"wordCount\":762,\"commentCount\":2,\"publisher\":{\"@id\":\"https:\\\/\\\/wade.one\\\/blog\\\/#\\\/schema\\\/person\\\/8b4739f8f8bb2cff5d792d4b8779fcc3\"},\"keywords\":[\"PHP Conference 2010\"],\"articleSection\":[\"Programming\"],\"inLanguage\":\"en-GB\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/wade.one\\\/blog\\\/2010\\\/01\\\/31\\\/mysql-and-binary16-the-reasonsbenefitsdrawbacks-mysql\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/wade.one\\\/blog\\\/2010\\\/01\\\/31\\\/mysql-and-binary16-the-reasonsbenefitsdrawbacks-mysql\\\/\",\"url\":\"https:\\\/\\\/wade.one\\\/blog\\\/2010\\\/01\\\/31\\\/mysql-and-binary16-the-reasonsbenefitsdrawbacks-mysql\\\/\",\"name\":\"MySQL and Binary(16) - The Reasons\\\/Benefits\\\/Drawbacks (#mysql) - wade.one\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/wade.one\\\/blog\\\/#website\"},\"datePublished\":\"2010-01-31T09:40:25+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/wade.one\\\/blog\\\/2010\\\/01\\\/31\\\/mysql-and-binary16-the-reasonsbenefitsdrawbacks-mysql\\\/#breadcrumb\"},\"inLanguage\":\"en-GB\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/wade.one\\\/blog\\\/2010\\\/01\\\/31\\\/mysql-and-binary16-the-reasonsbenefitsdrawbacks-mysql\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/wade.one\\\/blog\\\/2010\\\/01\\\/31\\\/mysql-and-binary16-the-reasonsbenefitsdrawbacks-mysql\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/wade.one\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"MySQL and Binary(16) &#8211; The Reasons\\\/Benefits\\\/Drawbacks (#mysql)\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/wade.one\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/wade.one\\\/blog\\\/\",\"name\":\"wade.one\",\"description\":\"wade womersley - york based software engineer\",\"publisher\":{\"@id\":\"https:\\\/\\\/wade.one\\\/blog\\\/#\\\/schema\\\/person\\\/8b4739f8f8bb2cff5d792d4b8779fcc3\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/wade.one\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-GB\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\\\/\\\/wade.one\\\/blog\\\/#\\\/schema\\\/person\\\/8b4739f8f8bb2cff5d792d4b8779fcc3\",\"name\":\"Wade Womersley\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-GB\",\"@id\":\"https:\\\/\\\/wade.one\\\/blog\\\/wp-content\\\/uploads\\\/2015\\\/02\\\/200px.png\",\"url\":\"https:\\\/\\\/wade.one\\\/blog\\\/wp-content\\\/uploads\\\/2015\\\/02\\\/200px.png\",\"contentUrl\":\"https:\\\/\\\/wade.one\\\/blog\\\/wp-content\\\/uploads\\\/2015\\\/02\\\/200px.png\",\"width\":202,\"height\":200,\"caption\":\"Wade Womersley\"},\"logo\":{\"@id\":\"https:\\\/\\\/wade.one\\\/blog\\\/wp-content\\\/uploads\\\/2015\\\/02\\\/200px.png\"}},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/wade.one\\\/blog\\\/#\\\/schema\\\/person\\\/f9dedd948575256e77a44aa1417f63de\",\"name\":\"Wade\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-GB\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/94100ef7361b8aaac136b852c8df93bdd10942165a122d5c56e4466cc403e5d9?s=96&d=retro&r=pg\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/94100ef7361b8aaac136b852c8df93bdd10942165a122d5c56e4466cc403e5d9?s=96&d=retro&r=pg\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/94100ef7361b8aaac136b852c8df93bdd10942165a122d5c56e4466cc403e5d9?s=96&d=retro&r=pg\",\"caption\":\"Wade\"},\"url\":\"https:\\\/\\\/wade.one\\\/blog\\\/author\\\/wade\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"MySQL and Binary(16) - The Reasons\/Benefits\/Drawbacks (#mysql) - wade.one","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/wade.one\/blog\/2010\/01\/31\/mysql-and-binary16-the-reasonsbenefitsdrawbacks-mysql\/","og_locale":"en_GB","og_type":"article","og_title":"MySQL and Binary(16) - The Reasons\/Benefits\/Drawbacks (#mysql) - wade.one","og_description":"I recently posted an article about using BINARY(16) for storing MD5&#8217;s as unique identifiers instead of simple integer ID&#8217;s (usually auto increment); in that article I touched on one of the benefits, reducing JOIN&#8217;s, but there are other reasons for doing it too, so I thought I&#8217;d post an article discussing purely the reasons behind ... Read More \"MySQL and Binary(16) &#8211; The Reasons\/Benefits\/Drawbacks (#mysql)\" &raquo;","og_url":"https:\/\/wade.one\/blog\/2010\/01\/31\/mysql-and-binary16-the-reasonsbenefitsdrawbacks-mysql\/","og_site_name":"wade.one","article_published_time":"2010-01-31T09:40:25+00:00","og_image":[{"width":1518,"height":1506,"url":"https:\/\/wade.one\/blog\/wp-content\/uploads\/2015\/02\/Wade-Logo-cropped.png","type":"image\/png"}],"author":"Wade","twitter_card":"summary_large_image","twitter_creator":"@wadewomersley","twitter_site":"@wadewomersley","twitter_misc":{"Written by":"Wade","Estimated reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/wade.one\/blog\/2010\/01\/31\/mysql-and-binary16-the-reasonsbenefitsdrawbacks-mysql\/#article","isPartOf":{"@id":"https:\/\/wade.one\/blog\/2010\/01\/31\/mysql-and-binary16-the-reasonsbenefitsdrawbacks-mysql\/"},"author":{"name":"Wade","@id":"https:\/\/wade.one\/blog\/#\/schema\/person\/f9dedd948575256e77a44aa1417f63de"},"headline":"MySQL and Binary(16) &#8211; The Reasons\/Benefits\/Drawbacks (#mysql)","datePublished":"2010-01-31T09:40:25+00:00","mainEntityOfPage":{"@id":"https:\/\/wade.one\/blog\/2010\/01\/31\/mysql-and-binary16-the-reasonsbenefitsdrawbacks-mysql\/"},"wordCount":762,"commentCount":2,"publisher":{"@id":"https:\/\/wade.one\/blog\/#\/schema\/person\/8b4739f8f8bb2cff5d792d4b8779fcc3"},"keywords":["PHP Conference 2010"],"articleSection":["Programming"],"inLanguage":"en-GB","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/wade.one\/blog\/2010\/01\/31\/mysql-and-binary16-the-reasonsbenefitsdrawbacks-mysql\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/wade.one\/blog\/2010\/01\/31\/mysql-and-binary16-the-reasonsbenefitsdrawbacks-mysql\/","url":"https:\/\/wade.one\/blog\/2010\/01\/31\/mysql-and-binary16-the-reasonsbenefitsdrawbacks-mysql\/","name":"MySQL and Binary(16) - The Reasons\/Benefits\/Drawbacks (#mysql) - wade.one","isPartOf":{"@id":"https:\/\/wade.one\/blog\/#website"},"datePublished":"2010-01-31T09:40:25+00:00","breadcrumb":{"@id":"https:\/\/wade.one\/blog\/2010\/01\/31\/mysql-and-binary16-the-reasonsbenefitsdrawbacks-mysql\/#breadcrumb"},"inLanguage":"en-GB","potentialAction":[{"@type":"ReadAction","target":["https:\/\/wade.one\/blog\/2010\/01\/31\/mysql-and-binary16-the-reasonsbenefitsdrawbacks-mysql\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/wade.one\/blog\/2010\/01\/31\/mysql-and-binary16-the-reasonsbenefitsdrawbacks-mysql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/wade.one\/blog\/"},{"@type":"ListItem","position":2,"name":"MySQL and Binary(16) &#8211; The Reasons\/Benefits\/Drawbacks (#mysql)"}]},{"@type":"WebSite","@id":"https:\/\/wade.one\/blog\/#website","url":"https:\/\/wade.one\/blog\/","name":"wade.one","description":"wade womersley - york based software engineer","publisher":{"@id":"https:\/\/wade.one\/blog\/#\/schema\/person\/8b4739f8f8bb2cff5d792d4b8779fcc3"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/wade.one\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-GB"},{"@type":["Person","Organization"],"@id":"https:\/\/wade.one\/blog\/#\/schema\/person\/8b4739f8f8bb2cff5d792d4b8779fcc3","name":"Wade Womersley","image":{"@type":"ImageObject","inLanguage":"en-GB","@id":"https:\/\/wade.one\/blog\/wp-content\/uploads\/2015\/02\/200px.png","url":"https:\/\/wade.one\/blog\/wp-content\/uploads\/2015\/02\/200px.png","contentUrl":"https:\/\/wade.one\/blog\/wp-content\/uploads\/2015\/02\/200px.png","width":202,"height":200,"caption":"Wade Womersley"},"logo":{"@id":"https:\/\/wade.one\/blog\/wp-content\/uploads\/2015\/02\/200px.png"}},{"@type":"Person","@id":"https:\/\/wade.one\/blog\/#\/schema\/person\/f9dedd948575256e77a44aa1417f63de","name":"Wade","image":{"@type":"ImageObject","inLanguage":"en-GB","@id":"https:\/\/secure.gravatar.com\/avatar\/94100ef7361b8aaac136b852c8df93bdd10942165a122d5c56e4466cc403e5d9?s=96&d=retro&r=pg","url":"https:\/\/secure.gravatar.com\/avatar\/94100ef7361b8aaac136b852c8df93bdd10942165a122d5c56e4466cc403e5d9?s=96&d=retro&r=pg","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/94100ef7361b8aaac136b852c8df93bdd10942165a122d5c56e4466cc403e5d9?s=96&d=retro&r=pg","caption":"Wade"},"url":"https:\/\/wade.one\/blog\/author\/wade\/"}]}},"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack-related-posts":[{"id":431,"url":"https:\/\/wade.one\/blog\/2010\/01\/29\/mysql-binary16-and-scalability\/","url_meta":{"origin":437,"position":0},"title":"MySQL &#8211; Binary(16) and scalability","author":"Wade","date":"January 29, 2010","format":false,"excerpt":"Over the past few months at work, we've seen our database grown from silly big to really silly big, it's still a way to go to get to the size of the big boys such as Facebook etc. but it's still a database stored in MySQL that most day-to-day PHP\u2026","rel":"","context":"In &quot;Programming&quot;","block_context":{"text":"Programming","link":"https:\/\/wade.one\/blog\/category\/programming\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":95,"url":"https:\/\/wade.one\/blog\/2009\/09\/10\/linux-raid-mysql\/","url_meta":{"origin":437,"position":1},"title":"Linux RAID, MySQL","author":"Wade","date":"September 10, 2009","format":false,"excerpt":"Ubuntu...it's a great Linux OS to use on servers due to its speed and simplicity - personal preference based on no statistics, only personal use. However, one thing Linux drives me mad with is software RAID. It's taken me the better part of a day to set up a few\u2026","rel":"","context":"In &quot;Programming&quot;","block_context":{"text":"Programming","link":"https:\/\/wade.one\/blog\/category\/programming\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":463,"url":"https:\/\/wade.one\/blog\/2010\/04\/12\/reset-mysql-root-password-if-you-forgot-it-mysql\/","url_meta":{"origin":437,"position":2},"title":"Reset MySQL root password if you forgot it #mysql","author":"Wade","date":"April 12, 2010","format":false,"excerpt":"Just had a need to reset the mysql root login password for a server, did a bit of Googling and found out this is how you do it (I work on Ubuntu so you may have to tinker with the lines slightly depending on your distribution): Stop the current MySQL\u2026","rel":"","context":"In &quot;Programming&quot;","block_context":{"text":"Programming","link":"https:\/\/wade.one\/blog\/category\/programming\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":453,"url":"https:\/\/wade.one\/blog\/2010\/02\/26\/phpuk2010-part-2-mysql-stuff\/","url_meta":{"origin":437,"position":3},"title":"#PHPUK2010 Part 2 (MySQL stuff)","author":"Wade","date":"February 26, 2010","format":false,"excerpt":"Just picked up a nice tid-bit on creating a unique index on a two column table where the values in each column may be either way around but you only ever want one instance of the value in that row. So what this means is, inserting 2,1 and 1,2 for\u2026","rel":"","context":"In &quot;Programming&quot;","block_context":{"text":"Programming","link":"https:\/\/wade.one\/blog\/category\/programming\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":665,"url":"https:\/\/wade.one\/blog\/2013\/09\/18\/c-mysqldatareader-one-line-function-to-return-a-row-as-an-instance-of-a-class\/","url_meta":{"origin":437,"position":4},"title":"C# MySqlDataReader &#8211; &#8220;one line&#8221; function to return a row as an instance of a class.","author":"Wade","date":"September 18, 2013","format":false,"excerpt":"MySql in C# is rather painless using the MySQL Connector for .net, but one thing it is missing is a no-frills, no extra requirements, no pre-defined ERD return a row as an instance of a class function. Coming from a PHP background, I love the PDO function fetchObject(), it's just\u2026","rel":"","context":"In &quot;Programming&quot;","block_context":{"text":"Programming","link":"https:\/\/wade.one\/blog\/category\/programming\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":38,"url":"https:\/\/wade.one\/blog\/2009\/09\/03\/todays-musings\/","url_meta":{"origin":437,"position":5},"title":"Today&#8217;s musings","author":"Wade","date":"September 3, 2009","format":false,"excerpt":"Today's work was a standard day, well except for the morning, actually no, that was a standard morning too, things didn't work too well, they needed fixing, during which there were random jokes at Ben's expense due to his pure love of graphs. With some help from Remo (my team\u2026","rel":"","context":"In &quot;Ego-centric&quot;","block_context":{"text":"Ego-centric","link":"https:\/\/wade.one\/blog\/category\/ego-centric\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"https:\/\/wade.one\/blog\/wp-json\/wp\/v2\/posts\/437","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/wade.one\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/wade.one\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/wade.one\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/wade.one\/blog\/wp-json\/wp\/v2\/comments?post=437"}],"version-history":[{"count":0,"href":"https:\/\/wade.one\/blog\/wp-json\/wp\/v2\/posts\/437\/revisions"}],"wp:attachment":[{"href":"https:\/\/wade.one\/blog\/wp-json\/wp\/v2\/media?parent=437"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/wade.one\/blog\/wp-json\/wp\/v2\/categories?post=437"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/wade.one\/blog\/wp-json\/wp\/v2\/tags?post=437"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}