{"id":12526,"date":"2025-10-29T20:29:09","date_gmt":"2025-10-29T14:59:09","guid":{"rendered":"https:\/\/selfimagination.in\/tips\/?p=12526"},"modified":"2025-10-29T20:29:13","modified_gmt":"2025-10-29T14:59:13","slug":"mysql-performance-optimization-tips","status":"publish","type":"post","link":"https:\/\/selfimagination.in\/tips\/mysql-performance-optimization-tips\/","title":{"rendered":"MySQL Performance Optimization Tips"},"content":{"rendered":"\n<p>MySQL performance, query Optimization, SQL performance, Indexing<\/p>\n\n\n\n<p><\/p>\n\n\n\n<h1 class=\"wp-block-heading\" id=\"table-design-to-query-execution\">SQL Performance Tips<\/h1>\n\n\n\n<p>\u0907\u0938 \u092c\u094d\u0932\u0949\u0917 \u092e\u0947 \u0939\u092e Table Creation \u0915\u0930\u0928\u0947 \u0938\u0947 \u0932\u0947\u0915\u0930 \u0909\u0938 \u092a\u0930 Join \u0932\u0917\u093e\u0928\u093e \u0914\u0930 Filter \u0915\u0930\u0928\u0947 \u0938\u0947 Related \u091c\u094b \u092d\u0940 Changes \u0915\u0930\u0928\u093e \u0939\u094b\u0924\u0947 \u0939\u0948\u0902 \u092f\u093e \u0927\u094d\u092f\u093e\u0928 \u0930\u0916\u0928\u093e \u0939\u094b\u0924\u093e \u0939\u0948\u0902 \u0935\u0939 \u0938\u092d\u0940 \u0939\u092e \u0907\u0938 Blog \u092e\u0947 \u0938\u092e\u091d\u0947\u0902\u0917\u0947 \u0964 <\/p>\n\n\n\n<p>\ud83d\udca1 \u0921\u0947\u091f\u093e\u092c\u0947\u0938 \u0915\u0940 \u0938\u094d\u092a\u0940\u0921 \u0914\u0930 \u092a\u0930\u092b\u0949\u0930\u094d\u092e\u0947\u0902\u0938 \u0915\u093f\u0938\u0940 \u092d\u0940 \u0935\u0947\u092c \u0910\u092a \u092f\u093e \u0938\u0949\u092b\u094d\u091f\u0935\u0947\u092f\u0930 \u0915\u0940 \u091c\u093e\u0928 \u0939\u094b\u0924\u0940 \u0939\u0948\u0964<br>\u0905\u0917\u0930 \u0906\u092a\u0915\u093e MySQL \u0921\u0947\u091f\u093e\u092c\u0947\u0938 \u0924\u0947\u091c\u093c \u091a\u0932\u0947, \u0924\u094b \u0928 \u0915\u0947\u0935\u0932 \u092f\u0942\u091c\u0930 \u090f\u0915\u094d\u0938\u092a\u0940\u0930\u093f\u092f\u0902\u0938 \u092c\u0947\u0939\u0924\u0930 \u0939\u094b\u0924\u093e \u0939\u0948 \u092c\u0932\u094d\u0915\u093f \u0938\u0930\u094d\u0935\u0930 \u0915\u0940 \u0932\u094b\u0921 \u092d\u0940 \u0915\u092e \u0939\u094b\u0924\u0940 \u0939\u0948\u0964<br>\u0907\u0938 \u092c\u094d\u0932\u0949\u0917 \u092e\u0947\u0902 \u0939\u092e \u0938\u094d\u091f\u0947\u092a-\u092c\u093e\u092f-\u0938\u094d\u091f\u0947\u092a \u0926\u0947\u0916\u0947\u0902\u0917\u0947 \u0915\u093f \u2014<br>\ud83d\udc49 \u091f\u0947\u092c\u0932 \u092c\u0928\u093e\u0924\u0947 \u0938\u092e\u092f,<br>\ud83d\udc49 \u0915\u0949\u0932\u092e \u091a\u0941\u0928\u0924\u0947 \u0938\u092e\u092f,<br>\ud83d\udc49 \u0915\u094d\u0935\u0947\u0930\u0940 (WHERE, JOIN, SUM \u0906\u0926\u093f) \u0932\u093f\u0916\u0924\u0947 \u0938\u092e\u092f<br>\u0915\u094d\u092f\u093e-\u0915\u094d\u092f\u093e \u0927\u094d\u092f\u093e\u0928 \u0930\u0916\u0928\u093e \u091a\u093e\u0939\u093f\u090f \u0924\u093e\u0915\u093f \u092a\u0930\u092b\u0949\u0930\u094d\u092e\u0947\u0902\u0938 \u092c\u0947\u0939\u0924\u0930 \u092c\u0928\u0940 \u0930\u0939\u0947\u0964<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<div class=\"wp-block-rank-math-toc-block\" id=\"rank-math-toc\"><h2>Table of Contents<\/h2><nav><ul><li><a href=\"#table-design-to-query-execution\">SQL Performance Tips<\/a><ul><li><a href=\"#\ud83e\uddf1-1-table-design-\u0915\u0930\u0924\u0947-\u0938\u092e\u092f-\u0915\u094d\u092f\u093e-\u0927\u094d\u092f\u093e\u0928-\u0930\u0916\u0947\u0902\">\ud83e\uddf1 1. Table Design \u0915\u0930\u0924\u0947 \u0938\u092e\u092f \u0915\u094d\u092f\u093e \u0927\u094d\u092f\u093e\u0928 \u0930\u0916\u0947\u0902<\/a><\/li><li><a href=\"#\ud83e\uddee-2-column-\u092c\u0928\u093e\u0924\u0947-\u0938\u092e\u092f-\u0938\u0939\u0940-data-type-\u091a\u0941\u0928\u0947\u0902\">\ud83e\uddee 2. Column \u092c\u0928\u093e\u0924\u0947 \u0938\u092e\u092f \u0938\u0939\u0940 Data Type \u091a\u0941\u0928\u0947\u0902<\/a><\/li><li><a href=\"#\u2699\ufe0f-3-indexing-performance-\u0915\u093e-\u0905\u0938\u0932\u0940-\u0939\u0925\u093f\u092f\u093e\u0930\">\u2699\ufe0f 3. Indexing \u2013 SQL Performance \u0915\u093e \u0905\u0938\u0932\u0940 \u0939\u0925\u093f\u092f\u093e\u0930<\/a><ul><li><a href=\"#\ud83d\udd38-index-\u0915\u094d\u092f\u093e-\u0915\u0930\u0924\u093e-\u0939\u0948\">\ud83d\udd38 Index \u0915\u094d\u092f\u093e \u0915\u0930\u0924\u093e \u0939\u0948?<\/a><\/li><li><a href=\"#\ud83d\udd38-index-\u0915\u092c-\u092c\u0928\u093e\u0928\u093e-\u091a\u093e\u0939\u093f\u090f\">\ud83d\udd38 Index \u0915\u092c \u092c\u0928\u093e\u0928\u093e \u091a\u093e\u0939\u093f\u090f?<\/a><\/li><li><a href=\"#\ud83d\udd38-index-\u0915\u0948\u0938\u0947-\u092c\u0928\u093e\u090f\u0902\">\ud83d\udd38 Index \u0915\u0948\u0938\u0947 \u092c\u0928\u093e\u090f\u0902?<\/a><\/li><li><a href=\"#\ud83d\udd38-composite-index-multi-column-index\">\ud83d\udd38 Composite Index (multi-column index)<\/a><\/li><\/ul><\/li><li><a href=\"#\ud83d\udd0d-4-where-filter-\u0932\u093f\u0916\u0924\u0947-\u0938\u092e\u092f-performance-tips\">\ud83d\udd0d 4. WHERE Filter \u0932\u093f\u0916\u0924\u0947 \u0938\u092e\u092f SQL Performance Tips<\/a><\/li><li><a href=\"#\ud83d\udd17-5-join-\u0915\u0930\u0924\u0947-\u0938\u092e\u092f-performance-\u092c\u0922\u093c\u093e\u0928\u0947-\u0915\u0947-tips\">\ud83d\udd17 5. JOIN \u0915\u0930\u0924\u0947 \u0938\u092e\u092f SQL Performance \u092c\u0922\u093c\u093e\u0928\u0947 \u0915\u0947 Tips<\/a><\/li><li><a href=\"#\ud83e\uddfe-6-aggregate-functions-sum-count-avg-optimize-\u0915\u0930\u0928\u0947-\u0915\u0947-\u0924\u0930\u0940\u0915\u0947\">\ud83e\uddfe 6. Aggregate Functions (SUM, COUNT, AVG&#8230;) Optimize \u0915\u0930\u0928\u0947 \u0915\u0947 \u0924\u0930\u0940\u0915\u0947<\/a><\/li><li><a href=\"#\ud83d\ude80-7-\u091c\u092c-table-\u092e\u0947\u0902-data-\u092c\u0939\u0941\u0924-\u091c\u093c\u094d\u092f\u093e\u0926\u093e-\u0939\u094b-large-tables\">\ud83d\ude80 7. \u091c\u092c Table \u092e\u0947\u0902 Data \u092c\u0939\u0941\u0924 \u091c\u093c\u094d\u092f\u093e\u0926\u093e \u0939\u094b (Large Tables)<\/a><\/li><li><a href=\"#\ud83e\udde0-8-extra-optimization-tips\">\ud83e\udde0 8. Extra Optimization Tips<\/a><\/li><li><a href=\"#\u270d\ufe0f-\u0928\u093f\u0937\u094d\u0915\u0930\u094d\u0937-conclusion-sql-performance\">\u270d\ufe0f \u0928\u093f\u0937\u094d\u0915\u0930\u094d\u0937 (Conclusion) SQL Performance<\/a><\/li><li><a href=\"#strong-\u0939\u092e\u093e\u0930\u0947-\u0905\u0928\u094d\u092f-\u0906\u0930\u094d\u091f\u093f\u0915\u0932-strong\">\u0939\u092e\u093e\u0930\u0947 \u0905\u0928\u094d\u092f \u0906\u0930\u094d\u091f\u093f\u0915\u0932<\/a><\/li><\/ul><\/li><\/ul><\/nav><\/div>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"\ud83e\uddf1-1-table-design-\u0915\u0930\u0924\u0947-\u0938\u092e\u092f-\u0915\u094d\u092f\u093e-\u0927\u094d\u092f\u093e\u0928-\u0930\u0916\u0947\u0902\">\ud83e\uddf1 1. Table Design \u0915\u0930\u0924\u0947 \u0938\u092e\u092f \u0915\u094d\u092f\u093e \u0927\u094d\u092f\u093e\u0928 \u0930\u0916\u0947\u0902<\/h2>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>Normalization \u0915\u093e \u0907\u0938\u094d\u0924\u0947\u092e\u093e\u0932 \u0915\u0930\u0947\u0902 (3NF \u0924\u0915)<\/strong>\n<ul class=\"wp-block-list\">\n<li>\u0921\u093e\u091f\u093e \u0915\u094b \u0905\u0932\u0917-\u0905\u0932\u0917 \u0930\u093f\u0932\u0947\u091f\u0947\u0921 \u091f\u0947\u092c\u0932\u094d\u0938 \u092e\u0947\u0902 \u092c\u093e\u0901\u091f\u0947\u0902\u0964<\/li>\n\n\n\n<li>Duplicate \u0921\u093e\u091f\u093e \u0928\u093e \u0930\u0916\u0947\u0902\u0964<\/li>\n\n\n\n<li>\u0907\u0938\u0938\u0947 \u0921\u093e\u091f\u093e \u091b\u094b\u091f\u093e, \u0938\u093e\u092b\u093c \u0914\u0930 maintainable \u0930\u0939\u0924\u093e \u0939\u0948\u0964<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>Unnecessary Columns \u092e\u0924 \u092c\u0928\u093e\u090f\u0902<\/strong>\n<ul class=\"wp-block-list\">\n<li>Example: \u0905\u0917\u0930 \u0938\u093f\u0930\u094d\u092b Date \u091a\u093e\u0939\u093f\u090f \u0924\u094b <code>DATETIME<\/code> \u0915\u0940 \u091c\u0917\u0939 <code>DATE<\/code> \u0930\u0916\u0947\u0902\u0964<\/li>\n\n\n\n<li>\u0905\u0917\u0930 Gender \u0938\u093f\u0930\u094d\u092b \u2018M\u2019 \u092f\u093e \u2018F\u2019 \u0939\u0948 \u0924\u094b <code>CHAR(1)<\/code> \u0939\u0940 \u0915\u093e\u092b\u0940 \u0939\u0948\u0964<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>Proper Primary Key \u091a\u0941\u0928\u0947\u0902<\/strong>\n<ul class=\"wp-block-list\">\n<li>\u0939\u092e\u0947\u0936\u093e <strong>numeric primary key<\/strong> (\u091c\u0948\u0938\u0947 <code>INT AUTO_INCREMENT<\/code>) \u0915\u093e \u0907\u0938\u094d\u0924\u0947\u092e\u093e\u0932 \u0915\u0930\u0947\u0902\u0964<\/li>\n\n\n\n<li>Text primary key \u092f\u093e multi-column key \u092a\u0930 join \u0927\u0940\u092e\u0947 \u091a\u0932\u0924\u0947 \u0939\u0948\u0902\u0964<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>Foreign Key \u0915\u093e \u092a\u094d\u0930\u092f\u094b\u0917 \u0938\u094b\u091a-\u0938\u092e\u091d\u0915\u0930 \u0915\u0930\u0947\u0902<\/strong>\n<ul class=\"wp-block-list\">\n<li>\u0905\u0917\u0930 constraints \u0915\u0940 \u091c\u0930\u0942\u0930\u0924 \u0928\u0939\u0940\u0902 \u0939\u0948 \u0924\u094b \u0938\u093f\u0930\u094d\u092b indexing \u0938\u0947 \u092d\u0940 \u0915\u093e\u092e \u091a\u0932 \u091c\u093e\u0924\u093e \u0939\u0948\u0964<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>Storage Engine \u0915\u093e \u091a\u0941\u0928\u093e\u0935 \u0915\u0930\u0947\u0902<\/strong>\n<ul class=\"wp-block-list\">\n<li>\u091c\u093c\u094d\u092f\u093e\u0926\u093e\u0924\u0930 \u0915\u0947\u0938 \u092e\u0947\u0902 <code>InnoDB<\/code> \u092c\u0947\u0939\u0924\u0930 \u0939\u0948 \u0915\u094d\u092f\u094b\u0902\u0915\u093f \u092f\u0939 transaction-safe \u0914\u0930 \u0924\u0947\u091c\u093c \u0939\u0948\u0964<\/li>\n\n\n\n<li>\u0905\u0917\u0930 read-heavy \u0938\u093f\u0938\u094d\u091f\u092e \u0939\u0948 (reporting type), \u0924\u094b <code>MyISAM<\/code> \u092a\u0930 \u0935\u093f\u091a\u093e\u0930 \u0915\u0930 \u0938\u0915\u0924\u0947 \u0939\u0948\u0902\u0964<\/li>\n<\/ul>\n<\/li>\n<\/ol>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"\ud83e\uddee-2-column-\u092c\u0928\u093e\u0924\u0947-\u0938\u092e\u092f-\u0938\u0939\u0940-data-type-\u091a\u0941\u0928\u0947\u0902\">\ud83e\uddee 2. Column \u092c\u0928\u093e\u0924\u0947 \u0938\u092e\u092f \u0938\u0939\u0940 Data Type \u091a\u0941\u0928\u0947\u0902<\/h2>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>Numeric Columns<\/strong>\n<ul class=\"wp-block-list\">\n<li>Size \u091c\u093f\u0924\u0928\u093e \u091b\u094b\u091f\u093e \u0930\u0916 \u0938\u0915\u0924\u0947 \u0939\u0948\u0902 \u0930\u0916\u0947\u0902\u0964<br>\u0909\u0926\u093e\u0939\u0930\u0923: <code>TINYINT<\/code> (1 byte), <code>INT<\/code> (4 byte), <code>BIGINT<\/code> (8 byte)\u0964<br>\u0905\u0917\u0930 \u092f\u0942\u091c\u093c\u0930 \u0915\u0940 \u0917\u093f\u0928\u0924\u0940 10000 \u0938\u0947 \u0915\u092e \u0939\u0948 \u0924\u094b <code>SMALLINT<\/code> \u0915\u093e\u092b\u0940 \u0939\u0948\u0964<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>Text Columns<\/strong>\n<ul class=\"wp-block-list\">\n<li>Fixed length (<code>CHAR<\/code>) vs Variable length (<code>VARCHAR<\/code>)\n<ul class=\"wp-block-list\">\n<li>\u0905\u0917\u0930 length fix \u0939\u0948 \u091c\u0948\u0938\u0947 PIN code (6 digit), \u0924\u094b <code>CHAR(6)<\/code>\u0964<\/li>\n\n\n\n<li>\u0905\u0917\u0930 variable \u0939\u0948 \u091c\u0948\u0938\u0947 \u0928\u093e\u092e \u092f\u093e \u092a\u0924\u093e, \u0924\u094b <code>VARCHAR(100)<\/code>\u0964<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>Date Columns<\/strong>\n<ul class=\"wp-block-list\">\n<li>\u0915\u0947\u0935\u0932 \u0924\u093e\u0930\u0940\u0916 \u091a\u093e\u0939\u093f\u090f \u0924\u094b <code>DATE<\/code><\/li>\n\n\n\n<li>\u0924\u093e\u0930\u0940\u0916 \u0914\u0930 \u0938\u092e\u092f \u0926\u094b\u0928\u094b\u0902 \u091a\u093e\u0939\u093f\u090f \u0924\u094b <code>DATETIME<\/code> \u092f\u093e <code>TIMESTAMP<\/code>\u0964<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>Default Values \u0915\u093e \u0907\u0938\u094d\u0924\u0947\u092e\u093e\u0932 \u0915\u0930\u0947\u0902<\/strong>\n<ul class=\"wp-block-list\">\n<li>Null values \u0915\u092e \u0930\u0916\u0947\u0902 \u0915\u094d\u092f\u094b\u0902\u0915\u093f \u0935\u094b comparison \u092e\u0947\u0902 extra cost \u092c\u0922\u093c\u093e\u0924\u0947 \u0939\u0948\u0902\u0964<\/li>\n<\/ul>\n<\/li>\n<\/ol>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"\u2699\ufe0f-3-indexing-performance-\u0915\u093e-\u0905\u0938\u0932\u0940-\u0939\u0925\u093f\u092f\u093e\u0930\">\u2699\ufe0f 3. Indexing \u2013 SQL Performance \u0915\u093e \u0905\u0938\u0932\u0940 \u0939\u0925\u093f\u092f\u093e\u0930<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"\ud83d\udd38-index-\u0915\u094d\u092f\u093e-\u0915\u0930\u0924\u093e-\u0939\u0948\">\ud83d\udd38 Index \u0915\u094d\u092f\u093e \u0915\u0930\u0924\u093e \u0939\u0948?<\/h3>\n\n\n\n<p>Index MySQL \u0915\u094b data \u091c\u0932\u094d\u0926\u0940 \u0922\u0942\u0902\u0922\u0928\u0947 \u092e\u0947\u0902 \u092e\u0926\u0926 \u0915\u0930\u0924\u093e \u0939\u0948 \u2014 \u091c\u0948\u0938\u0947 \u0915\u093f\u0924\u093e\u092c \u092e\u0947\u0902 Index page\u0964<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"\ud83d\udd38-index-\u0915\u092c-\u092c\u0928\u093e\u0928\u093e-\u091a\u093e\u0939\u093f\u090f\">\ud83d\udd38 Index \u0915\u092c \u092c\u0928\u093e\u0928\u093e \u091a\u093e\u0939\u093f\u090f?<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u091c\u094b column <strong>WHERE<\/strong>, <strong>JOIN<\/strong>, <strong>ORDER BY<\/strong>, <strong>GROUP BY<\/strong> \u092e\u0947\u0902 \u092c\u093e\u0930-\u092c\u093e\u0930 \u0906\u0924\u093e \u0939\u0948,<br>\u0909\u0928 \u092a\u0930 index \u092c\u0928\u093e\u090f\u0902\u0964<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"\ud83d\udd38-index-\u0915\u0948\u0938\u0947-\u092c\u0928\u093e\u090f\u0902\">\ud83d\udd38 Index \u0915\u0948\u0938\u0947 \u092c\u0928\u093e\u090f\u0902?<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code class=\"\">CREATE INDEX idx_customer_name ON customers(customer_name);\n<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"\ud83d\udd38-composite-index-multi-column-index\">\ud83d\udd38 Composite Index (multi-column index)<\/h3>\n\n\n\n<p>\u0905\u0917\u0930 \u0905\u0915\u094d\u0938\u0930 \u092f\u0939 query \u091a\u0932\u0924\u0940 \u0939\u0948:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code class=\"\">SELECT * FROM orders WHERE customer_id = 5 AND order_date = '2025-10-01';\n<\/code><\/pre>\n\n\n\n<p>\u0924\u094b composite index \u092c\u0928\u093e\u090f\u0902:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code class=\"\">CREATE INDEX idx_cust_orderdate ON orders(customer_id, order_date);\n<\/code><\/pre>\n\n\n\n<p>\ud83d\udccc \u0927\u094d\u092f\u093e\u0928 \u0926\u0947\u0902:<br>Composite index \u0939\u092e\u0947\u0936\u093e <strong>left to right rule<\/strong> \u092a\u0930 \u091a\u0932\u0924\u093e \u0939\u0948 \u2014 \u092f\u093e\u0928\u0940 \u092a\u0939\u0932\u093e column \u0939\u092e\u0947\u0936\u093e WHERE \u092e\u0947\u0902 \u0939\u094b\u0928\u093e \u091a\u093e\u0939\u093f\u090f\u0964<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"\ud83d\udd0d-4-where-filter-\u0932\u093f\u0916\u0924\u0947-\u0938\u092e\u092f-performance-tips\">\ud83d\udd0d 4. WHERE Filter \u0932\u093f\u0916\u0924\u0947 \u0938\u092e\u092f SQL Performance Tips<\/h2>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>Function-based condition \u0938\u0947 \u092c\u091a\u0947\u0902<\/strong><br>\u274c <code>WHERE YEAR(order_date) = 2025<\/code><br>\u2705 <code>WHERE order_date BETWEEN '2025-01-01' AND '2025-12-31'<\/code><\/li>\n\n\n\n<li><strong>LIKE \u092e\u0947\u0902 \u0936\u0941\u0930\u0941\u0906\u0924 \u092e\u0947\u0902 % \u0938\u0947 \u092c\u091a\u0947\u0902<\/strong><br>\u274c <code>WHERE name LIKE '%ram%'<\/code><br>\u2705 <code>WHERE name LIKE 'ram%'<\/code><\/li>\n\n\n\n<li><strong>IN \u0915\u0940 \u091c\u0917\u0939 EXISTS \u092f\u093e JOIN \u0915\u093e \u092a\u094d\u0930\u092f\u094b\u0917 \u0915\u0930\u0947\u0902 (\u0915\u0941\u091b \u0915\u0947\u0938 \u092e\u0947\u0902 \u0924\u0947\u091c\u093c \u0939\u094b\u0924\u093e \u0939\u0948)<\/strong>  <code>SELECT * FROM orders WHERE EXISTS (SELECT 1 FROM customers WHERE customers.id = orders.customer_id);<\/code><\/li>\n\n\n\n<li><strong>LIMIT \u0915\u093e \u092a\u094d\u0930\u092f\u094b\u0917 \u0915\u0930\u0947\u0902<\/strong>\n<ul class=\"wp-block-list\">\n<li>\u0905\u0917\u0930 \u092c\u0939\u0941\u0924 \u092c\u0921\u093c\u093e \u0921\u0947\u091f\u093e \u0939\u0948 \u0924\u094b pagination \u091c\u093c\u0930\u0942\u0930\u0940 \u0939\u0948\u0964<br>\u091c\u0948\u0938\u0947: <code>LIMIT 50 OFFSET 0<\/code><\/li>\n<\/ul>\n<\/li>\n<\/ol>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"\ud83d\udd17-5-join-\u0915\u0930\u0924\u0947-\u0938\u092e\u092f-performance-\u092c\u0922\u093c\u093e\u0928\u0947-\u0915\u0947-tips\">\ud83d\udd17 5. JOIN \u0915\u0930\u0924\u0947 \u0938\u092e\u092f SQL Performance \u092c\u0922\u093c\u093e\u0928\u0947 \u0915\u0947 Tips<\/h2>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>Join Columns \u092a\u0930 Index \u0939\u094b\u0928\u093e \u091a\u093e\u0939\u093f\u090f<\/strong> <code>CREATE INDEX idx_customer_id ON orders(customer_id);<\/code><\/li>\n\n\n\n<li><strong>Join order maintain \u0915\u0930\u0947\u0902<\/strong>\n<ul class=\"wp-block-list\">\n<li>\u0939\u092e\u0947\u0936\u093e \u091b\u094b\u091f\u0940 \u091f\u0947\u092c\u0932 \u0915\u094b \u092a\u0939\u0932\u0947 join \u0915\u0930\u0947\u0902, \u092b\u093f\u0930 \u092c\u0921\u093c\u0940 \u091f\u0947\u092c\u0932 \u0915\u094b\u0964<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>Only Required Columns Select \u0915\u0930\u0947\u0902<\/strong><br>\u274c <code>SELECT *<\/code><br>\u2705 <code>SELECT name, amount, order_date<\/code><\/li>\n\n\n\n<li><strong>INNER JOIN vs LEFT JOIN<\/strong>\n<ul class=\"wp-block-list\">\n<li>\u091c\u092c \u0926\u094b\u0928\u094b\u0902 \u091f\u0947\u092c\u0932 \u092e\u0947\u0902 matching data \u091a\u093e\u0939\u093f\u090f, \u0924\u092c <code>INNER JOIN<\/code>\u0964<\/li>\n\n\n\n<li>Performance-wise <code>INNER JOIN<\/code> \u0925\u094b\u0921\u093c\u093e \u0924\u0947\u091c\u093c \u0939\u094b\u0924\u093e \u0939\u0948\u0964<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>JOIN \u0915\u0947 \u092c\u093e\u0926 Aggregate (SUM, COUNT) \u0915\u0930\u0928\u0947 \u0938\u0947 \u092a\u0939\u0932\u0947 Filtering \u0915\u0930\u0947\u0902<\/strong> <code>SELECT c.name, SUM(o.amount) FROM orders o INNER JOIN customers c ON c.id = o.customer_id WHERE o.status = 'PAID' GROUP BY c.name;<\/code><\/li>\n<\/ol>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"\ud83e\uddfe-6-aggregate-functions-sum-count-avg-optimize-\u0915\u0930\u0928\u0947-\u0915\u0947-\u0924\u0930\u0940\u0915\u0947\">\ud83e\uddfe 6. Aggregate Functions (SUM, COUNT, AVG&#8230;) Optimize \u0915\u0930\u0928\u0947 \u0915\u0947 \u0924\u0930\u0940\u0915\u0947<\/h2>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>GROUP BY Columns \u092a\u0930 Index \u092c\u0928\u093e\u090f\u0902<\/strong> <code>CREATE INDEX idx_group_status ON orders(status);<\/code><\/li>\n\n\n\n<li><strong>Filtered Aggregation \u0915\u0930\u0947\u0902<\/strong><br>\u092f\u093e\u0928\u0940 \u092a\u0939\u0932\u0947 WHERE \u0932\u0917\u093e\u090f\u0902, \u092b\u093f\u0930 SUM \u0915\u0930\u0947\u0902\u0964<\/li>\n\n\n\n<li><strong>Subquery \u0915\u0940 \u091c\u0917\u0939 Derived Table \u092f\u093e Temporary Table \u0915\u093e \u092a\u094d\u0930\u092f\u094b\u0917 \u0915\u0930\u0947\u0902<\/strong><br>\u0905\u0917\u0930 \u092c\u093e\u0930-\u092c\u093e\u0930 \u0935\u0939\u0940 calculation \u0915\u0930\u0928\u0940 \u0939\u0948 \u0924\u094b \u090f\u0915 temporary table \u092c\u0928\u093e \u0932\u0947\u0902\u0964<\/li>\n<\/ol>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"\ud83d\ude80-7-\u091c\u092c-table-\u092e\u0947\u0902-data-\u092c\u0939\u0941\u0924-\u091c\u093c\u094d\u092f\u093e\u0926\u093e-\u0939\u094b-large-tables\">\ud83d\ude80 7. \u091c\u092c Table \u092e\u0947\u0902 Data \u092c\u0939\u0941\u0924 \u091c\u093c\u094d\u092f\u093e\u0926\u093e \u0939\u094b (Large Tables)<\/h2>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>Partitioning \u0915\u093e \u0907\u0938\u094d\u0924\u0947\u092e\u093e\u0932 \u0915\u0930\u0947\u0902<\/strong>\n<ul class=\"wp-block-list\">\n<li>\u091c\u0948\u0938\u0947 <code>order_date<\/code> \u0915\u0947 \u0939\u093f\u0938\u093e\u092c \u0938\u0947 year-wise partitions\u0964<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>Archiving \u092a\u0941\u0930\u093e\u0928\u093e \u0921\u0947\u091f\u093e<\/strong>\n<ul class=\"wp-block-list\">\n<li>\u092a\u0941\u0930\u093e\u0928\u093e (inactive) \u0921\u0947\u091f\u093e \u0915\u093f\u0938\u0940 \u0926\u0942\u0938\u0930\u0947 archive table \u092e\u0947\u0902 shift \u0915\u0930\u0947\u0902\u0964<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>EXPLAIN \u0915\u092e\u093e\u0902\u0921 \u0938\u0947 Query Analyze \u0915\u0930\u0947\u0902<\/strong> <code>EXPLAIN SELECT * FROM orders WHERE customer_id = 5;<\/code> \u092f\u0939 \u092c\u0924\u093e\u090f\u0917\u093e \u0915\u093f \u0915\u094c\u0928 \u0938\u093e index use \u0939\u094b \u0930\u0939\u093e \u0939\u0948 \u0914\u0930 \u0915\u094c\u0928 \u0938\u093e \u0928\u0939\u0940\u0902\u0964<\/li>\n\n\n\n<li><strong>Slow Query Log \u0911\u0928 \u0915\u0930\u0947\u0902<\/strong> <code>slow_query_log = 1 long_query_time = 2<\/code> \u0907\u0938\u0938\u0947 \u0906\u092a \u0926\u0947\u0916 \u092a\u093e\u090f\u0902\u0917\u0947 \u0915\u094c\u0928 \u0938\u0940 query \u0938\u092c\u0938\u0947 \u0927\u0940\u092e\u0940 \u0939\u0948\u0964<\/li>\n<\/ol>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"\ud83e\udde0-8-extra-optimization-tips\">\ud83e\udde0 8. Extra Optimization Tips<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Caching \u0915\u093e \u0909\u092a\u092f\u094b\u0917 \u0915\u0930\u0947\u0902<\/strong> (\u091c\u0948\u0938\u0947 Redis \u092f\u093e Memcached)<\/li>\n\n\n\n<li><strong>Prepared Statements<\/strong> \u0915\u093e \u0907\u0938\u094d\u0924\u0947\u092e\u093e\u0932 \u0915\u0930\u0947\u0902\u0964<\/li>\n\n\n\n<li><strong>Avoid too many joins<\/strong> (3-4 \u0938\u0947 \u091c\u093c\u094d\u092f\u093e\u0926\u093e join performance \u0918\u091f\u093e \u0938\u0915\u0924\u0947 \u0939\u0948\u0902)<\/li>\n\n\n\n<li><strong>Regularly Analyze and Optimize Tables<\/strong> <code>ANALYZE TABLE orders; OPTIMIZE TABLE orders;<\/code><\/li>\n<\/ul>\n\n\n\n<p><\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"\u270d\ufe0f-\u0928\u093f\u0937\u094d\u0915\u0930\u094d\u0937-conclusion-sql-performance\">\u270d\ufe0f \u0928\u093f\u0937\u094d\u0915\u0930\u094d\u0937 (Conclusion) SQL Performance<\/h2>\n\n\n\n<p>\u0905\u0917\u0930 \u0906\u092a MySQL \u0915\u0940 performance \u0915\u094b \u092e\u091c\u092c\u0942\u0924 \u092c\u0928\u093e\u0928\u093e \u091a\u093e\u0939\u0924\u0947 \u0939\u0948\u0902 \u0924\u094b \u0936\u0941\u0930\u0941\u0906\u0924 \u0915\u0930\u0947\u0902 \u2014<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Table design \u0938\u0947,<\/li>\n\n\n\n<li>\u092b\u093f\u0930 \u0938\u0939\u0940 Data Type \u0938\u0947,<\/li>\n\n\n\n<li>\u092b\u093f\u0930 Index \u0914\u0930 Query Optimization \u0938\u0947\u0964<\/li>\n<\/ul>\n\n\n\n<p>\u091b\u094b\u091f\u0940-\u091b\u094b\u091f\u0940 \u0917\u0932\u0924\u093f\u092f\u093e\u0902 (\u091c\u0948\u0938\u0947 \u0917\u0932\u0924 data type, unindexed join, \u092f\u093e SELECT *) \u0906\u092a\u0915\u0940 query \u0915\u094b 10 \u0917\u0941\u0928\u093e \u0927\u0940\u092e\u093e \u092c\u0928\u093e \u0938\u0915\u0924\u0940 \u0939\u0948\u0902\u0964<br>\u0907\u0938\u0932\u093f\u090f \u0939\u092e\u0947\u0936\u093e query run \u0915\u0930\u0928\u0947 \u0938\u0947 \u092a\u0939\u0932\u0947 <code>EXPLAIN<\/code> \u0915\u0930\u0947\u0902 \u0914\u0930 index usage check \u0915\u0930\u0947\u0902\u0964<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"strong-\u0939\u092e\u093e\u0930\u0947-\u0905\u0928\u094d\u092f-\u0906\u0930\u094d\u091f\u093f\u0915\u0932-strong\"><strong>\u0939\u092e\u093e\u0930\u0947 \u0905\u0928\u094d\u092f \u0906\u0930\u094d\u091f\u093f\u0915\u0932<\/strong><\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li><a href=\"https:\/\/selfimagination.in\/tips\/mysql-function\/\">MySQL Function<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/selfimagination.in\/tips\/mysql-stored-procedure\/\">MySQL Stored Procedure<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/selfimagination.in\/tips\/get-post-method\/\">PHP Get \/Post Method \u0915\u0947 \u092c\u093e\u0930\u0947 \u092e\u0947\u0902 \u091c\u093e\u0928\u0947<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/selfimagination.in\/tips\/php-math-function\/\">PHP Math Function \u0915\u0947 \u092c\u093e\u0930\u0947 \u092e\u0947\u0902 \u091c\u093e\u0928\u0947<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/selfimagination.in\/tips\/learn-cpp-from-c\/\">C \u0938\u0947 C ++ \u0938\u0940\u0916\u0947 \u0938\u0930\u0932 \u0936\u092c\u094d\u0926\u094b\u0902 \u092e\u0947\u0902<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/selfimagination.in\/tips\/php-variables\/\">PHP Variable \u0915\u0947 \u092c\u093e\u0930\u0947 \u092e\u0947\u0902 \u091c\u093e\u0928\u0947<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/selfimagination.in\/tips\/arduino-control-structure\/\">Arduino Control Structure<\/a>\u00a0<\/li>\n\n\n\n<li><a href=\"https:\/\/selfimagination.in\/tips\/c-union\/\">Union in C<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/selfimagination.in\/tips\/c-pointer\/\">Declaration of Pointer in C<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/selfimagination.in\/tips\/search-jquery\/\">jQuery Search Filter<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/selfimagination.in\/tips\/mysql-table-create-alter-drop\/\">MySQL Create Table | Alter Table | Drop Table<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/selfimagination.in\/tips\/api-using-node-react-express-mysql\/\">API Using NODE REACT EXPRESS MYSQL SEQUELIZE (For Backend OR Server Side)<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/selfimagination.in\/tips\/cpanel\/\">CPANEL In Hindi<\/a><\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>MySQL performance, query Optimization, SQL performance, Indexing SQL Performance Tips \u0907\u0938 \u092c\u094d\u0932\u0949\u0917 \u092e\u0947 \u0939\u092e Table Creation \u0915\u0930\u0928\u0947 \u0938\u0947 \u0932\u0947\u0915\u0930 \u0909\u0938 \u092a\u0930 Join \u0932\u0917\u093e\u0928\u093e \u0914\u0930 Filter \u0915\u0930\u0928\u0947 \u0938\u0947 Related \u091c\u094b \u092d\u0940 Changes \u0915\u0930\u0928\u093e \u0939\u094b\u0924\u0947 \u0939\u0948\u0902 \u092f\u093e \u0927\u094d\u092f\u093e\u0928 \u0930\u0916\u0928\u093e \u0939\u094b\u0924\u093e \u0939\u0948\u0902 \u0935\u0939 \u0938\u092d\u0940 \u0939\u092e \u0907\u0938 Blog \u092e\u0947 \u0938\u092e\u091d\u0947\u0902\u0917\u0947 \u0964 \ud83d\udca1 \u0921\u0947\u091f\u093e\u092c\u0947\u0938 \u0915\u0940 \u0938\u094d\u092a\u0940\u0921 \u0914\u0930 \u092a\u0930\u092b\u0949\u0930\u094d\u092e\u0947\u0902\u0938 \u0915\u093f\u0938\u0940 \u092d\u0940 [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":12533,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[5],"tags":[55,54],"class_list":["post-12526","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-mysql","tag-index","tag-mysql"],"jetpack_sharing_enabled":true,"jetpack_featured_media_url":"https:\/\/i0.wp.com\/selfimagination.in\/tips\/wp-content\/uploads\/2025\/10\/SQL_Performance.jpg?fit=1200%2C628&ssl=1","_links":{"self":[{"href":"https:\/\/selfimagination.in\/tips\/wp-json\/wp\/v2\/posts\/12526","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/selfimagination.in\/tips\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/selfimagination.in\/tips\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/selfimagination.in\/tips\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/selfimagination.in\/tips\/wp-json\/wp\/v2\/comments?post=12526"}],"version-history":[{"count":10,"href":"https:\/\/selfimagination.in\/tips\/wp-json\/wp\/v2\/posts\/12526\/revisions"}],"predecessor-version":[{"id":12537,"href":"https:\/\/selfimagination.in\/tips\/wp-json\/wp\/v2\/posts\/12526\/revisions\/12537"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/selfimagination.in\/tips\/wp-json\/wp\/v2\/media\/12533"}],"wp:attachment":[{"href":"https:\/\/selfimagination.in\/tips\/wp-json\/wp\/v2\/media?parent=12526"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/selfimagination.in\/tips\/wp-json\/wp\/v2\/categories?post=12526"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/selfimagination.in\/tips\/wp-json\/wp\/v2\/tags?post=12526"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}