{"id":198,"date":"2023-06-18T19:25:11","date_gmt":"2023-06-18T19:25:11","guid":{"rendered":"https:\/\/wp-yoda.com\/en\/?p=198"},"modified":"2024-03-08T22:44:12","modified_gmt":"2024-03-08T22:44:12","slug":"clustered-and-secondary-indexes","status":"publish","type":"post","link":"https:\/\/wp-yoda.com\/en\/mysql\/clustered-and-secondary-indexes\/","title":{"rendered":"Clustered and secondary indexes for relational databases"},"content":{"rendered":"\n<p>In this article, we will consider the difference between clustered and secondary indexes. We will get acquainted with the B-Tree data structure in which indexes are stored.<\/p>\n\n\n  \r\n    \r\n<div id=\"wpj-jtoc\" class=\"wpj-jtoc wpj-jtoc--main --jtoc-the-content --jtoc-theme-basic-light --jtoc-title-align-left --jtoc-toggle-icon --jtoc-toggle-position-right --jtoc-toggle-1 --jtoc-has-numeration --jtoc-numeration-legacy --jtoc-has-custom-styles --jtoc-is-unfolded --jtoc-align-left\" >\r\n  <!-- TOC -->\r\n        <div class=\"wpj-jtoc--toc wpj-jtoc--toc-inline \" >\r\n              <div class=\"wpj-jtoc--header\">\r\n        <div class=\"wpj-jtoc--header-main\">\r\n                    <div class=\"wpj-jtoc--title\">\r\n                        <span class=\"wpj-jtoc--title-label\">Table of contents<\/span>\r\n          <\/div>\r\n                                <div class=\"wpj-jtoc--toggle-wrap\">\r\n                                                          <div class=\"wpj-jtoc--toggle-box\">\r\n                  <div class=\"wpj-jtoc--toggle\"><\/div>\r\n                <\/div>\r\n                          <\/div>\r\n                  <\/div>\r\n      <\/div>\r\n            <div class=\"wpj-jtoc--body\">\r\n            <nav class=\"wpj-jtoc--nav\">\r\n        <ol class=\"wpj-jtoc--items\"><li class=\"wpj-jtoc--item --jtoc-h2\">\r\n        <div class=\"wpj-jtoc--item-content --jtoc-h2\" data-depth=\"2\">\r\n                                                <a href=\"#database-index\"  title=\"Database index\" data-numeration=\"1\">Database index<\/a>\r\n                    <\/div><\/li><li class=\"wpj-jtoc--item --jtoc-h2\">\r\n        <div class=\"wpj-jtoc--item-content --jtoc-h2\" data-depth=\"2\">\r\n                                                <a href=\"#clustered-index\"  title=\"Clustered index\" data-numeration=\"2\">Clustered index<\/a>\r\n                    <\/div><ol class=\"wpj-jtoc--items\"><li class=\"wpj-jtoc--item --jtoc-h3\">\r\n        <div class=\"wpj-jtoc--item-content --jtoc-h3\" data-depth=\"3\">\r\n                                                <a href=\"#b-tree\"  title=\"B-Tree\" data-numeration=\"2.1\">B-Tree<\/a>\r\n                    <\/div><\/li><li class=\"wpj-jtoc--item --jtoc-h3\">\r\n        <div class=\"wpj-jtoc--item-content --jtoc-h3\" data-depth=\"3\">\r\n                                                <a href=\"#mysql-innodb-peculiarity\"  title=\"MySQL InnoDB peculiarity\" data-numeration=\"2.2\">MySQL InnoDB peculiarity<\/a>\r\n                    <\/div><\/li><li class=\"wpj-jtoc--item --jtoc-h3\">\r\n        <div class=\"wpj-jtoc--item-content --jtoc-h3\" data-depth=\"3\">\r\n                                                <a href=\"#mysql-myisam-peculiarity\"  title=\"MySQL MyISAM peculiarity\" data-numeration=\"2.3\">MySQL MyISAM peculiarity<\/a>\r\n                    <\/div><\/li><\/ol><\/li><li class=\"wpj-jtoc--item --jtoc-h2\">\r\n        <div class=\"wpj-jtoc--item-content --jtoc-h2\" data-depth=\"2\">\r\n                                                <a href=\"#secondary-index\"  title=\"Secondary index\" data-numeration=\"3\">Secondary index<\/a>\r\n                    <\/div><\/li><\/ol>      <\/nav>\r\n          <\/div>\r\n      <\/div>\r\n    <\/div>\r\n\n\n\n<h2 class=\"wp-block-heading\">Database index<\/h2>\n\n\n\n<p><strong>Database index<\/strong>&nbsp;\u2014 an object of DB created with the goal of improving search data performance.<\/p>\n\n\n\n<p>Since tables in a database may contain many rows stored in random order, searching for rows according to the specified criteria can be time-consuming when you browse through the table sequentially. An index is created based on the values of one or more table columns along with pointers to the corresponding table rows, which allows you to quickly find rows that meet the search criteria. The use of an index improves speed due to its search-optimized structure, such as a balanced B-Tree.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Clustered index<\/h2>\n\n\n\n<p>Clustered index&nbsp;\u2013 a type of index in DBMS with a tree structure, where index values together with data are stored as an ordered tree, usually as a balanced search tree &#8211; B-Tree (or its B-Tree variations).<br>In a clustered index, each level of the tree represents index pages, and the end pages (leaves, Leaf) contain the actual table row data.<\/p>\n\n\n\n<p>Consider the <strong>clustered index<\/strong> (Fig.1) built on a column <code>post_id<\/code> (unique identifier of post an article). <\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><a href=\"https:\/\/wp-yoda.com\/wp-content\/uploads\/2023\/06\/image-54.png\"><img decoding=\"async\" src=\"https:\/\/wp-yoda.com\/wp-content\/uploads\/2023\/06\/image-54-1024x367.png\" alt=\"Clustered index of relational database\" class=\"wp-image-1046\"\/><\/a><figcaption class=\"wp-element-caption\">Fig.1 &#8211; simplified form of cluster index based on B-tree data structure<\/figcaption><\/figure>\n\n\n\n<p>The structure of the clustered index is as follows:<\/p>\n\n\n\n<ol class=\"wp-block-list\" style=\"margin-top:0;margin-right:0;margin-bottom:0;margin-left:0\">\n<li><strong>Root node<\/strong> &#8211; tree top, containing sorted index values and pointers to child levels. The root level does not store data.<br>The root level provides the starting point of the search when performing a query.<\/li>\n\n\n\n<li><strong>Intermediate nodes<\/strong> &#8211; store sorted index values and pointers to child levels (in our case Leafs). Intermediate levels do not store column data.<\/li>\n\n\n\n<li><strong>Leaf nodes<\/strong> &#8211; the lowest levels of the tree containing the full table rows ( columns with data ). Leaves are stored in the index-ordered, fragmented form on disk.<\/li>\n<\/ol>\n\n\n\n<p><strong>Let&#8217;s look at an example:<\/strong><br><br>The database search starts with the <code>Root node<\/code> , after finding in the list that the value we are looking for is 5 &#8211; it is the range between 5-6, then we go to the <code>Intermediate node<\/code> in it, we find a pointer to the <code>Leaf node<\/code> , where all data about <code>post_id=5<\/code> is stored.<\/p>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p><\/p>\n<cite>This example was simplified because in most cases the range in the lists will be much wider (for example, the first row from 1-100, and the second row from 101-200), and the depth of the tree will have more levels.<\/cite><\/blockquote>\n\n\n\n<blockquote class=\"wp-block-quote has-highlight-background-color has-background is-layout-flow wp-block-quote-is-layout-flow\">\n<p><\/p>\n<cite>The cluster index can be only one for each table, because it is used to sort and fragment the data of the whole table on disk.<\/cite><\/blockquote>\n\n\n\n<h3 class=\"wp-block-heading\">B-Tree<\/h3>\n\n\n\n<p><strong>The B-Tree structure<\/strong> allows you to quickly and efficiently search, insert, delete, and update rows based on the key values of the indexed columns.<br>The complexity of the B-Tree algorithm is O(log n).<br><br>The table below is an illustrative example of how many comparisons you need to make to find a record in a database table with a different number of rows:<\/p>\n\n\n\n<figure class=\"wp-block-table\" style=\"margin-top:var(--wp--preset--spacing--20);margin-right:var(--wp--preset--spacing--20);margin-bottom:var(--wp--preset--spacing--20);margin-left:var(--wp--preset--spacing--20)\"><table><tbody><tr><td><strong>Lines in the table<\/strong><\/td><td><strong>Number of comparisons<\/strong><\/td><\/tr><tr><td>10<\/td><td>3,32<\/td><\/tr><tr><td>100<\/td><td>6,64<\/td><\/tr><tr><td>1 000<\/td><td>9,96<\/td><\/tr><tr><td>10 000<\/td><td>13,28<\/td><\/tr><tr><td>100 000<\/td><td>16,61<\/td><\/tr><tr><td>1 000 000<\/td><td>19,93<\/td><\/tr><tr><td>100 000 000<\/td><td>26,57<\/td><\/tr><\/tbody><\/table><figcaption class=\"wp-element-caption\">Number of operations in the B-Tree search by O(Log n)<\/figcaption><\/figure>\n\n\n\n<p>When you add a new row to the table, it is added not to the end of the file, not to the end of the flat list, but to the right branch and node of the tree structure that corresponds to it in terms of sorting. Sometimes the tree performs rebalancing.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">MySQL InnoDB peculiarity<\/h3>\n\n\n\n<p>In MySQL for InnoDB, every table <strong>must have a clustered index<\/strong>. Normally, a clustered index is synonymous with a primary key.<\/p>\n\n\n\n<p>All data in InnoDB is stored in a clustered index.<\/p>\n\n\n\n<ul class=\"wp-block-list\" style=\"margin-top:0;margin-right:0;margin-bottom:0;margin-left:0\">\n<li>MySQL uses PRIMARY KEY for the clustered index. <\/li>\n\n\n\n<li>If it was not created, InnoDB will take the first UNIQUE index if it exists.<\/li>\n\n\n\n<li>If no UNIQUE index was created, then MySQL will create a hidden cluster index named <code>GEN_CLUST_INDEX<\/code>, which will contain the index ID, and when a new string is added, the ID will automatically be increased.<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">MySQL MyISAM peculiarity<\/h3>\n\n\n\n<p>MyISAM, unlike InnoDB, does not support clustered indexes, all indexes there are secondaries.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Secondary index<\/h2>\n\n\n\n<p><strong>Secondary index <\/strong>(<em>non-clustered) <\/em>\u2014 is a type of DBMS index similar to cluster index, which creates additional data structure (B-Tree, Hash, etc.) for a table by column\/multiple columns for faster search. Such index stores data of indexed column as well as <strong>index cluster key<\/strong>.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><a href=\"https:\/\/wp-yoda.com\/wp-content\/uploads\/2023\/06\/image-55.png\"><img decoding=\"async\" src=\"https:\/\/wp-yoda.com\/wp-content\/uploads\/2023\/06\/image-55-1024x975.png\" alt=\"Clustered and Secondary indexes of relation database\" class=\"wp-image-1075\"\/><\/a><figcaption class=\"wp-element-caption\">Fig.2 &#8211; Secondary index<\/figcaption><\/figure>\n\n\n\n<blockquote class=\"wp-block-quote has-highlight-background-color has-background is-layout-flow wp-block-quote-is-layout-flow\">\n<p><\/p>\n<cite>Unlike the cluster index, the <strong>secondary index<\/strong> does not affect the physical order of the records, but is an auxiliary structure to speed up data retrieval.<\/cite><\/blockquote>\n\n\n\n<p><strong>The secondary index<\/strong> stores on the tree structure leaves to the clustered index entry. The index cluster key is needed if you want to get the data of another column that is not in the secondary index.<br>In this case, DB will perform an additional search of the row by the cluster index and retrieve the values of these columns from the <code>Leaf<\/code> cluster index. In this case, we get 2 B-Tree searches: first by secondary index and then by clustered index.<\/p>\n\n\n\n<blockquote class=\"wp-block-quote has-highlight-background-color has-background is-layout-flow wp-block-quote-is-layout-flow\">\n<p>For InnoDB in MySQL<\/p>\n<cite>Keep in mind that the <strong>size of the cluster index affects the size of the secondary index<\/strong>, because the non-cluster index contains the key of the cluster index.<\/cite><\/blockquote>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p><\/p>\n<cite>Tables can have several different secondaries indexes.<\/cite><\/blockquote>\n\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Clustered index\u00a0\u2013 a type of index in DBMS with a tree structure, where index values together with data are stored as an ordered tree, usually as a balanced search tree &#8211; B-Tree (or its B-Tree variations).<br \/>\nIn a clustered index, each level of the tree represents index pages, and the end pages (leaves, Leaf) contain the actual table row data&#8230;<\/p>\n","protected":false},"author":1,"featured_media":200,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[17,16,18],"tags":[20,21,19],"class_list":["post-198","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-databases","category-mysql","category-relational-databases","tag-database-indexes","tag-db","tag-relational-databases"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.9 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Clustered and secondary indexes for relational databases - WP Yoda<\/title>\n<meta name=\"description\" content=\"In this article, we will consider the difference between clustered and secondary indexes. We will get acquainted with the B-Tree data structure in which indexes are stored.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/wp-yoda.com\/en\/mysql\/clustered-and-secondary-indexes\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Clustered and secondary indexes for relational databases - WP Yoda\" \/>\n<meta property=\"og:description\" content=\"In this article, we will consider the difference between clustered and secondary indexes. We will get acquainted with the B-Tree data structure in which indexes are stored.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/wp-yoda.com\/en\/mysql\/clustered-and-secondary-indexes\/\" \/>\n<meta property=\"og:site_name\" content=\"WP Yoda\" \/>\n<meta property=\"article:published_time\" content=\"2023-06-18T19:25:11+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-03-08T22:44:12+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/wp-yoda.com\/wp-content\/uploads\/2023\/06\/0_2.png\" \/>\n\t<meta property=\"og:image:width\" content=\"1536\" \/>\n\t<meta property=\"og:image:height\" content=\"768\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"\u0410\u043d\u0434\u0440\u0435\u0439 \u041f\u0438\u0441\u0430\u0440\u0435\u0432\u0441\u043a\u0438\u0439\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"\u0410\u043d\u0434\u0440\u0435\u0439 \u041f\u0438\u0441\u0430\u0440\u0435\u0432\u0441\u043a\u0438\u0439\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. 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:\\\/\\\/wp-yoda.com\\\/en\\\/mysql\\\/clustered-and-secondary-indexes\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/wp-yoda.com\\\/en\\\/mysql\\\/clustered-and-secondary-indexes\\\/\"},\"author\":{\"name\":\"\u0410\u043d\u0434\u0440\u0435\u0439 \u041f\u0438\u0441\u0430\u0440\u0435\u0432\u0441\u043a\u0438\u0439\",\"@id\":\"https:\\\/\\\/wp-yoda.com\\\/en\\\/#\\\/schema\\\/person\\\/195c797aee113d174e07e4887a9fe464\"},\"headline\":\"Clustered and secondary indexes for relational databases\",\"datePublished\":\"2023-06-18T19:25:11+00:00\",\"dateModified\":\"2024-03-08T22:44:12+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/wp-yoda.com\\\/en\\\/mysql\\\/clustered-and-secondary-indexes\\\/\"},\"wordCount\":870,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\\\/\\\/wp-yoda.com\\\/en\\\/#organization\"},\"image\":{\"@id\":\"https:\\\/\\\/wp-yoda.com\\\/en\\\/mysql\\\/clustered-and-secondary-indexes\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/wp-yoda.com\\\/wp-content\\\/uploads\\\/2023\\\/06\\\/0_2.png\",\"keywords\":[\"Database indexes\",\"DB\",\"Relational databases\"],\"articleSection\":[\"Databases\",\"MySQL\",\"Relational databases\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/wp-yoda.com\\\/en\\\/mysql\\\/clustered-and-secondary-indexes\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/wp-yoda.com\\\/en\\\/mysql\\\/clustered-and-secondary-indexes\\\/\",\"url\":\"https:\\\/\\\/wp-yoda.com\\\/en\\\/mysql\\\/clustered-and-secondary-indexes\\\/\",\"name\":\"Clustered and secondary indexes for relational databases - WP Yoda\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/wp-yoda.com\\\/en\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/wp-yoda.com\\\/en\\\/mysql\\\/clustered-and-secondary-indexes\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/wp-yoda.com\\\/en\\\/mysql\\\/clustered-and-secondary-indexes\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/wp-yoda.com\\\/wp-content\\\/uploads\\\/2023\\\/06\\\/0_2.png\",\"datePublished\":\"2023-06-18T19:25:11+00:00\",\"dateModified\":\"2024-03-08T22:44:12+00:00\",\"description\":\"In this article, we will consider the difference between clustered and secondary indexes. We will get acquainted with the B-Tree data structure in which indexes are stored.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/wp-yoda.com\\\/en\\\/mysql\\\/clustered-and-secondary-indexes\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/wp-yoda.com\\\/en\\\/mysql\\\/clustered-and-secondary-indexes\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/wp-yoda.com\\\/en\\\/mysql\\\/clustered-and-secondary-indexes\\\/#primaryimage\",\"url\":\"https:\\\/\\\/wp-yoda.com\\\/wp-content\\\/uploads\\\/2023\\\/06\\\/0_2.png\",\"contentUrl\":\"https:\\\/\\\/wp-yoda.com\\\/wp-content\\\/uploads\\\/2023\\\/06\\\/0_2.png\",\"width\":1536,\"height\":768,\"caption\":\"Clustered and non-clustered indexes\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/wp-yoda.com\\\/en\\\/mysql\\\/clustered-and-secondary-indexes\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/wp-yoda.com\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Clustered and secondary indexes for relational databases\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/wp-yoda.com\\\/en\\\/#website\",\"url\":\"https:\\\/\\\/wp-yoda.com\\\/en\\\/\",\"name\":\"WP Yoda\",\"description\":\"Notes about PHP, WordPress and IT\",\"publisher\":{\"@id\":\"https:\\\/\\\/wp-yoda.com\\\/en\\\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/wp-yoda.com\\\/en\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\\\/\\\/wp-yoda.com\\\/en\\\/#organization\",\"name\":\"WP Yoda\",\"url\":\"https:\\\/\\\/wp-yoda.com\\\/en\\\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/wp-yoda.com\\\/en\\\/#\\\/schema\\\/logo\\\/image\\\/\",\"url\":\"https:\\\/\\\/wp-yoda.com\\\/en\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2023\\\/05\\\/Andrew_222_Master_Yoda__WordPress_plain_background_763a84a4-5042-4fac-9bdb-4562c05563e7.png\",\"contentUrl\":\"https:\\\/\\\/wp-yoda.com\\\/en\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2023\\\/05\\\/Andrew_222_Master_Yoda__WordPress_plain_background_763a84a4-5042-4fac-9bdb-4562c05563e7.png\",\"width\":1024,\"height\":1024,\"caption\":\"WP Yoda\"},\"image\":{\"@id\":\"https:\\\/\\\/wp-yoda.com\\\/en\\\/#\\\/schema\\\/logo\\\/image\\\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/wp-yoda.com\\\/en\\\/#\\\/schema\\\/person\\\/195c797aee113d174e07e4887a9fe464\",\"name\":\"\u0410\u043d\u0434\u0440\u0435\u0439 \u041f\u0438\u0441\u0430\u0440\u0435\u0432\u0441\u043a\u0438\u0439\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/d3f11053e005f02e0dc3049c010b45034646963c7af8edac8076c3767022d750?s=96&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/d3f11053e005f02e0dc3049c010b45034646963c7af8edac8076c3767022d750?s=96&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/d3f11053e005f02e0dc3049c010b45034646963c7af8edac8076c3767022d750?s=96&r=g\",\"caption\":\"\u0410\u043d\u0434\u0440\u0435\u0439 \u041f\u0438\u0441\u0430\u0440\u0435\u0432\u0441\u043a\u0438\u0439\"},\"sameAs\":[\"https:\\\/\\\/wp-yoda.com\"],\"url\":\"https:\\\/\\\/wp-yoda.com\\\/en\\\/author\\\/admin\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Clustered and secondary indexes for relational databases - WP Yoda","description":"In this article, we will consider the difference between clustered and secondary indexes. We will get acquainted with the B-Tree data structure in which indexes are stored.","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:\/\/wp-yoda.com\/en\/mysql\/clustered-and-secondary-indexes\/","og_locale":"en_US","og_type":"article","og_title":"Clustered and secondary indexes for relational databases - WP Yoda","og_description":"In this article, we will consider the difference between clustered and secondary indexes. We will get acquainted with the B-Tree data structure in which indexes are stored.","og_url":"https:\/\/wp-yoda.com\/en\/mysql\/clustered-and-secondary-indexes\/","og_site_name":"WP Yoda","article_published_time":"2023-06-18T19:25:11+00:00","article_modified_time":"2024-03-08T22:44:12+00:00","og_image":[{"width":1536,"height":768,"url":"https:\/\/wp-yoda.com\/wp-content\/uploads\/2023\/06\/0_2.png","type":"image\/png"}],"author":"\u0410\u043d\u0434\u0440\u0435\u0439 \u041f\u0438\u0441\u0430\u0440\u0435\u0432\u0441\u043a\u0438\u0439","twitter_card":"summary_large_image","twitter_misc":{"Written by":"\u0410\u043d\u0434\u0440\u0435\u0439 \u041f\u0438\u0441\u0430\u0440\u0435\u0432\u0441\u043a\u0438\u0439","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/wp-yoda.com\/en\/mysql\/clustered-and-secondary-indexes\/#article","isPartOf":{"@id":"https:\/\/wp-yoda.com\/en\/mysql\/clustered-and-secondary-indexes\/"},"author":{"name":"\u0410\u043d\u0434\u0440\u0435\u0439 \u041f\u0438\u0441\u0430\u0440\u0435\u0432\u0441\u043a\u0438\u0439","@id":"https:\/\/wp-yoda.com\/en\/#\/schema\/person\/195c797aee113d174e07e4887a9fe464"},"headline":"Clustered and secondary indexes for relational databases","datePublished":"2023-06-18T19:25:11+00:00","dateModified":"2024-03-08T22:44:12+00:00","mainEntityOfPage":{"@id":"https:\/\/wp-yoda.com\/en\/mysql\/clustered-and-secondary-indexes\/"},"wordCount":870,"commentCount":0,"publisher":{"@id":"https:\/\/wp-yoda.com\/en\/#organization"},"image":{"@id":"https:\/\/wp-yoda.com\/en\/mysql\/clustered-and-secondary-indexes\/#primaryimage"},"thumbnailUrl":"https:\/\/wp-yoda.com\/wp-content\/uploads\/2023\/06\/0_2.png","keywords":["Database indexes","DB","Relational databases"],"articleSection":["Databases","MySQL","Relational databases"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/wp-yoda.com\/en\/mysql\/clustered-and-secondary-indexes\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/wp-yoda.com\/en\/mysql\/clustered-and-secondary-indexes\/","url":"https:\/\/wp-yoda.com\/en\/mysql\/clustered-and-secondary-indexes\/","name":"Clustered and secondary indexes for relational databases - WP Yoda","isPartOf":{"@id":"https:\/\/wp-yoda.com\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/wp-yoda.com\/en\/mysql\/clustered-and-secondary-indexes\/#primaryimage"},"image":{"@id":"https:\/\/wp-yoda.com\/en\/mysql\/clustered-and-secondary-indexes\/#primaryimage"},"thumbnailUrl":"https:\/\/wp-yoda.com\/wp-content\/uploads\/2023\/06\/0_2.png","datePublished":"2023-06-18T19:25:11+00:00","dateModified":"2024-03-08T22:44:12+00:00","description":"In this article, we will consider the difference between clustered and secondary indexes. We will get acquainted with the B-Tree data structure in which indexes are stored.","breadcrumb":{"@id":"https:\/\/wp-yoda.com\/en\/mysql\/clustered-and-secondary-indexes\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/wp-yoda.com\/en\/mysql\/clustered-and-secondary-indexes\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/wp-yoda.com\/en\/mysql\/clustered-and-secondary-indexes\/#primaryimage","url":"https:\/\/wp-yoda.com\/wp-content\/uploads\/2023\/06\/0_2.png","contentUrl":"https:\/\/wp-yoda.com\/wp-content\/uploads\/2023\/06\/0_2.png","width":1536,"height":768,"caption":"Clustered and non-clustered indexes"},{"@type":"BreadcrumbList","@id":"https:\/\/wp-yoda.com\/en\/mysql\/clustered-and-secondary-indexes\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/wp-yoda.com\/en\/"},{"@type":"ListItem","position":2,"name":"Clustered and secondary indexes for relational databases"}]},{"@type":"WebSite","@id":"https:\/\/wp-yoda.com\/en\/#website","url":"https:\/\/wp-yoda.com\/en\/","name":"WP Yoda","description":"Notes about PHP, WordPress and IT","publisher":{"@id":"https:\/\/wp-yoda.com\/en\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/wp-yoda.com\/en\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/wp-yoda.com\/en\/#organization","name":"WP Yoda","url":"https:\/\/wp-yoda.com\/en\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/wp-yoda.com\/en\/#\/schema\/logo\/image\/","url":"https:\/\/wp-yoda.com\/en\/wp-content\/uploads\/sites\/2\/2023\/05\/Andrew_222_Master_Yoda__WordPress_plain_background_763a84a4-5042-4fac-9bdb-4562c05563e7.png","contentUrl":"https:\/\/wp-yoda.com\/en\/wp-content\/uploads\/sites\/2\/2023\/05\/Andrew_222_Master_Yoda__WordPress_plain_background_763a84a4-5042-4fac-9bdb-4562c05563e7.png","width":1024,"height":1024,"caption":"WP Yoda"},"image":{"@id":"https:\/\/wp-yoda.com\/en\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/wp-yoda.com\/en\/#\/schema\/person\/195c797aee113d174e07e4887a9fe464","name":"\u0410\u043d\u0434\u0440\u0435\u0439 \u041f\u0438\u0441\u0430\u0440\u0435\u0432\u0441\u043a\u0438\u0439","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/d3f11053e005f02e0dc3049c010b45034646963c7af8edac8076c3767022d750?s=96&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/d3f11053e005f02e0dc3049c010b45034646963c7af8edac8076c3767022d750?s=96&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/d3f11053e005f02e0dc3049c010b45034646963c7af8edac8076c3767022d750?s=96&r=g","caption":"\u0410\u043d\u0434\u0440\u0435\u0439 \u041f\u0438\u0441\u0430\u0440\u0435\u0432\u0441\u043a\u0438\u0439"},"sameAs":["https:\/\/wp-yoda.com"],"url":"https:\/\/wp-yoda.com\/en\/author\/admin\/"}]}},"_links":{"self":[{"href":"https:\/\/wp-yoda.com\/en\/wp-json\/wp\/v2\/posts\/198","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/wp-yoda.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/wp-yoda.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/wp-yoda.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/wp-yoda.com\/en\/wp-json\/wp\/v2\/comments?post=198"}],"version-history":[{"count":0,"href":"https:\/\/wp-yoda.com\/en\/wp-json\/wp\/v2\/posts\/198\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/wp-yoda.com\/en\/wp-json\/wp\/v2\/media\/200"}],"wp:attachment":[{"href":"https:\/\/wp-yoda.com\/en\/wp-json\/wp\/v2\/media?parent=198"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/wp-yoda.com\/en\/wp-json\/wp\/v2\/categories?post=198"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/wp-yoda.com\/en\/wp-json\/wp\/v2\/tags?post=198"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}