{"id":3993,"date":"2025-06-07T09:08:53","date_gmt":"2025-06-07T09:08:53","guid":{"rendered":"https:\/\/w3buddy.com\/?post_type=cposts&#038;p=3993"},"modified":"2025-06-07T09:08:54","modified_gmt":"2025-06-07T09:08:54","slug":"sql-index","status":"publish","type":"cposts","link":"https:\/\/w3buddy.com\/blog\/notes\/sql\/sql-index\/","title":{"rendered":"SQL INDEX"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">An <strong>INDEX<\/strong> improves the speed of data retrieval on large tables by allowing the database to quickly locate rows. It\u2019s like a book\u2019s table of contents \u2014 it doesn\u2019t change the data, just speeds up access.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">\ud83d\udd39 <strong>Basic Syntax<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code><code>-- Create an index on a single column\nCREATE INDEX idx_employee_name ON employees(name);\n\n-- Create a unique index (no duplicate values allowed)\nCREATE UNIQUE INDEX idx_employee_email ON employees(email);\n\n-- Create a composite index (on multiple columns)\nCREATE INDEX idx_emp_dept ON employees(department_id, hire_date);<\/code><\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">\ud83d\udd39 <strong>Dropping an Index<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code><code>-- MySQL \/ PostgreSQL\nDROP INDEX idx_employee_name;\n\n-- SQL Server\nDROP INDEX idx_employee_name ON employees;<\/code><\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">\ud83d\udd39 <strong>Best Practices<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Index columns used frequently in <code>WHERE<\/code>, <code>JOIN<\/code>, or <code>ORDER BY<\/code>.<\/li>\n\n\n\n<li>Avoid indexing columns with high update frequency or low selectivity (like boolean flags).<\/li>\n\n\n\n<li>Composite indexes should match query column order for maximum efficiency.<\/li>\n\n\n\n<li>Too many indexes can slow down <code>INSERT<\/code>, <code>UPDATE<\/code>, and <code>DELETE<\/code> operations.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">\ud83e\udde0 <strong>Quick Recap<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-table has-small-font-size\"><table><thead><tr><th>Key Point<\/th><th>Explanation<\/th><\/tr><\/thead><tbody><tr><td>Purpose<\/td><td>Speeds up SELECT and JOIN operations<\/td><\/tr><tr><td>Types<\/td><td>Regular, UNIQUE, Composite<\/td><\/tr><tr><td>Drop Syntax<\/td><td><code>DROP INDEX idx_name [ON table]<\/code><\/td><\/tr><tr><td>Caution<\/td><td>Too many or unnecessary indexes hurt performance<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">\ud83d\udca1 Use indexes wisely \u2014 they&#8217;re powerful for reads, but can slow down writes!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>An INDEX improves the speed of data retrieval on large tables by allowing the database to quickly locate rows. It\u2019s like a book\u2019s table of contents \u2014 it doesn\u2019t change the data, just speeds up access. \ud83d\udd39 Basic Syntax \ud83d\udd39 Dropping an Index \ud83d\udd39 Best Practices \ud83e\udde0 Quick Recap Key Point Explanation Purpose Speeds up [&hellip;]<\/p>\n","protected":false},"template":"","meta":{"googlesitekit_rrm_CAowu461DA:productID":""},"categories":[952,979],"class_list":["post-3993","cposts","type-cposts","status-publish","hentry","category-notes","category-sql"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/cposts\/3993","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/cposts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/cposts"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=3993"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=3993"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}