{"id":297,"date":"2016-08-07T03:42:47","date_gmt":"2016-08-07T00:42:47","guid":{"rendered":"https:\/\/community.virtono.com\/?p=297"},"modified":"2023-06-21T10:18:37","modified_gmt":"2023-06-21T07:18:37","slug":"mysql-indexing","status":"publish","type":"post","link":"https:\/\/www.virtono.com\/community\/knowledgebase\/mysql-indexing\/","title":{"rendered":"MySQL Indexing"},"content":{"rendered":"<p style=\"text-align: center;\">This article is a fraction of a Number of Articles on MySQL, to access them click <a href=\"https:\/\/virtono.com\/community\/knowledgebase\/articles-on-mysql\/\" target=\"_blank\" rel=\"noopener\">here<\/a>.<\/p>\n<p><strong>MySQL Indexing<\/strong><\/p>\n<p><strong>\u00a0<\/strong><\/p>\n<ul>\n<li><strong>MySQL documentation says:<\/strong><\/li>\n<\/ul>\n<p><strong><em>The best way to improve the performance of <\/em><\/strong><strong><em>SELECT <\/em><\/strong><strong><em>operations is<\/em><\/strong><\/p>\n<p><strong><em>to create indexes on one or more of the columns that are tested in<\/em><\/strong><\/p>\n<p><strong><em>the query.<\/em><\/strong><\/p>\n<ul>\n<li><strong>Great! But:<\/strong><\/li>\n<\/ul>\n<p><strong><em>Unnecessary indexes waste space and waste time to determine<\/em><\/strong><\/p>\n<p><strong><em>which indexes to use. You must find the right balance to achieve<\/em><\/strong><\/p>\n<p><strong><em>fast queries using the optimal set of indexes.<\/em><\/strong><\/p>\n<p><strong>\u00a0<img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" data-attachment-id=\"300\" data-permalink=\"https:\/\/www.virtono.com\/community\/knowledgebase\/mysql-indexing\/attachment\/mysql-indexes\/\" data-orig-file=\"https:\/\/i0.wp.com\/www.virtono.com\/community\/wp-content\/uploads\/2016\/08\/mysql-indexes.png?fit=400%2C400&amp;ssl=1\" data-orig-size=\"400,400\" data-comments-opened=\"1\" data-image-meta=\"{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}\" data-image-title=\"mysql-indexes\" data-image-description=\"\" data-image-caption=\"\" data-large-file=\"https:\/\/i0.wp.com\/www.virtono.com\/community\/wp-content\/uploads\/2016\/08\/mysql-indexes.png?fit=400%2C400&amp;ssl=1\" class=\"size-medium wp-image-300 aligncenter\" src=\"https:\/\/i0.wp.com\/community.virtono.com\/wp-content\/uploads\/2016\/08\/mysql-indexes-300x300.png?resize=300%2C300&#038;ssl=1\" alt=\"mysql-indexes\" width=\"300\" height=\"300\" srcset=\"https:\/\/i0.wp.com\/www.virtono.com\/community\/wp-content\/uploads\/2016\/08\/mysql-indexes.png?resize=300%2C300&amp;ssl=1 300w, https:\/\/i0.wp.com\/www.virtono.com\/community\/wp-content\/uploads\/2016\/08\/mysql-indexes.png?resize=150%2C150&amp;ssl=1 150w, https:\/\/i0.wp.com\/www.virtono.com\/community\/wp-content\/uploads\/2016\/08\/mysql-indexes.png?resize=160%2C160&amp;ssl=1 160w, https:\/\/i0.wp.com\/www.virtono.com\/community\/wp-content\/uploads\/2016\/08\/mysql-indexes.png?resize=320%2C320&amp;ssl=1 320w, https:\/\/i0.wp.com\/www.virtono.com\/community\/wp-content\/uploads\/2016\/08\/mysql-indexes.png?w=400&amp;ssl=1 400w\" sizes=\"auto, (max-width: 300px) 100vw, 300px\" \/><\/strong><\/p>\n<p><strong>\u00a0<\/strong><\/p>\n<p><strong>\u00a0<\/strong><\/p>\n<p><strong>What is an Index?<\/strong><\/p>\n<p>Indexes help the system to access data fast and provide an ordering on the rows of a table as well as help enforce uniqueness of the values in a table. A lot of performance problems occur in Cloud Sites for database queries when customers have not created the necessary indexes on their tables. To look into how to create an index in MySQL, please refer to<\/p>\n<p><strong>\u00a0<\/strong><\/p>\n<p><strong>\u00a0<\/strong><\/p>\n<p><strong>MySQL uses indexes:<\/strong><\/p>\n<ul>\n<li><strong> To enforce uniqueness (PRIMARY KEY, UNIQUE<\/strong><\/li>\n<\/ul>\n<p><strong>KEY)<\/strong><\/p>\n<ul>\n<li><strong> To fast access and filter rows (WHERE)<\/strong><\/li>\n<li><strong> To perform joins fast (JOIN)<\/strong><\/li>\n<li><strong> To find MIN() and MAX() values<\/strong><\/li>\n<li><strong> For sorting and grouping (ORDER BY, GROUP BY)<\/strong><\/li>\n<li><strong> To avoid joins by using covering indexes<\/strong><\/li>\n<li><strong> To enforce FOREIGN KEY Constraints (FOREIGN<\/strong><\/li>\n<\/ul>\n<p><strong>KEY)<\/strong><\/p>\n<p><strong>\u00a0<\/strong><\/p>\n<p><strong>The Explain Statement<\/strong><\/p>\n<p>The best way to analyze your query and to see if indexes are being used is by running an explain plan on it. This will show you the path chosen by the optimizer in executing the query and help give you an idea as to whether or not you will benefit from creating an index. More on the explain plan here:<\/p>\n<p><strong>Prefixing index lengths with an example<\/strong><\/p>\n<p>Say you run this SQL frequently:<\/p>\n<p>SELECT user_id, user_id AS ID, user_login, display_name, user_email, meta_value FROM insider_users,<\/p>\n<p>usermeta WHERE insider_users.ID = usermeta.user_id AND meta_key = &#8216;S&#8217; ORDER BY usermeta.user_id;<\/p>\n<p>Say you have this table:<\/p>\n<p>CREATE TABLE `usermeta` ( `umeta_id` bigint(20) unsigned NOT NULL auto_increment, `user_id` bigint<\/p>\n<p>(20) unsigned NOT NULL default &#8216;0&#8217;, `meta_key` varchar(255) default NULL, `meta_value` longtext, PRIMARY KEY<\/p>\n<p>(`umeta_id`), KEY `user_id` (`user_id`), KEY `meta_key` (`meta_key`) ENGINE=InnoDB AUTO_INCREMENT=25199<\/p>\n<p>DEFAULT CHARSET=utf8<\/p>\n<p>For table usermeta table above, the original meta_key index that is varchar(255).<\/p>\n<p><strong>Note:<\/strong>\u00a0When you index a full and large column like the meta_key index, then you will hurt your performance.**<\/p>\n<p>What would benefit this query is prefixing the length &#8211; dropping the meta_key index and then re-creating an index but at prefix length of 20. Doing this helps gain performance and saves space. This also reduces disk IO which buys your faster performance also.<\/p>\n<p><strong>Covering Index<\/strong><\/p>\n<p><strong>The following is an example of a slow MySQL query which also does not have an Index(s) in place:<\/strong><\/p>\n<p># Query_time: 82.420792\u00a0 Lock_time: 0.015179\u00a0 Rows_sent: 1\u00a0 Rows_examined: 15828351\u00a0 Rows_affected: 0\u00a0 Rows_read: 15828351<\/p>\n<p>use 393870_p2LIVE; SELECT `Impression`.`id`, `Impression`.`account_id`, `Impression`.`content_id`, `Impression`.`networks_id`,<\/p>\n<p>`Impression`.`ip`, `Impression`.`recorded` FROM `impressions` AS `Impression`\u00a0\u00a0 WHERE `account_id` = 222 AND `content_id` = 19 AND `networks_id` = 8<\/p>\n","protected":false},"excerpt":{"rendered":"<p>This article is a fraction of a Number of Articles on MySQL, to access them click here. MySQL Indexing \u00a0 MySQL documentation says: The best way to improve the performance of SELECT operations is to create indexes on one or more of the columns that are tested in the query.<\/p>\n","protected":false},"author":3,"featured_media":299,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_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":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[5],"tags":[],"class_list":["post-297","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-knowledgebase"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/www.virtono.com\/community\/wp-content\/uploads\/2016\/08\/MySQL_Replication1-3.png?fit=715%2C262&ssl=1","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p7ISfL-4N","jetpack_likes_enabled":true,"jetpack-related-posts":[{"id":3496,"url":"https:\/\/www.virtono.com\/community\/tutorial-how-to\/how-to-tune-your-mysql-server-for-optimal-performance\/","url_meta":{"origin":297,"position":0},"title":"How to Tune Your MySQL Server for Optimal Performance","author":"George B.","date":"June 12, 2023","format":false,"excerpt":"In this step-by-step guide, we will walk you through the process of tuning your MySQL server, helping you enhance its efficiency and responsiveness. MySQL is one of the most popular open-source relational database management systems, widely used for various applications and websites. However, as your database grows in size and\u2026","rel":"","context":"In &quot;Tutorials&quot;","block_context":{"text":"Tutorials","link":"https:\/\/www.virtono.com\/community\/category\/tutorial-how-to\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/www.virtono.com\/community\/wp-content\/uploads\/2023\/06\/1.png?fit=600%2C330&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/www.virtono.com\/community\/wp-content\/uploads\/2023\/06\/1.png?fit=600%2C330&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/www.virtono.com\/community\/wp-content\/uploads\/2023\/06\/1.png?fit=600%2C330&ssl=1&resize=525%2C300 1.5x"},"classes":[]},{"id":294,"url":"https:\/\/www.virtono.com\/community\/knowledgebase\/mysql-performance-tuning-the-stages-of-tuning\/","url_meta":{"origin":297,"position":1},"title":"MySQL Performance Tuning: The stages of Tuning","author":"Daniel Draga","date":"August 7, 2016","format":false,"excerpt":"This article is a fraction of a Number of Articles on MySQL, to access them click here. Why Tune a Database? Cost-effectiveness A system that is tuned can minimize the need to buy additional hardware and other resources to meet the needs of the end users. Tuning may demonstrate that\u2026","rel":"","context":"In &quot;Knowledgebase&quot;","block_context":{"text":"Knowledgebase","link":"https:\/\/www.virtono.com\/community\/category\/knowledgebase\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":302,"url":"https:\/\/www.virtono.com\/community\/tutorial-how-to\/how-to-create-an-index\/","url_meta":{"origin":297,"position":2},"title":"How to create an Index","author":"Daniel Draga","date":"August 7, 2016","format":false,"excerpt":"This article is a fraction of a Number of Articles on MySQL, to access them click here. SQL CREATE INDEX Syntax Creates an index on a table. Duplicate values are allowed: CREATE INDEX index_name ON table_name (column_name) SQL CREATE UNIQUE INDEX Syntax Creates a unique index on a table. Duplicate\u2026","rel":"","context":"In &quot;Tutorials&quot;","block_context":{"text":"Tutorials","link":"https:\/\/www.virtono.com\/community\/category\/tutorial-how-to\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/www.virtono.com\/community\/wp-content\/uploads\/2016\/08\/MySQL_Replication1-4.png?fit=731%2C266&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/www.virtono.com\/community\/wp-content\/uploads\/2016\/08\/MySQL_Replication1-4.png?fit=731%2C266&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/www.virtono.com\/community\/wp-content\/uploads\/2016\/08\/MySQL_Replication1-4.png?fit=731%2C266&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/www.virtono.com\/community\/wp-content\/uploads\/2016\/08\/MySQL_Replication1-4.png?fit=731%2C266&ssl=1&resize=700%2C400 2x"},"classes":[]},{"id":281,"url":"https:\/\/www.virtono.com\/community\/tutorial-how-to\/mysql-replication-and-how-to-achieve-it\/","url_meta":{"origin":297,"position":3},"title":"MySQL Replication and How to Achieve It","author":"Daniel Draga","date":"August 7, 2016","format":false,"excerpt":"This article is a fraction of a Number of Articles on MySQL, to access them click here. Replication Replication is the ability to duplicate data changes that occur on one server to another. Basic components Servers Master, Slave, Relay Slave Configuration files Threads Binlog dump thread, I\/O Thread, SQL Thread\u2026","rel":"","context":"In &quot;Tutorials&quot;","block_context":{"text":"Tutorials","link":"https:\/\/www.virtono.com\/community\/category\/tutorial-how-to\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/www.virtono.com\/community\/wp-content\/uploads\/2016\/08\/MySQL_Replication1.png?fit=750%2C255&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/www.virtono.com\/community\/wp-content\/uploads\/2016\/08\/MySQL_Replication1.png?fit=750%2C255&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/www.virtono.com\/community\/wp-content\/uploads\/2016\/08\/MySQL_Replication1.png?fit=750%2C255&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/www.virtono.com\/community\/wp-content\/uploads\/2016\/08\/MySQL_Replication1.png?fit=750%2C255&ssl=1&resize=700%2C400 2x"},"classes":[]},{"id":338,"url":"https:\/\/www.virtono.com\/community\/knowledgebase\/articles-on-mysql\/","url_meta":{"origin":297,"position":4},"title":"Articles on MySQL","author":"Daniel Draga","date":"August 7, 2016","format":false,"excerpt":"MySQL:\u00a0 Introduction and How it Works MySQL Installation MySQL replication The Top MySQL 10 Design Tips MySQL Performance Tuning: The stages of Tuning MySQL Indexing How to create an index How MySQL and PHP Work Together How to handle MySQL errors Constraints in SQL and How to Specify Them","rel":"","context":"In &quot;Knowledgebase&quot;","block_context":{"text":"Knowledgebase","link":"https:\/\/www.virtono.com\/community\/category\/knowledgebase\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/www.virtono.com\/community\/wp-content\/uploads\/2016\/08\/MySQL-Foreign-Key-EXAMPLE-2-1.png?fit=1200%2C675&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/www.virtono.com\/community\/wp-content\/uploads\/2016\/08\/MySQL-Foreign-Key-EXAMPLE-2-1.png?fit=1200%2C675&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/www.virtono.com\/community\/wp-content\/uploads\/2016\/08\/MySQL-Foreign-Key-EXAMPLE-2-1.png?fit=1200%2C675&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/www.virtono.com\/community\/wp-content\/uploads\/2016\/08\/MySQL-Foreign-Key-EXAMPLE-2-1.png?fit=1200%2C675&ssl=1&resize=700%2C400 2x, https:\/\/i0.wp.com\/www.virtono.com\/community\/wp-content\/uploads\/2016\/08\/MySQL-Foreign-Key-EXAMPLE-2-1.png?fit=1200%2C675&ssl=1&resize=1050%2C600 3x"},"classes":[]},{"id":2961,"url":"https:\/\/www.virtono.com\/community\/tutorial-how-to\/how-to-install-wordpress-on-centos-8\/","url_meta":{"origin":297,"position":5},"title":"How to Install WordPress on CentOS 8","author":"George B.","date":"August 4, 2022","format":false,"excerpt":"What is WordPress? WordPress is a free and open-source platform for building websites. On a more technical level, WordPress is a PHP-based content management system (CMS) that uses a MySQL database. WordPress is the most user-friendly and powerful blogging and website builder available today. Important note: According to WPVALID the\u2026","rel":"","context":"In &quot;Tutorials&quot;","block_context":{"text":"Tutorials","link":"https:\/\/www.virtono.com\/community\/category\/tutorial-how-to\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/www.virtono.com\/community\/wp-content\/uploads\/2022\/08\/Looking-for-a-WordPress-Developer-1042x665-1.jpeg?fit=1042%2C665&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/www.virtono.com\/community\/wp-content\/uploads\/2022\/08\/Looking-for-a-WordPress-Developer-1042x665-1.jpeg?fit=1042%2C665&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/www.virtono.com\/community\/wp-content\/uploads\/2022\/08\/Looking-for-a-WordPress-Developer-1042x665-1.jpeg?fit=1042%2C665&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/www.virtono.com\/community\/wp-content\/uploads\/2022\/08\/Looking-for-a-WordPress-Developer-1042x665-1.jpeg?fit=1042%2C665&ssl=1&resize=700%2C400 2x"},"classes":[]}],"_links":{"self":[{"href":"https:\/\/www.virtono.com\/community\/wp-json\/wp\/v2\/posts\/297","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.virtono.com\/community\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.virtono.com\/community\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.virtono.com\/community\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/www.virtono.com\/community\/wp-json\/wp\/v2\/comments?post=297"}],"version-history":[{"count":3,"href":"https:\/\/www.virtono.com\/community\/wp-json\/wp\/v2\/posts\/297\/revisions"}],"predecessor-version":[{"id":3567,"href":"https:\/\/www.virtono.com\/community\/wp-json\/wp\/v2\/posts\/297\/revisions\/3567"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.virtono.com\/community\/wp-json\/wp\/v2\/media\/299"}],"wp:attachment":[{"href":"https:\/\/www.virtono.com\/community\/wp-json\/wp\/v2\/media?parent=297"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.virtono.com\/community\/wp-json\/wp\/v2\/categories?post=297"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.virtono.com\/community\/wp-json\/wp\/v2\/tags?post=297"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}