{"id":453,"date":"2010-02-26T11:37:58","date_gmt":"2010-02-26T11:37:58","guid":{"rendered":"https:\/\/wade.one\/blog\/?p=453"},"modified":"2010-02-26T11:37:58","modified_gmt":"2010-02-26T11:37:58","slug":"phpuk2010-part-2-mysql-stuff","status":"publish","type":"post","link":"https:\/\/wade.one\/blog\/2010\/02\/26\/phpuk2010-part-2-mysql-stuff\/","title":{"rendered":"#PHPUK2010 Part 2 (MySQL stuff)"},"content":{"rendered":"

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 example would result in only the first of the two inserts succeeding.<\/p>\n

CREATE UNIQUE INDEX ON tablename (LEAST(col1,col2), GREATEST(col1,col2));<\/code><\/p>\n

Also, WITH, I’ll be honest, never thought about using it to create temporary views. This is a bad example but shows the structure rather well:<\/p>\n

WITH tempView (a,b) AS (
\nSELECT table1.col1, table2.col2
\nFROM table1
\nLEFT JOIN table2
\nON table1.id=table2.id
\n)
\nSELECT a,b FROM tempView;<\/code><\/p>\n

Better yet is changing this to WITH RECURSIVE tempView and then adding in a select inside the WITH that recalls tempView. The great example he gave is for getting flights from A to B with a varying\u00a0 amount of stops, it would be possible to get all routes from A to B with one MySQL query, as long as the data stored all connecting routes.<\/p>\n

Incidentally, while there is some great stuff coming out of this RDBMS talk, I think the queries are really hurting a lot of people’s heads. Good stuff though.<\/p>\n","protected":false},"excerpt":{"rendered":"

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 example would result in only … Read More “#PHPUK2010 Part 2 (MySQL stuff)”<\/span> »<\/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-453","post","type-post","status-publish","format-standard","hentry","category-programming","tag-php-conference-2010"],"yoast_head":"\n#PHPUK2010 Part 2 (MySQL stuff) - 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\/02\/26\/phpuk2010-part-2-mysql-stuff\/\" \/>\n<meta property=\"og:locale\" content=\"en_GB\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"#PHPUK2010 Part 2 (MySQL stuff) - wade.one\" \/>\n<meta property=\"og:description\" content=\"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 example would result in only ... Read More "#PHPUK2010 Part 2 (MySQL stuff)" »\" \/>\n<meta property=\"og:url\" content=\"https:\/\/wade.one\/blog\/2010\/02\/26\/phpuk2010-part-2-mysql-stuff\/\" \/>\n<meta property=\"og:site_name\" content=\"wade.one\" \/>\n<meta property=\"article:published_time\" content=\"2010-02-26T11:37:58+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=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/wade.one\\\/blog\\\/2010\\\/02\\\/26\\\/phpuk2010-part-2-mysql-stuff\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/wade.one\\\/blog\\\/2010\\\/02\\\/26\\\/phpuk2010-part-2-mysql-stuff\\\/\"},\"author\":{\"name\":\"Wade\",\"@id\":\"https:\\\/\\\/wade.one\\\/blog\\\/#\\\/schema\\\/person\\\/f9dedd948575256e77a44aa1417f63de\"},\"headline\":\"#PHPUK2010 Part 2 (MySQL stuff)\",\"datePublished\":\"2010-02-26T11:37:58+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/wade.one\\\/blog\\\/2010\\\/02\\\/26\\\/phpuk2010-part-2-mysql-stuff\\\/\"},\"wordCount\":184,\"commentCount\":0,\"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\\\/02\\\/26\\\/phpuk2010-part-2-mysql-stuff\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/wade.one\\\/blog\\\/2010\\\/02\\\/26\\\/phpuk2010-part-2-mysql-stuff\\\/\",\"url\":\"https:\\\/\\\/wade.one\\\/blog\\\/2010\\\/02\\\/26\\\/phpuk2010-part-2-mysql-stuff\\\/\",\"name\":\"#PHPUK2010 Part 2 (MySQL stuff) - wade.one\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/wade.one\\\/blog\\\/#website\"},\"datePublished\":\"2010-02-26T11:37:58+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/wade.one\\\/blog\\\/2010\\\/02\\\/26\\\/phpuk2010-part-2-mysql-stuff\\\/#breadcrumb\"},\"inLanguage\":\"en-GB\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/wade.one\\\/blog\\\/2010\\\/02\\\/26\\\/phpuk2010-part-2-mysql-stuff\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/wade.one\\\/blog\\\/2010\\\/02\\\/26\\\/phpuk2010-part-2-mysql-stuff\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/wade.one\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"#PHPUK2010 Part 2 (MySQL stuff)\"}]},{\"@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":"#PHPUK2010 Part 2 (MySQL stuff) - 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\/02\/26\/phpuk2010-part-2-mysql-stuff\/","og_locale":"en_GB","og_type":"article","og_title":"#PHPUK2010 Part 2 (MySQL stuff) - wade.one","og_description":"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 example would result in only ... Read More \"#PHPUK2010 Part 2 (MySQL stuff)\" »","og_url":"https:\/\/wade.one\/blog\/2010\/02\/26\/phpuk2010-part-2-mysql-stuff\/","og_site_name":"wade.one","article_published_time":"2010-02-26T11:37:58+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":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/wade.one\/blog\/2010\/02\/26\/phpuk2010-part-2-mysql-stuff\/#article","isPartOf":{"@id":"https:\/\/wade.one\/blog\/2010\/02\/26\/phpuk2010-part-2-mysql-stuff\/"},"author":{"name":"Wade","@id":"https:\/\/wade.one\/blog\/#\/schema\/person\/f9dedd948575256e77a44aa1417f63de"},"headline":"#PHPUK2010 Part 2 (MySQL stuff)","datePublished":"2010-02-26T11:37:58+00:00","mainEntityOfPage":{"@id":"https:\/\/wade.one\/blog\/2010\/02\/26\/phpuk2010-part-2-mysql-stuff\/"},"wordCount":184,"commentCount":0,"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\/02\/26\/phpuk2010-part-2-mysql-stuff\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/wade.one\/blog\/2010\/02\/26\/phpuk2010-part-2-mysql-stuff\/","url":"https:\/\/wade.one\/blog\/2010\/02\/26\/phpuk2010-part-2-mysql-stuff\/","name":"#PHPUK2010 Part 2 (MySQL stuff) - wade.one","isPartOf":{"@id":"https:\/\/wade.one\/blog\/#website"},"datePublished":"2010-02-26T11:37:58+00:00","breadcrumb":{"@id":"https:\/\/wade.one\/blog\/2010\/02\/26\/phpuk2010-part-2-mysql-stuff\/#breadcrumb"},"inLanguage":"en-GB","potentialAction":[{"@type":"ReadAction","target":["https:\/\/wade.one\/blog\/2010\/02\/26\/phpuk2010-part-2-mysql-stuff\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/wade.one\/blog\/2010\/02\/26\/phpuk2010-part-2-mysql-stuff\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/wade.one\/blog\/"},{"@type":"ListItem","position":2,"name":"#PHPUK2010 Part 2 (MySQL stuff)"}]},{"@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":453,"position":0},"title":"MySQL – 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 "Programming"","block_context":{"text":"Programming","link":"https:\/\/wade.one\/blog\/category\/programming\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":437,"url":"https:\/\/wade.one\/blog\/2010\/01\/31\/mysql-and-binary16-the-reasonsbenefitsdrawbacks-mysql\/","url_meta":{"origin":453,"position":1},"title":"MySQL and Binary(16) – The Reasons\/Benefits\/Drawbacks (#mysql)","author":"Wade","date":"January 31, 2010","format":false,"excerpt":"I recently posted an article about using BINARY(16) for storing MD5's as unique identifiers instead of simple integer ID's (usually auto increment); in that article I touched on one of the benefits, reducing JOIN's, but there are other reasons for doing it too, so I thought I'd post an article\u2026","rel":"","context":"In "Programming"","block_context":{"text":"Programming","link":"https:\/\/wade.one\/blog\/category\/programming\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":938,"url":"https:\/\/wade.one\/blog\/2015\/07\/06\/cloudant-ibm-query-indexing-arrays-directly-using-erlang\/","url_meta":{"origin":453,"position":2},"title":"Cloudant (IBM) Query – Indexing arrays directly using Erlang","author":"Wade","date":"July 6, 2015","format":false,"excerpt":"Really short post this one on indexing using Cloudant Query\u00a0to index arrays in your document, e.g. documents that have a field like this: { \"ids\": [ \"alpha\", \"bravo\", \"charlie\" ] } I was reading the documentation and just could not get it to index these items using anything but the\u2026","rel":"","context":"In "Programming"","block_context":{"text":"Programming","link":"https:\/\/wade.one\/blog\/category\/programming\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/wade.one\/blog\/wp-content\/uploads\/2015\/07\/SmJYeZ0r_400x400.png?resize=350%2C200&ssl=1","width":350,"height":200},"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":453,"position":3},"title":"C# MySqlDataReader – “one line” 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 "Programming"","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":453,"position":4},"title":"Today’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 "Ego-centric"","block_context":{"text":"Ego-centric","link":"https:\/\/wade.one\/blog\/category\/ego-centric\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":629,"url":"https:\/\/wade.one\/blog\/2012\/10\/15\/using-amazon-ec2-with-c-net\/","url_meta":{"origin":453,"position":5},"title":"Using Amazon EC2 with C# .net","author":"Wade","date":"October 15, 2012","format":false,"excerpt":"Amazon's EC2 platform has a very in-depth console which allows you to do a lot, but what if you want to automate some of the processes or handling servers? I've been working with EC2 a lot recently and automating server starting\/stopping based on parameters of the system we use here\u2026","rel":"","context":"In "Programming"","block_context":{"text":"Programming","link":"https:\/\/wade.one\/blog\/category\/programming\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"https:\/\/wade.one\/blog\/wp-json\/wp\/v2\/posts\/453","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=453"}],"version-history":[{"count":0,"href":"https:\/\/wade.one\/blog\/wp-json\/wp\/v2\/posts\/453\/revisions"}],"wp:attachment":[{"href":"https:\/\/wade.one\/blog\/wp-json\/wp\/v2\/media?parent=453"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/wade.one\/blog\/wp-json\/wp\/v2\/categories?post=453"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/wade.one\/blog\/wp-json\/wp\/v2\/tags?post=453"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}