{"id":12417,"date":"2023-11-20T00:12:22","date_gmt":"2023-11-20T07:12:22","guid":{"rendered":"https:\/\/www.mysqltutorial.org\/?page_id=12417"},"modified":"2023-11-20T00:14:27","modified_gmt":"2023-11-20T07:14:27","slug":"mysql-analyze-table","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-administration\/mysql-analyze-table\/","title":{"rendered":"MySQL ANALYZE TABLE Statement"},"content":{"rendered":"\n<p><strong>Summary<\/strong>: in this tutorial, you will learn how to use the MySQL <code>ANALYZE TABLE<\/code> statement to ensure that the query optimizer has accurate and up-to-date table statistics.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Introduction to MySQL ANALYZE TABLE statement<\/h2>\n\n\n\n<p>In MySQL, the query optimizer relies on table statistics to optimize query execution plans. <\/p>\n\n\n\n<p>The table statistics help the query optimizer estimate the number of rows in a table that satisfy a particular condition.<\/p>\n\n\n\n<p>However, sometimes the table statistics can be inaccurate. For example, after you have done a lot of data changes in the table such as <a href=\"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-insert\/\">inserting<\/a>,&nbsp;<a href=\"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-delete\/\">deleting<\/a>, or&nbsp;<a href=\"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-update\/\">updating<\/a>. <\/p>\n\n\n\n<p>If the table statistics are not accurate, the query optimizer may pick a non-optimal query execution plan that may cause a severe performance issue.<\/p>\n\n\n\n<p>To address this issue, MySQL provides the <code>ANALYZE TABLE<\/code> statement that updates these statistics, ensuring that the query optimizer has accurate information for efficient query planning.<\/p>\n\n\n\n<p>The following <code>ANALYZE TABLE<\/code> statement performs a key distribution analysis:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-1\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">ANALYZE<\/span> <span class=\"hljs-keyword\">TABLE<\/span> table_name &#91;, table_name];<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-1\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>In this syntax:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>table_name<\/code>: The name of the table that you want to analyze. If you want to analyze multiple tables, you separate them by commas.<\/li>\n<\/ul>\n\n\n\n<p>This key distribution analysis is essential for understanding the distribution of key values within the table. The query optimizer uses the results of this statement to optimize join operations and index usage.<\/p>\n\n\n\n<p>The <code>ANANLYZE TABLE<\/code> statement works only with <code><a href=\"https:\/\/www.mysqltutorial.org\/mysql-administration\/mysql-innodb-storage-engine\/\">InnoDB<\/a><\/code>, <code>NDB<\/code>, and <code><a href=\"https:\/\/www.mysqltutorial.org\/mysql-administration\/mysql-myisam\/\">MyISAM<\/a><\/code> tables. <\/p>\n\n\n\n<h2 class=\"wp-block-heading\">MySQL ANALYZE TABLE statement example<\/h2>\n\n\n\n<p>We&#8217;ll use the table from the <a href=\"https:\/\/www.mysqltutorial.org\/getting-started-with-mysql\/mysql-sample-database\/\">sample database<\/a> for the demonstration.<\/p>\n\n\n\n<p>First, log in to the MySQL Server using the <code>root<\/code> account:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-2\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">mysql -u root -p<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-2\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>It&#8217;ll prompt you to enter a password for the <code>root<\/code> account.<\/p>\n\n\n\n<p>Second, switch the current database to <code>classicmodels<\/code>:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-3\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">use<\/span> classicmodels;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-3\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Third, analyze the <code>customers<\/code> table:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-4\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">analyze<\/span> <span class=\"hljs-keyword\">table<\/span> customers;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-4\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Output:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-5\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">+<span class=\"hljs-comment\">-------------------------+---------+----------+----------+<\/span>\n| Table                   | Op      | Msg_type | Msg_text |\n+<span class=\"hljs-comment\">-------------------------+---------+----------+----------+<\/span>\n| classicmodels.customers | <span class=\"hljs-keyword\">analyze<\/span> | <span class=\"hljs-keyword\">status<\/span>   | OK       |\n+<span class=\"hljs-comment\">-------------------------+---------+----------+----------+<\/span>\n<span class=\"hljs-number\">1<\/span> <span class=\"hljs-keyword\">row<\/span> <span class=\"hljs-keyword\">in<\/span> <span class=\"hljs-keyword\">set<\/span> (<span class=\"hljs-number\">0.01<\/span> sec)<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-5\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>The output table has the following columns:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>Table<\/code>: The table name that was analyzed.<\/li>\n\n\n\n<li><code>op<\/code>: analyze or histogram.<\/li>\n\n\n\n<li><code>Msg_type<\/code>: show the message type including status, error, info, note, or warning.<\/li>\n\n\n\n<li><code>Msg_text<\/code>: an informational message.<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">Summary<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Use the MySQL <code>ANALYZE<\/code> <code>TABLE<\/code> statement to ensure that the query optimizer has accurate and up-to-date table statistics, allowing it to generate optimal query execution plans.<\/li>\n<\/ul>\n<div class=\"helpful-block-content\" data-title=\"\">\n\t<header>\n\t\t<div class=\"wth-question\">Was this tutorial helpful? <\/div>\n\t\t<div class=\"wth-thumbs\">\n\t\t\t<button\n\t\t\t\tdata-post=\"12417\"\n\t\t\t\tdata-post-url=\"https:\/\/www.mysqltutorial.org\/mysql-administration\/mysql-analyze-table\/\"\n\t\t\t\tdata-post-title=\"MySQL ANALYZE TABLE Statement\"\n\t\t\t\tdata-response=\"1\"\n\t\t\t\tclass=\"wth-btn-rounded wth-yes-btn\"\n\t\t\t>\n\t\t\t\t<svg\n\t\t\t\t\txmlns=\"http:\/\/www.w3.org\/2000\/svg\"\n\t\t\t\t\tviewBox=\"0 0 24 24\"\n\t\t\t\t\tfill=\"none\"\n\t\t\t\t\tstroke=\"currentColor\"\n\t\t\t\t\tstroke-width=\"2\"\n\t\t\t\t\tstroke-linecap=\"round\"\n\t\t\t\t\tstroke-linejoin=\"round\"\n\t\t\t\t\tclass=\"feather feather-thumbs-up block w-full h-full\"\n\t\t\t\t>\n\t\t\t\t\t<path\n\t\t\t\t\t\td=\"M14 9V5a3 3 0 0 0-3-3l-4 9v11h11.28a2 2 0 0 0 2-1.7l1.38-9a2 2 0 0 0-2-2.3zM7 22H4a2 2 0 0 1-2-2v-7a2 2 0 0 1 2-2h3\"\n\t\t\t\t\t><\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t<span class=\"sr-only\"> Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t<button\n\t\t\t\tdata-response=\"0\"\n\t\t\t\tdata-post=\"12417\"\n\t\t\t\tdata-post-url=\"https:\/\/www.mysqltutorial.org\/mysql-administration\/mysql-analyze-table\/\"\n\t\t\t\tdata-post-title=\"MySQL ANALYZE TABLE Statement\"\n\t\t\t\tclass=\"wth-btn-rounded wth-no-btn\"\n\t\t\t>\n\t\t\t\t<svg\n\t\t\t\t\txmlns=\"http:\/\/www.w3.org\/2000\/svg\"\n\t\t\t\t\tviewBox=\"0 0 24 24\"\n\t\t\t\t\tfill=\"none\"\n\t\t\t\t\tstroke=\"currentColor\"\n\t\t\t\t\tstroke-width=\"2\"\n\t\t\t\t\tstroke-linecap=\"round\"\n\t\t\t\t\tstroke-linejoin=\"round\"\n\t\t\t\t>\n\t\t\t\t\t<path\n\t\t\t\t\t\td=\"M10 15v4a3 3 0 0 0 3 3l4-9V2H5.72a2 2 0 0 0-2 1.7l-1.38 9a2 2 0 0 0 2 2.3zm7-13h2.67A2.31 2.31 0 0 1 22 4v7a2.31 2.31 0 0 1-2.33 2H17\"\n\t\t\t\t\t><\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t<span class=\"sr-only\"> No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t<div class=\"wth-form hidden\">\n\t\t<div class=\"wth-form-wrapper\">\n\t\t\t<div class=\"wth-title\"><\/div>\n\t\t\t\n\t\t\t<textarea class=\"wth-message\"><\/textarea>\n\n\t\t\t<button class=\"btn btn-primary wth-btn-submit\">Send<\/button>\n\t\t\t<button class=\"btn wth-btn-cancel\">Cancel<\/button>\n\t\t\n\t\t<\/div>\n\t<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>In this tutorial, you will learn how to use the MySQL ANALYZE TABLE statement to ensure that the query optimizer has accurate and up-to-date table statistics.<\/p>\n","protected":false},"author":2,"featured_media":0,"parent":441,"menu_order":54,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-12417","page","type-page","status-publish","hentry"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.5 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>MySQL ANALYZE TABLE Statement<\/title>\n<meta name=\"description\" content=\"You will learn how to use the MySQL ANALYZE TABLE statement to ensure that the query optimizer has accurate and up-to-date table statistics.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.mysqltutorial.org\/mysql-administration\/mysql-analyze-table\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"MySQL ANALYZE TABLE Statement\" \/>\n<meta property=\"og:description\" content=\"You will learn how to use the MySQL ANALYZE TABLE statement to ensure that the query optimizer has accurate and up-to-date table statistics.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.mysqltutorial.org\/mysql-administration\/mysql-analyze-table\/\" \/>\n<meta property=\"og:site_name\" content=\"MySQL Tutorial\" \/>\n<meta property=\"article:modified_time\" content=\"2023-11-20T07:14:27+00:00\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-administration\\\/mysql-analyze-table\\\/\",\"url\":\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-administration\\\/mysql-analyze-table\\\/\",\"name\":\"MySQL ANALYZE TABLE Statement\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.mysqltutorial.org\\\/#website\"},\"datePublished\":\"2023-11-20T07:12:22+00:00\",\"dateModified\":\"2023-11-20T07:14:27+00:00\",\"description\":\"You will learn how to use the MySQL ANALYZE TABLE statement to ensure that the query optimizer has accurate and up-to-date table statistics.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-administration\\\/mysql-analyze-table\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-administration\\\/mysql-analyze-table\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-administration\\\/mysql-analyze-table\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.mysqltutorial.org\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"MySQL Administration\",\"item\":\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-administration\\\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"MySQL ANALYZE TABLE Statement\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.mysqltutorial.org\\\/#website\",\"url\":\"https:\\\/\\\/www.mysqltutorial.org\\\/\",\"name\":\"MySQL Tutorial\",\"description\":\"A comprehensive MySQL Tutorial\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.mysqltutorial.org\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"MySQL ANALYZE TABLE Statement","description":"You will learn how to use the MySQL ANALYZE TABLE statement to ensure that the query optimizer has accurate and up-to-date table statistics.","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:\/\/www.mysqltutorial.org\/mysql-administration\/mysql-analyze-table\/","og_locale":"en_US","og_type":"article","og_title":"MySQL ANALYZE TABLE Statement","og_description":"You will learn how to use the MySQL ANALYZE TABLE statement to ensure that the query optimizer has accurate and up-to-date table statistics.","og_url":"https:\/\/www.mysqltutorial.org\/mysql-administration\/mysql-analyze-table\/","og_site_name":"MySQL Tutorial","article_modified_time":"2023-11-20T07:14:27+00:00","twitter_misc":{"Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.mysqltutorial.org\/mysql-administration\/mysql-analyze-table\/","url":"https:\/\/www.mysqltutorial.org\/mysql-administration\/mysql-analyze-table\/","name":"MySQL ANALYZE TABLE Statement","isPartOf":{"@id":"https:\/\/www.mysqltutorial.org\/#website"},"datePublished":"2023-11-20T07:12:22+00:00","dateModified":"2023-11-20T07:14:27+00:00","description":"You will learn how to use the MySQL ANALYZE TABLE statement to ensure that the query optimizer has accurate and up-to-date table statistics.","breadcrumb":{"@id":"https:\/\/www.mysqltutorial.org\/mysql-administration\/mysql-analyze-table\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.mysqltutorial.org\/mysql-administration\/mysql-analyze-table\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.mysqltutorial.org\/mysql-administration\/mysql-analyze-table\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.mysqltutorial.org\/"},{"@type":"ListItem","position":2,"name":"MySQL Administration","item":"https:\/\/www.mysqltutorial.org\/mysql-administration\/"},{"@type":"ListItem","position":3,"name":"MySQL ANALYZE TABLE Statement"}]},{"@type":"WebSite","@id":"https:\/\/www.mysqltutorial.org\/#website","url":"https:\/\/www.mysqltutorial.org\/","name":"MySQL Tutorial","description":"A comprehensive MySQL Tutorial","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.mysqltutorial.org\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"}]}},"_links":{"self":[{"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/pages\/12417","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/comments?post=12417"}],"version-history":[{"count":5,"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/pages\/12417\/revisions"}],"predecessor-version":[{"id":12424,"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/pages\/12417\/revisions\/12424"}],"up":[{"embeddable":true,"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/pages\/441"}],"wp:attachment":[{"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/media?parent=12417"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}