<h1 data-start="753" data-end="810">Why Your MySQL Index Is Not Working (And How to Fix It)</h1>
<p data-start="812" data-end="1084">Indexes are one of the most powerful tools in MySQL for improving query performance. When used correctly, they reduce full table scans and dramatically speed up data retrieval. However, many developers encounter situations where they create an index &mdash; and nothing changes.</p>
<p data-start="1086" data-end="1243">If your <strong data-start="1094" data-end="1124">MySQL index is not working</strong>, the issue is rarely MySQL itself. It is usually related to query structure, data distribution, or optimizer behavior.</p>
<p data-start="1245" data-end="1305">Let&rsquo;s break it down from a database performance perspective.</p>
<hr data-start="1307" data-end="1310">
<h2 data-start="1312" data-end="1356">1. You&rsquo;re Not Checking the Execution Plan</h2>
<p data-start="1358" data-end="1416">Before assuming the index is ignored, always verify using:</p>
<div class="contain-inline-size rounded-2xl corner-superellipse/1.1 relative bg-token-sidebar-surface-primary">
<div class="sticky top-[calc(var(--sticky-padding-top)+9*var(--spacing))]">
<div class="absolute end-0 bottom-0 flex h-9 items-center pe-2">
<div class="bg-token-bg-elevated-secondary text-token-text-secondary flex items-center gap-4 rounded-sm px-2 font-sans text-xs">&nbsp;</div>
</div>
</div>
<div class="overflow-y-auto p-4" dir="ltr"><code class="whitespace-pre! language-sql">EXPLAIN <span class="hljs-keyword">SELECT</span> <span class="hljs-operator">*</span> <span class="hljs-keyword">FROM</span> users <span class="hljs-keyword">WHERE</span> email <span class="hljs-operator">=</span> <span class="hljs-string">'test@example.com'</span>;
</code></div>
</div>
<p data-start="1492" data-end="1503">If you see:</p>
<ul data-start="1504" data-end="1571">
<li data-start="1504" data-end="1541">
<p data-start="1506" data-end="1541"><code data-start="1506" data-end="1517">type: ALL</code> &rarr; Full table scan (bad)</p>
</li>
<li data-start="1542" data-end="1571">
<p data-start="1544" data-end="1571"><code data-start="1544" data-end="1555">key: NULL</code> &rarr; No index used</p>
</li>
</ul>
<p data-start="1573" data-end="1619">This confirms the index is not being utilized.</p>
<p data-start="1621" data-end="1722">The first step in diagnosing performance is understanding how MySQL&rsquo;s optimizer evaluates your query.</p>
<hr data-start="1724" data-end="1727">
<h2 data-start="1729" data-end="1758">2. Low Selectivity Columns</h2>
<p data-start="1760" data-end="1832">Indexes work best on columns with high cardinality (many unique values).</p>
<p data-start="1834" data-end="1846">For example:</p>
<ul data-start="1848" data-end="1947">
<li data-start="1848" data-end="1897">
<p data-start="1850" data-end="1897">Good candidate: <code data-start="1866" data-end="1873">email</code>, <code data-start="1875" data-end="1885">username</code>, <code data-start="1887" data-end="1897">order_id</code></p>
</li>
<li data-start="1898" data-end="1947">
<p data-start="1900" data-end="1947">Poor candidate: <code data-start="1916" data-end="1924">gender</code>, <code data-start="1926" data-end="1934">status</code>, <code data-start="1936" data-end="1947">is_active</code></p>
</li>
</ul>
<p data-start="1949" data-end="2051">If most rows share the same value, MySQL may decide a full table scan is cheaper than using the index.</p>
<p data-start="2053" data-end="2061">Example:</p>
<div class="contain-inline-size rounded-2xl corner-superellipse/1.1 relative bg-token-sidebar-surface-primary">
<div class="sticky top-[calc(var(--sticky-padding-top)+9*var(--spacing))]">
<div class="absolute end-0 bottom-0 flex h-9 items-center pe-2">
<div class="bg-token-bg-elevated-secondary text-token-text-secondary flex items-center gap-4 rounded-sm px-2 font-sans text-xs">&nbsp;</div>
</div>
</div>
<div class="overflow-y-auto p-4" dir="ltr"><code class="whitespace-pre! language-sql"><span class="hljs-keyword">CREATE</span> INDEX idx_status <span class="hljs-keyword">ON</span> users(status);
</code></div>
</div>
<p data-start="2117" data-end="2187">If 95% of rows have <code data-start="2137" data-end="2156">status = 'active'</code>, MySQL might ignore the index.</p>
<p data-start="2189" data-end="2293">Indexes are about reducing search space. If they don&rsquo;t significantly narrow results, they won&rsquo;t be used.</p>
<hr data-start="2295" data-end="2298">
<h2 data-start="2300" data-end="2340">3. Using Functions on Indexed Columns</h2>
<p data-start="2342" data-end="2382">This is one of the most common mistakes.</p>
<p data-start="2384" data-end="2397">If you write:</p>
<div class="contain-inline-size rounded-2xl corner-superellipse/1.1 relative bg-token-sidebar-surface-primary">
<div class="sticky top-[calc(var(--sticky-padding-top)+9*var(--spacing))]">
<div class="absolute end-0 bottom-0 flex h-9 items-center pe-2">
<div class="bg-token-bg-elevated-secondary text-token-text-secondary flex items-center gap-4 rounded-sm px-2 font-sans text-xs">&nbsp;</div>
</div>
</div>
<div class="overflow-y-auto p-4" dir="ltr"><code class="whitespace-pre! language-sql"><span class="hljs-keyword">SELECT</span> <span class="hljs-operator">*</span> <span class="hljs-keyword">FROM</span> users <span class="hljs-keyword">WHERE</span> <span class="hljs-type">DATE</span>(created_at) <span class="hljs-operator">=</span> <span class="hljs-string">'2025-01-01'</span>;
</code></div>
</div>
<p data-start="2470" data-end="2576">Even if <code data-start="2478" data-end="2490">created_at</code> is indexed, MySQL cannot use it effectively because the function modifies the column.</p>
<p data-start="2578" data-end="2601">Instead, rewrite it as:</p>
<div class="contain-inline-size rounded-2xl corner-superellipse/1.1 relative bg-token-sidebar-surface-primary">
<div class="sticky top-[calc(var(--sticky-padding-top)+9*var(--spacing))]">
<div class="absolute end-0 bottom-0 flex h-9 items-center pe-2">
<div class="bg-token-bg-elevated-secondary text-token-text-secondary flex items-center gap-4 rounded-sm px-2 font-sans text-xs">&nbsp;</div>
</div>
</div>
<div class="overflow-y-auto p-4" dir="ltr"><code class="whitespace-pre! language-sql"><span class="hljs-keyword">SELECT</span> <span class="hljs-operator">*</span> <span class="hljs-keyword">FROM</span> users
<span class="hljs-keyword">WHERE</span> created_at <span class="hljs-operator">&gt;=</span> <span class="hljs-string">'2025-01-01 00:00:00'</span>
<span class="hljs-keyword">AND</span> created_at <span class="hljs-operator">&lt;</span> <span class="hljs-string">'2025-01-02 00:00:00'</span>;
</code></div>
</div>
<p data-start="2718" data-end="2791">Indexes work when the column is used directly &mdash; not wrapped in functions.</p>
<hr data-start="2793" data-end="2796">
<h2 data-start="2798" data-end="2828">4. Wrong Data Type Mismatch</h2>
<p data-start="2830" data-end="2883">If your column is <code data-start="2848" data-end="2853">INT</code> but you query it as a string:</p>
<div class="contain-inline-size rounded-2xl corner-superellipse/1.1 relative bg-token-sidebar-surface-primary">
<div class="sticky top-[calc(var(--sticky-padding-top)+9*var(--spacing))]">
<div class="absolute end-0 bottom-0 flex h-9 items-center pe-2">
<div class="bg-token-bg-elevated-secondary text-token-text-secondary flex items-center gap-4 rounded-sm px-2 font-sans text-xs">&nbsp;</div>
</div>
</div>
<div class="overflow-y-auto p-4" dir="ltr"><code class="whitespace-pre! language-sql"><span class="hljs-keyword">WHERE</span> user_id <span class="hljs-operator">=</span> <span class="hljs-string">'100'</span>
</code></div>
</div>
<p data-start="2919" data-end="2987">MySQL may perform implicit conversion, which can affect index usage.</p>
<p data-start="2989" data-end="3003">Always ensure:</p>
<ul data-start="3004" data-end="3076">
<li data-start="3004" data-end="3047">
<p data-start="3006" data-end="3047">Query parameter types match column types.</p>
</li>
<li data-start="3048" data-end="3076">
<p data-start="3050" data-end="3076">Avoid unnecessary casting.</p>
</li>
</ul>
<hr data-start="3078" data-end="3081">
<h2 data-start="3083" data-end="3122">5. Leading Wildcards in LIKE Queries</h2>
<p data-start="3124" data-end="3173">Indexes cannot help when using leading wildcards.</p>
<p data-start="3175" data-end="3179">Bad:</p>
<div class="contain-inline-size rounded-2xl corner-superellipse/1.1 relative bg-token-sidebar-surface-primary">
<div class="sticky top-[calc(var(--sticky-padding-top)+9*var(--spacing))]">
<div class="absolute end-0 bottom-0 flex h-9 items-center pe-2">
<div class="bg-token-bg-elevated-secondary text-token-text-secondary flex items-center gap-4 rounded-sm px-2 font-sans text-xs">&nbsp;</div>
</div>
</div>
<div class="overflow-y-auto p-4" dir="ltr"><code class="whitespace-pre! language-sql"><span class="hljs-keyword">SELECT</span> <span class="hljs-operator">*</span> <span class="hljs-keyword">FROM</span> products <span class="hljs-keyword">WHERE</span> name <span class="hljs-keyword">LIKE</span> <span class="hljs-string">'%phone'</span>;
</code></div>
</div>
<p data-start="3242" data-end="3266">This forces a full scan.</p>
<p data-start="3268" data-end="3275">Better:</p>
<div class="contain-inline-size rounded-2xl corner-superellipse/1.1 relative bg-token-sidebar-surface-primary">
<div class="sticky top-[calc(var(--sticky-padding-top)+9*var(--spacing))]">
<div class="absolute end-0 bottom-0 flex h-9 items-center pe-2">
<div class="bg-token-bg-elevated-secondary text-token-text-secondary flex items-center gap-4 rounded-sm px-2 font-sans text-xs">&nbsp;</div>
</div>
</div>
<div class="overflow-y-auto p-4" dir="ltr"><code class="whitespace-pre! language-sql"><span class="hljs-keyword">SELECT</span> <span class="hljs-operator">*</span> <span class="hljs-keyword">FROM</span> products <span class="hljs-keyword">WHERE</span> name <span class="hljs-keyword">LIKE</span> <span class="hljs-string">'phone%'</span>;
</code></div>
</div>
<p data-start="3338" data-end="3381">If you need flexible text search, consider:</p>
<ul data-start="3382" data-end="3448">
<li data-start="3382" data-end="3400">
<p data-start="3384" data-end="3400">FULLTEXT indexes</p>
</li>
<li data-start="3401" data-end="3448">
<p data-start="3403" data-end="3448">External search engines (e.g., Elasticsearch)</p>
</li>
</ul>
<hr data-start="3450" data-end="3453">
<h2 data-start="3455" data-end="3483">6. Composite Index Misuse</h2>
<p data-start="3485" data-end="3518">If you created a composite index:</p>
<div class="contain-inline-size rounded-2xl corner-superellipse/1.1 relative bg-token-sidebar-surface-primary">
<div class="sticky top-[calc(var(--sticky-padding-top)+9*var(--spacing))]">
<div class="absolute end-0 bottom-0 flex h-9 items-center pe-2">
<div class="bg-token-bg-elevated-secondary text-token-text-secondary flex items-center gap-4 rounded-sm px-2 font-sans text-xs">&nbsp;</div>
</div>
</div>
<div class="overflow-y-auto p-4" dir="ltr"><code class="whitespace-pre! language-sql"><span class="hljs-keyword">CREATE</span> INDEX idx_user_email_status <span class="hljs-keyword">ON</span> users(email, status);
</code></div>
</div>
<p data-start="3592" data-end="3615">It works in this order:</p>
<ul data-start="3617" data-end="3641">
<li data-start="3617" data-end="3624">
<p data-start="3619" data-end="3624">email</p>
</li>
<li data-start="3625" data-end="3641">
<p data-start="3627" data-end="3641">email + status</p>
</li>
</ul>
<p data-start="3643" data-end="3651">But NOT:</p>
<ul data-start="3653" data-end="3667">
<li data-start="3653" data-end="3667">
<p data-start="3655" data-end="3667">status alone</p>
</li>
</ul>
<p data-start="3669" data-end="3789">MySQL uses composite indexes left-to-right. If your query does not start with the first column, the index may not apply.</p>
<p data-start="3791" data-end="3829">Understanding index order is critical.</p>
<hr data-start="3831" data-end="3834">
<h2 data-start="3836" data-end="3860">7. Table Is Too Small</h2>
<p data-start="3862" data-end="3981">If your table has only a few hundred rows, MySQL may skip the index intentionally because scanning the table is faster.</p>
<p data-start="3983" data-end="4035">Indexes shine with large datasets &mdash; not tiny tables.</p>
<hr data-start="4037" data-end="4040">
<h2 data-start="4042" data-end="4067">8. Outdated Statistics</h2>
<p data-start="4069" data-end="4139">MySQL relies on internal statistics to decide whether to use an index.</p>
<p data-start="4141" data-end="4145">Run:</p>
<div class="contain-inline-size rounded-2xl corner-superellipse/1.1 relative bg-token-sidebar-surface-primary">
<div class="sticky top-[calc(var(--sticky-padding-top)+9*var(--spacing))]">
<div class="absolute end-0 bottom-0 flex h-9 items-center pe-2">
<div class="bg-token-bg-elevated-secondary text-token-text-secondary flex items-center gap-4 rounded-sm px-2 font-sans text-xs">&nbsp;</div>
</div>
</div>
<div class="overflow-y-auto p-4" dir="ltr"><code class="whitespace-pre! language-sql">ANALYZE <span class="hljs-keyword">TABLE</span> users;
</code></div>
</div>
<p data-start="4180" data-end="4248">This refreshes index statistics and may improve optimizer decisions.</p>
<hr data-start="4250" data-end="4253">
<h2 data-start="4255" data-end="4274">9. Over-Indexing</h2>
<p data-start="4276" data-end="4297">Too many indexes can:</p>
<ul data-start="4299" data-end="4388">
<li data-start="4299" data-end="4339">
<p data-start="4301" data-end="4339">Slow down INSERT and UPDATE operations</p>
</li>
<li data-start="4340" data-end="4358">
<p data-start="4342" data-end="4358">Increase storage</p>
</li>
<li data-start="4359" data-end="4388">
<p data-start="4361" data-end="4388">Confuse optimizer decisions</p>
</li>
</ul>
<p data-start="4390" data-end="4432">Indexes must be strategic &mdash; not excessive.</p>
<hr data-start="4434" data-end="4437">
<h2 data-start="4439" data-end="4483">10. Query Structure Prevents Optimization</h2>
<p data-start="4485" data-end="4506">Complex queries with:</p>
<ul data-start="4508" data-end="4553">
<li data-start="4508" data-end="4523">
<p data-start="4510" data-end="4523">OR conditions</p>
</li>
<li data-start="4524" data-end="4536">
<p data-start="4526" data-end="4536">Subqueries</p>
</li>
<li data-start="4537" data-end="4553">
<p data-start="4539" data-end="4553">Improper joins</p>
</li>
</ul>
<p data-start="4555" data-end="4579">can prevent index usage.</p>
<p data-start="4581" data-end="4589">Example:</p>
<div class="contain-inline-size rounded-2xl corner-superellipse/1.1 relative bg-token-sidebar-surface-primary">
<div class="sticky top-[calc(var(--sticky-padding-top)+9*var(--spacing))]">
<div class="absolute end-0 bottom-0 flex h-9 items-center pe-2">
<div class="bg-token-bg-elevated-secondary text-token-text-secondary flex items-center gap-4 rounded-sm px-2 font-sans text-xs">&nbsp;</div>
</div>
</div>
<div class="overflow-y-auto p-4" dir="ltr"><code class="whitespace-pre! language-sql"><span class="hljs-keyword">WHERE</span> email <span class="hljs-operator">=</span> <span class="hljs-string">'a@example.com'</span> <span class="hljs-keyword">OR</span> status <span class="hljs-operator">=</span> <span class="hljs-string">'active'</span>;
</code></div>
</div>
<p data-start="4655" data-end="4705">MySQL may avoid indexes depending on distribution.</p>
<p data-start="4707" data-end="4779">Refactoring queries often improves performance more than adding indexes.</p>
<hr data-start="4781" data-end="4784">
<h1 data-start="4786" data-end="4825">How to Properly Diagnose Index Issues</h1>
<p data-start="4827" data-end="4849">Follow this checklist:</p>
<ol data-start="4851" data-end="5077">
<li data-start="4851" data-end="4867">
<p data-start="4854" data-end="4867">Use <code data-start="4858" data-end="4867">EXPLAIN</code></p>
</li>
<li data-start="4868" data-end="4895">
<p data-start="4871" data-end="4895">Check column cardinality</p>
</li>
<li data-start="4896" data-end="4942">
<p data-start="4899" data-end="4942">Avoid wrapping indexed columns in functions</p>
</li>
<li data-start="4943" data-end="4972">
<p data-start="4946" data-end="4972">Confirm correct data types</p>
</li>
<li data-start="4973" data-end="5004">
<p data-start="4976" data-end="5004">Review composite index order</p>
</li>
<li data-start="5005" data-end="5031">
<p data-start="5008" data-end="5031">Update table statistics</p>
</li>
<li data-start="5032" data-end="5077">
<p data-start="5035" data-end="5077">Benchmark queries before and after changes</p>
</li>
</ol>
<p data-start="5079" data-end="5136">Database performance tuning is analytical, not guesswork.</p>
<hr data-start="5138" data-end="5141">
<h1 data-start="5143" data-end="5159">Final Thoughts</h1>
<p data-start="5161" data-end="5251">If your MySQL index is not working, the problem is rarely the index itself. It is usually:</p>
<ul data-start="5253" data-end="5370">
<li data-start="5253" data-end="5272">
<p data-start="5255" data-end="5272">Poor query design</p>
</li>
<li data-start="5273" data-end="5290">
<p data-start="5275" data-end="5290">Low selectivity</p>
</li>
<li data-start="5291" data-end="5308">
<p data-start="5293" data-end="5308">Function misuse</p>
</li>
<li data-start="5309" data-end="5343">
<p data-start="5311" data-end="5343">Composite index misunderstanding</p>
</li>
<li data-start="5344" data-end="5370">
<p data-start="5346" data-end="5370">Optimizer cost decisions</p>
</li>
</ul>
<p data-start="5372" data-end="5456">Indexes are powerful &mdash; but only when aligned with query patterns and data structure.</p>
<p data-start="5458" data-end="5596">A senior developer doesn&rsquo;t just &ldquo;add an index.&rdquo;<br data-start="5505" data-end="5508">They analyze workload, study execution plans, and optimize based on real query behavior.</p>
<p data-start="5598" data-end="5701">When you approach indexing strategically, performance improvements become predictable &mdash; not accidental.</p>