{"id":23419,"date":"2015-01-28T12:47:12","date_gmt":"2015-01-28T10:47:12","guid":{"rendered":"https:\/\/mamchenkov.net\/wordpress\/?p=23419"},"modified":"2015-01-28T12:47:12","modified_gmt":"2015-01-28T10:47:12","slug":"mysql-view-processing-algorithms","status":"publish","type":"post","link":"https:\/\/mamchenkov.net\/wordpress\/2015\/01\/28\/mysql-view-processing-algorithms\/","title":{"rendered":"MySQL view processing algorithms"},"content":{"rendered":"\n

I had a last work session last night, troubleshooting one of the project’s database performance issues. \u00a0Without giving more details (at least for now), I want to save the link to MySQL view processing algorithms<\/a> for future me.<\/p>\n

For UNDEFINED<\/code>, MySQL chooses which algorithm to use. It prefers MERGE<\/code> over TEMPTABLE<\/code> if possible, because MERGE<\/code> is usually more efficient and because a view cannot be updatable if a temporary table is used.<\/p>\n

A reason to choose TEMPTABLE<\/code> explicitly is that locks can be released on underlying tables after the temporary table has been created and before it is used to finish processing the statement. This might result in quicker lock release than the MERGE<\/code> algorithm so that other clients that use the view are not blocked as long.<\/p><\/blockquote>\n

A particular heavy query, using views, kept going into “Copying to tmp table<\/em>” state, locking up the server and slowing everything to a crawl. \u00a0Upon closer examination, the view was created without specifying the algorithm (UNDEFINED). \u00a0Changing the view to use TEMPTABLE made everything so much faster.<\/p>\n

I knew there were reasons for me being against using views in MySQL, but I could never remember them. \u00a0This is one. \u00a0Views not supporting indexes is another.<\/p>\n\n","protected":false},"excerpt":{"rendered":"\n

I had a last work session last night, troubleshooting one of the project’s database performance issues. \u00a0Without giving more details (at least for now), I want to save the link to MySQL view processing algorithms for future me. For UNDEFINED, MySQL chooses which algorithm to use. It prefers MERGE over TEMPTABLE if possible, because MERGE … Continue reading MySQL view processing algorithms<\/span><\/a><\/p>\n\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"MySQL view processing algorithms #databases #MySQL #performance #WebDev","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"_links_to":"","_links_to_target":""},"categories":[1,18,133,62,1334],"tags":[1559,3066,1057,1330],"keyring_services":[],"class_list":["post-23419","post","type-post","status-publish","format-standard","hentry","category-general","category-programming","category-sysadmin","category-technology","category-web-work","tag-databases","tag-mysql","tag-performance","tag-web-development"],"aioseo_notices":[],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack-related-posts":[{"id":27341,"url":"https:\/\/mamchenkov.net\/wordpress\/2017\/02\/12\/github-to-mysql\/","url_meta":{"origin":23419,"position":0},"title":"GitHub to MySQL","author":"Leonid Mamchenkov","date":"February 12, 2017","format":false,"excerpt":"GitHub to MySQL is a handy little app in PHP that pulls labels, milestones and issues from GitHub into your local MySQL database. \u00a0This is useful for analysis and backup purposes. There are a few example queries provided that show issues vs. pull requests,\u00a0average number of days to merge a\u2026","rel":"","context":"In "All"","block_context":{"text":"All","link":"https:\/\/mamchenkov.net\/wordpress\/category\/general\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/mamchenkov.net\/wordpress\/wp-content\/uploads\/2017\/02\/issues-vs-pull-requests-500x229.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":28414,"url":"https:\/\/mamchenkov.net\/wordpress\/2018\/02\/26\/top-10-mysql-8-0-features-for-dbas-ops\/","url_meta":{"origin":23419,"position":1},"title":"TOP 10 MySQL 8.0 features for DBAs & OPS","author":"Leonid Mamchenkov","date":"February 26, 2018","format":false,"excerpt":"Here's a list of the TOP 10 MySQL 8.0 features for DBAs and OPS, with some detailed explanations of what they are and links to more information.\u00a0 The features covered are: Temporary Tables Improvements Persistent global variables No more InnoDB System Tables Reclaim UNDO space from large transactions UTF8 performance\u2026","rel":"","context":"In "All"","block_context":{"text":"All","link":"https:\/\/mamchenkov.net\/wordpress\/category\/general\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":28625,"url":"https:\/\/mamchenkov.net\/wordpress\/2018\/06\/11\/why-uber-engineering-switched-from-postgres-to-mysql\/","url_meta":{"origin":23419,"position":2},"title":"Why Uber Engineering Switched from Postgres to MySQL","author":"Leonid Mamchenkov","date":"June 11, 2018","format":false,"excerpt":"\"Why Uber Engineering Switched from Postgres to MySQL\" is an interesting study with plenty of technical detail of how MySQL was a better choice than PostgreSQL for the very demanding growth of Uber.\u00a0 These kinds of issues are probably way out of scope for any \"regular Joe\" application, but the\u2026","rel":"","context":"In "All"","block_context":{"text":"All","link":"https:\/\/mamchenkov.net\/wordpress\/category\/general\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/mamchenkov.net\/wordpress\/wp-content\/uploads\/2018\/06\/MySQL-indexes-500x243.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":48046,"url":"https:\/\/mamchenkov.net\/wordpress\/2020\/03\/30\/mysql-json-indexing-and-generated-columns\/","url_meta":{"origin":23419,"position":3},"title":"MySQL, JSON, indexing and generated columns","author":"Leonid Mamchenkov","date":"March 30, 2020","format":false,"excerpt":"For quite some time now I wanted to play around with the recently added JSON type in MySQL.\u00a0 Finally, I have a project where MySQL version is high enough to support it, and the requirements are such that this choice makes sense. The first impression was great - JSON type\u2026","rel":"","context":"In "All"","block_context":{"text":"All","link":"https:\/\/mamchenkov.net\/wordpress\/category\/general\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":28508,"url":"https:\/\/mamchenkov.net\/wordpress\/2018\/04\/20\/mysql-8-0-release\/","url_meta":{"origin":23419,"position":4},"title":"MySQL 8.0 release","author":"Leonid Mamchenkov","date":"April 20, 2018","format":false,"excerpt":"MySQL 8.0 has been released and it brings the following new features, enhancements, and more: SQL\u00a0Window functions, Common Table Expressions, NOWAIT and SKIP LOCKED, Descending Indexes, Grouping, Regular Expressions, Character Sets, Cost Model, and Histograms. JSON\u00a0Extended syntax, new functions, improved sorting, and partial updates. With JSON table functions you can\u2026","rel":"","context":"In "All"","block_context":{"text":"All","link":"https:\/\/mamchenkov.net\/wordpress\/category\/general\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":22487,"url":"https:\/\/mamchenkov.net\/wordpress\/2014\/09\/03\/making-mysql-better-at-github\/","url_meta":{"origin":23419,"position":5},"title":"Making MySQL Better at GitHub","author":"Leonid Mamchenkov","date":"September 3, 2014","format":"link","excerpt":"Making MySQL Better at GitHub \u00a0 \u00a0","rel":"","context":"In "All"","block_context":{"text":"All","link":"https:\/\/mamchenkov.net\/wordpress\/category\/general\/"},"img":{"alt_text":"mysql improvements at GitHub","src":"https:\/\/i0.wp.com\/mamchenkov.net\/wordpress\/wp-content\/uploads\/2014\/09\/mysql-improvements-at-GitHub-500x240.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]}],"jetpack_sharing_enabled":true,"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/mamchenkov.net\/wordpress\/wp-json\/wp\/v2\/posts\/23419","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/mamchenkov.net\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mamchenkov.net\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mamchenkov.net\/wordpress\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/mamchenkov.net\/wordpress\/wp-json\/wp\/v2\/comments?post=23419"}],"version-history":[{"count":0,"href":"https:\/\/mamchenkov.net\/wordpress\/wp-json\/wp\/v2\/posts\/23419\/revisions"}],"wp:attachment":[{"href":"https:\/\/mamchenkov.net\/wordpress\/wp-json\/wp\/v2\/media?parent=23419"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mamchenkov.net\/wordpress\/wp-json\/wp\/v2\/categories?post=23419"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mamchenkov.net\/wordpress\/wp-json\/wp\/v2\/tags?post=23419"},{"taxonomy":"keyring_services","embeddable":true,"href":"https:\/\/mamchenkov.net\/wordpress\/wp-json\/wp\/v2\/keyring_services?post=23419"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}