{"id":7410,"date":"2020-04-15T12:56:16","date_gmt":"2020-04-15T12:56:16","guid":{"rendered":"https:\/\/www.milesweb.co.uk\/hosting-faqs\/?p=7410"},"modified":"2020-04-15T12:56:16","modified_gmt":"2020-04-15T12:56:16","slug":"optimize-mysql-using-indexes","status":"publish","type":"post","link":"https:\/\/www.milesweb.co.uk\/hosting-faqs\/optimize-mysql-using-indexes\/","title":{"rendered":"How to Optimize MySQL using Indexes?"},"content":{"rendered":"<p>A data structure that enhances the speed of operations in a table is called as a database index.<\/p>\n<p>Each time a web application runs a database query wherein the database checks all the rows in your table for finding those that match with the request. When your database table grows, it is required to inspect increasing number of rows each time and this can decrease the performance of the database as well as your application.<\/p>\n<p>This problem is resolved by MySQL indexes by taking data from a column of your table and storing it in a separate location alphabetical wise and this is called an index.<\/p>\n<p>You can easily create indexes via phpMyAdmin in cPanel. Below is an example of such index:<\/p>\n<p>Here\u2019s an example table called <strong>&#8220;sample&#8221;<\/strong> with only two rows &#8211; <strong>&#8220;number&#8221;<\/strong> and <strong>&#8220;employee&#8221;<\/strong>. If a simple SQL query is run such as:<\/p>\n<pre class=\"lang:default decode:true \">SELECT * FROM sample WHERE number = 4;<\/pre>\n<p>MySQL will go through all records and will return only the one with the number value set to 4.<\/p>\n<p>Suppose there are thousands of entries for instance, it will run the query slowly. Here a unique field \u201cnumber\u201d is present. So, an index can be created for it. An internal register will be created by indexing and is saved in by the MySQL service. This can be done with the below query:<\/p>\n<pre class=\"lang:default decode:true \">ALTER TABLE sample ADD INDEX (number);<\/pre>\n<p>After you set the index, next time when you want to find the information for employee number 4, the service will go to it directly with the help of index and will return the information in a faster way.<\/p>\n<p>It is a very basic example. In terms of bigger databases, the difference in the loading time can be high. When your database is indexed, it can drastically decrease the loading time of your web applications.<\/p>\n<p>You can use another query to increase the loading time of your database as below:<\/p>\n<pre class=\"lang:default decode:true \">OPTIMIZE TABLE sample;<\/pre>\n<p>That\u2019s it! You have now learned to optimize MySQL using indexes.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>A data structure that enhances the speed of operations in a table is called as a database index. Each time a web application runs a database query wherein the database checks all the rows in your table for finding those that match with the request. When your database table grows, it is required to inspect [&hellip;]<\/p>\n","protected":false},"author":16,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[947],"tags":[205,1389,1388],"class_list":["post-7410","post","type-post","status-publish","format-standard","placeholder-for-hentry","category-mysql","tag-mysql","tag-mysql-optimization","tag-optimize-mysql-using-indexes"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v25.2 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>How to Optimize MySQL using Indexes?<\/title>\n<meta name=\"description\" content=\"The article describes the steps to optimize MySQL using indexes.\" \/>\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.milesweb.co.uk\/hosting-faqs\/optimize-mysql-using-indexes\/\" \/>\n<meta property=\"og:locale\" content=\"en_GB\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How to Optimize MySQL using Indexes?\" \/>\n<meta property=\"og:description\" content=\"The article describes the steps to optimize MySQL using indexes.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.milesweb.co.uk\/hosting-faqs\/optimize-mysql-using-indexes\/\" \/>\n<meta property=\"og:site_name\" content=\"Web Hosting FAQs by MilesWeb\" \/>\n<meta property=\"article:published_time\" content=\"2020-04-15T12:56:16+00:00\" \/>\n<meta name=\"author\" content=\"Pallavi Godse\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Pallavi Godse\" \/>\n\t<meta name=\"twitter:label2\" content=\"Estimated reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.milesweb.co.uk\/hosting-faqs\/optimize-mysql-using-indexes\/\",\"url\":\"https:\/\/www.milesweb.co.uk\/hosting-faqs\/optimize-mysql-using-indexes\/\",\"name\":\"How to Optimize MySQL using Indexes?\",\"isPartOf\":{\"@id\":\"https:\/\/www.milesweb.co.uk\/hosting-faqs\/#website\"},\"datePublished\":\"2020-04-15T12:56:16+00:00\",\"author\":{\"@id\":\"https:\/\/www.milesweb.co.uk\/hosting-faqs\/#\/schema\/person\/7e3952607fa9eb4e82fea9f7cad9c945\"},\"description\":\"The article describes the steps to optimize MySQL using indexes.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.milesweb.co.uk\/hosting-faqs\/optimize-mysql-using-indexes\/#breadcrumb\"},\"inLanguage\":\"en-GB\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.milesweb.co.uk\/hosting-faqs\/optimize-mysql-using-indexes\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.milesweb.co.uk\/hosting-faqs\/optimize-mysql-using-indexes\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.milesweb.co.uk\/hosting-faqs\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to Optimize MySQL using Indexes?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.milesweb.co.uk\/hosting-faqs\/#website\",\"url\":\"https:\/\/www.milesweb.co.uk\/hosting-faqs\/\",\"name\":\"Web Hosting FAQs by MilesWeb\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.milesweb.co.uk\/hosting-faqs\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-GB\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.milesweb.co.uk\/hosting-faqs\/#\/schema\/person\/7e3952607fa9eb4e82fea9f7cad9c945\",\"name\":\"Pallavi Godse\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-GB\",\"@id\":\"https:\/\/www.milesweb.co.uk\/hosting-faqs\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/eefc9695ea2b2c6e143c9c9919701aaa?s=96&d=blank&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/eefc9695ea2b2c6e143c9c9919701aaa?s=96&d=blank&r=g\",\"caption\":\"Pallavi Godse\"},\"description\":\"Pallavi is a Digital Marketing Executive at MilesWeb and has an experience of over 4 years in content development. She is interested in writing engaging content on business, technology, web hosting and other topics related to information technology.\",\"url\":\"https:\/\/www.milesweb.co.uk\/hosting-faqs\/author\/pallavi\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How to Optimize MySQL using Indexes?","description":"The article describes the steps to optimize MySQL using indexes.","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.milesweb.co.uk\/hosting-faqs\/optimize-mysql-using-indexes\/","og_locale":"en_GB","og_type":"article","og_title":"How to Optimize MySQL using Indexes?","og_description":"The article describes the steps to optimize MySQL using indexes.","og_url":"https:\/\/www.milesweb.co.uk\/hosting-faqs\/optimize-mysql-using-indexes\/","og_site_name":"Web Hosting FAQs by MilesWeb","article_published_time":"2020-04-15T12:56:16+00:00","author":"Pallavi Godse","twitter_misc":{"Written by":"Pallavi Godse","Estimated reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.milesweb.co.uk\/hosting-faqs\/optimize-mysql-using-indexes\/","url":"https:\/\/www.milesweb.co.uk\/hosting-faqs\/optimize-mysql-using-indexes\/","name":"How to Optimize MySQL using Indexes?","isPartOf":{"@id":"https:\/\/www.milesweb.co.uk\/hosting-faqs\/#website"},"datePublished":"2020-04-15T12:56:16+00:00","author":{"@id":"https:\/\/www.milesweb.co.uk\/hosting-faqs\/#\/schema\/person\/7e3952607fa9eb4e82fea9f7cad9c945"},"description":"The article describes the steps to optimize MySQL using indexes.","breadcrumb":{"@id":"https:\/\/www.milesweb.co.uk\/hosting-faqs\/optimize-mysql-using-indexes\/#breadcrumb"},"inLanguage":"en-GB","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.milesweb.co.uk\/hosting-faqs\/optimize-mysql-using-indexes\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.milesweb.co.uk\/hosting-faqs\/optimize-mysql-using-indexes\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.milesweb.co.uk\/hosting-faqs\/"},{"@type":"ListItem","position":2,"name":"How to Optimize MySQL using Indexes?"}]},{"@type":"WebSite","@id":"https:\/\/www.milesweb.co.uk\/hosting-faqs\/#website","url":"https:\/\/www.milesweb.co.uk\/hosting-faqs\/","name":"Web Hosting FAQs by MilesWeb","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.milesweb.co.uk\/hosting-faqs\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-GB"},{"@type":"Person","@id":"https:\/\/www.milesweb.co.uk\/hosting-faqs\/#\/schema\/person\/7e3952607fa9eb4e82fea9f7cad9c945","name":"Pallavi Godse","image":{"@type":"ImageObject","inLanguage":"en-GB","@id":"https:\/\/www.milesweb.co.uk\/hosting-faqs\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/eefc9695ea2b2c6e143c9c9919701aaa?s=96&d=blank&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/eefc9695ea2b2c6e143c9c9919701aaa?s=96&d=blank&r=g","caption":"Pallavi Godse"},"description":"Pallavi is a Digital Marketing Executive at MilesWeb and has an experience of over 4 years in content development. She is interested in writing engaging content on business, technology, web hosting and other topics related to information technology.","url":"https:\/\/www.milesweb.co.uk\/hosting-faqs\/author\/pallavi\/"}]}},"views":501,"_links":{"self":[{"href":"https:\/\/www.milesweb.co.uk\/hosting-faqs\/wp-json\/wp\/v2\/posts\/7410","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.milesweb.co.uk\/hosting-faqs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.milesweb.co.uk\/hosting-faqs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.milesweb.co.uk\/hosting-faqs\/wp-json\/wp\/v2\/users\/16"}],"replies":[{"embeddable":true,"href":"https:\/\/www.milesweb.co.uk\/hosting-faqs\/wp-json\/wp\/v2\/comments?post=7410"}],"version-history":[{"count":1,"href":"https:\/\/www.milesweb.co.uk\/hosting-faqs\/wp-json\/wp\/v2\/posts\/7410\/revisions"}],"predecessor-version":[{"id":7411,"href":"https:\/\/www.milesweb.co.uk\/hosting-faqs\/wp-json\/wp\/v2\/posts\/7410\/revisions\/7411"}],"wp:attachment":[{"href":"https:\/\/www.milesweb.co.uk\/hosting-faqs\/wp-json\/wp\/v2\/media?parent=7410"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.milesweb.co.uk\/hosting-faqs\/wp-json\/wp\/v2\/categories?post=7410"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.milesweb.co.uk\/hosting-faqs\/wp-json\/wp\/v2\/tags?post=7410"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}