{"id":95039,"date":"2022-11-10T21:31:54","date_gmt":"2022-11-10T21:31:54","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=95039"},"modified":"2022-11-10T21:31:54","modified_gmt":"2022-11-10T21:31:54","slug":"optimizing-mysql-the-basics-of-query-optimization","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/optimizing-mysql-the-basics-of-query-optimization\/","title":{"rendered":"Optimizing MySQL: The Basics of Query Optimization"},"content":{"rendered":"<p>MySQL is a very interesting beast to deal with \u2013 both junior developers and expert database administrators working with the RDBMS know that the world of MySQL offers a breadth of opportunities not found anywhere else: people can choose a storage engine to use from a list of powerful storage engines, the RDBMS can be heavily optimized to serve very specific needs, MySQL has multiple specific types of tools and operations in place that can be used to optimize query performance, etc.<\/p>\n<p>One of the most frequent causes of headaches for database administrators for ages are queries and their optimization for speed. As old as this question is, it\u2019s still very hot. <a href=\"https:\/\/stackoverflow.com\/questions\/tagged\/query-optimization\">Have a skim through database-related questions posted on StackOverflow within the present week<\/a> \u2013 do you see any patterns? \u201cSlow query\u2026\u201d, \u201cPerformance Improvement\u2026\u201c, \u201cIndexing strategy for..\u201d, \u201cPreventing toxic selects\u201d, \u201cDatabase not using index.\u201d All of those questions mean one thing \u2013 issues concerning database performance are still very important, and people want to know how best to work with their queries to achieve the best mix of performance, availability, security, and capacity.<\/p>\n<p>In the upcoming series, we\u2019re focusing on the optimization of MySQL \u2013 we\u2019re going to walk you through storage engine and query optimization, we\u2019re going to tell you a couple of secrets situated around MySQL optimization settings, how best to measure performance, amongst other things. We\u2019re starting small \u2013 in this blog, we\u2019re walking you through the basics of the basics and answering the age-old question \u2013 <strong>why are my queries slow<\/strong>?!<\/p>\n<h1>Why Your Queries Are Slow<\/h1>\n<p>MySQL offers a couple of types of queries we can choose from:<\/p>\n<ul>\n<li><code>INSERT<\/code> queries that insert data into our database;<\/li>\n<li><code>SELECT<\/code> queries that read through data;<\/li>\n<li><code>UPDATE<\/code> queries that update our data;<\/li>\n<li><code>DELETE<\/code> queries that delete data within our database;<\/li>\n<li><code>ALTER<\/code> queries that change (alter, hence the name) data within our database.<\/li>\n<\/ul>\n<p>The main reasons those queries are slow are as follows:<\/p>\n<ul>\n<li><code>INSERT<\/code> queries are slow most likely because there is a lot of data to be inserted into the database or (and) because the database isn\u2019t optimized properly.<\/li>\n<li><code>SELECT<\/code> queries are slow mainly because they read through a lot of unnecessary data. The less data there is to scan through, the faster our <code>SELECT<\/code> queries will become. That\u2019s it \u2013 there\u2019s no rocket science involved.<\/li>\n<li><code>UPDATE<\/code> queries are usually slow because we either have an index on the column that is being updated or we are executing a huge update query without familiarizing ourselves with the internals of storage engines \u2013 <em>InnoDB usually locks the table when <code>UPDATE<\/code>s are being performed and unlocks it after the fact.<\/em><\/li>\n<li><code>DELETE<\/code> queries might be slow because of an index on the column that we\u2019re deleting the data from or because we\u2019re deleting massive amounts of data without knowing the internals of storage engines as well. However, unlike <code>UPDATE<\/code> queries, <code>DELETE<\/code> queries have a couple of tricks up their sleeve \u2013 they can be massively sped up if we use the <code>TRUNCATE<\/code> query to remove all of the rows at once, then reimport only the necessary data.<\/li>\n<li><code>ALTER<\/code> queries are usually slow because of improperly optimized operations that run internally:\n<ul>\n<li><code>ALTER<\/code> queries first make a copy of the table.<\/li>\n<li>Once the copy of the table is made, the data existing in the original table is copied into it.<\/li>\n<li>All of the necessary changes are made.<\/li>\n<li>The original table is swapped with the copy.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<p>All of those operations consume time \u2013 time that must be accounted for.<\/p>\n<p>Some of such operations can be optimized for performance by properly adjusting the buffer pool used by the InnoDB storage engine, others can be optimized by adding or removing the presence of indexes and partitions, and all of them can be optimized by understanding the internals of queries available within MySQL. For now, though, understand one thing \u2013 <strong>queries are processes comprised of tasks, and to optimize the performance of those processes, we must optimize the tasks within those processes.<\/strong><\/p>\n<p>We hope that you\u2019ve enjoyed reading this article and that it helped you grasp the basics of query optimization in the database management system of MySQL \u2013 stay tuned to learn how to optimize tasks within processes, <a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/mysql\/\">read our blog to learn about database management systems in more detail<\/a>, and until next time.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In this blog, we will walk you through the basics of query optimization in MySQL. Have a read!&hellip;<\/p>\n","protected":false},"author":339547,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[4168,5854,5380,48482],"coauthors":[146040],"class_list":["post-95039","post","type-post","status-publish","format-standard","hentry","category-blogs","tag-database","tag-mysql","tag-optimization","tag-query-optimization"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/95039","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/339547"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=95039"}],"version-history":[{"count":4,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/95039\/revisions"}],"predecessor-version":[{"id":95043,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/95039\/revisions\/95043"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=95039"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=95039"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=95039"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=95039"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}