{"id":342,"date":"2026-10-26T11:20:00","date_gmt":"2026-10-26T11:20:00","guid":{"rendered":"https:\/\/backendside.com\/blog\/?p=342"},"modified":"2026-11-21T11:24:16","modified_gmt":"2026-11-21T11:24:16","slug":"mysql-indexing-explain-slow-queries","status":"publish","type":"post","link":"https:\/\/backendside.com\/blog\/2026\/10\/26\/mysql-indexing-explain-slow-queries\/","title":{"rendered":"Indexing Explained: Why Your Query Is Slow and How EXPLAIN Tells You"},"content":{"rendered":"<p class=\"lead\">A query that took 20 milliseconds when the table had a thousand rows takes eight seconds now that it has five million. The server did not get slower; the query is doing the database equivalent of reading a phone book cover to cover to find one name. Nine times out of ten the fix is an <strong>index<\/strong> &mdash; and the tool that tells you whether one is being used is <strong><code>EXPLAIN<\/code><\/strong>. This guide explains how indexes work in MySQL and MariaDB, how to read an execution plan, and the everyday mistakes that make the server ignore an index you thought you had.<\/p>\n<h2>What an index actually is<\/h2>\n<p>Without an index, finding rows that match <code>WHERE email = 'ayesha@example.com'<\/code> means examining every row in the table: a <strong>full table scan<\/strong>. An index is a separate, sorted structure &mdash; in InnoDB a <strong>B-tree<\/strong> &mdash; that maps column values to the rows that contain them. Because it is sorted, the server can jump to the right place in a handful of steps, like using the index at the back of a book instead of reading every page.<\/p>\n<p>Two InnoDB details are worth knowing:<\/p>\n<ul>\n<li>The <strong>primary key is the table<\/strong>. InnoDB stores rows in primary-key order (a <em>clustered index<\/em>), so lookups by primary key are the fastest possible.<\/li>\n<li>Every <strong>secondary index<\/strong> stores the indexed columns plus the primary key. A lookup through a secondary index finds the primary key first, then fetches the row &mdash; unless the index alone already contains every column the query needs.<\/li>\n<\/ul>\n<h2>Meet EXPLAIN<\/h2>\n<p>Put <code>EXPLAIN<\/code> in front of any <code>SELECT<\/code> (and, in current versions, <code>UPDATE<\/code> and <code>DELETE<\/code>) and the server shows the plan it <em>would<\/em> use, without running the query:<\/p>\n<pre style=\"background:#1e1d1b;color:#eceae6;padding:1rem 1.15rem;border-radius:8px;overflow-x:auto;font-size:.85rem;line-height:1.5;\"><code>EXPLAIN SELECT id, total\nFROM orders\nWHERE customer_id = 4821 AND status = 'paid';\n\n-- id | select_type | table  | type | possible_keys | key  | rows    | Extra\n--  1 | SIMPLE      | orders | ALL  | NULL          | NULL | 4970132 | Using where<\/code><\/pre>\n<p>That output is a red flag on every line: <code>type = ALL<\/code> (full scan), <code>key = NULL<\/code> (no index used) and an estimated five million rows examined to return a handful.<\/p>\n<h3>The columns that matter<\/h3>\n<table>\n<thead>\n<tr>\n<th>Column<\/th>\n<th>What it tells you<\/th>\n<th>What you want to see<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td><code>type<\/code><\/td>\n<td>How rows are accessed<\/td>\n<td><code>const<\/code>, <code>eq_ref<\/code>, <code>ref<\/code> or <code>range<\/code>; worry about <code>index<\/code> and <code>ALL<\/code><\/td>\n<\/tr>\n<tr>\n<td><code>possible_keys<\/code><\/td>\n<td>Indexes the optimizer considered<\/td>\n<td>Not <code>NULL<\/code><\/td>\n<\/tr>\n<tr>\n<td><code>key<\/code><\/td>\n<td>The index actually chosen<\/td>\n<td>The one you expected<\/td>\n<\/tr>\n<tr>\n<td><code>key_len<\/code><\/td>\n<td>How many bytes of the index are used<\/td>\n<td>Long enough to cover all your filtered columns<\/td>\n<\/tr>\n<tr>\n<td><code>rows<\/code><\/td>\n<td>Estimated rows examined<\/td>\n<td>Close to the number of rows returned<\/td>\n<\/tr>\n<tr>\n<td><code>Extra<\/code><\/td>\n<td>Additional work<\/td>\n<td><code>Using index<\/code> is great; <code>Using filesort<\/code> and <code>Using temporary<\/code> deserve a look<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>The <code>type<\/code> values from best to worst are roughly: <code>system<\/code>\/<code>const<\/code> (one row via primary or unique key) &rarr; <code>eq_ref<\/code> (one row per join row) &rarr; <code>ref<\/code> (several rows via a non-unique index) &rarr; <code>range<\/code> (an index range such as <code>BETWEEN<\/code> or <code>&gt;<\/code>) &rarr; <code>index<\/code> (scanning the whole index) &rarr; <code>ALL<\/code> (scanning the whole table).<\/p>\n<h2>Fixing it with a composite index<\/h2>\n<p>The query filters on <code>customer_id<\/code> and <code>status<\/code>, so index both, in that order:<\/p>\n<pre style=\"background:#1e1d1b;color:#eceae6;padding:1rem 1.15rem;border-radius:8px;overflow-x:auto;font-size:.85rem;line-height:1.5;\"><code>CREATE INDEX idx_orders_customer_status\n  ON orders (customer_id, status);\n\nEXPLAIN SELECT id, total\nFROM orders\nWHERE customer_id = 4821 AND status = 'paid';\n\n-- type | key                        | rows | Extra\n-- ref  | idx_orders_customer_status |   12 | NULL<\/code><\/pre>\n<p>From five million rows examined to twelve. That is the whole game.<\/p>\n<h3>Column order: the leftmost-prefix rule<\/h3>\n<p>A composite index on <code>(a, b, c)<\/code> is sorted by <code>a<\/code>, then <code>b<\/code> within each <code>a<\/code>, then <code>c<\/code>. It can serve filters on <code>a<\/code>, on <code>a, b<\/code> and on <code>a, b, c<\/code> &mdash; but not on <code>b<\/code> alone or <code>c<\/code> alone, just as a phone book sorted by surname then first name cannot find &ldquo;everyone called Sara&rdquo;. Practical guidance:<\/p>\n<ul>\n<li>Put columns tested with <strong>equality<\/strong> (<code>=<\/code>, <code>IN<\/code>) first.<\/li>\n<li>Put a <strong>range<\/strong> column (<code>&gt;<\/code>, <code>BETWEEN<\/code>, <code>LIKE 'abc%'<\/code>) last &mdash; columns after a range condition cannot be used for filtering.<\/li>\n<li>If the query also sorts, an index whose order matches the <code>ORDER BY<\/code> after the equality columns can remove <code>Using filesort<\/code> entirely.<\/li>\n<\/ul>\n<h3>Covering indexes<\/h3>\n<p>If an index contains <em>every<\/em> column a query reads, the server never touches the table rows at all, and <code>Extra<\/code> shows <code>Using index<\/code>. Adding <code>total<\/code> to the index above &mdash; <code>(customer_id, status, total)<\/code> &mdash; would make our query covering. Use this for hot queries, not as a habit: wider indexes cost disk and write time.<\/p>\n<div style=\"border:1px solid #c5d3f8;background:linear-gradient(135deg,#eef2fd 0%,#ffffff 72%);border-radius:14px;padding:1.5rem 1.65rem;margin:2rem 0;\">\n<div style=\"font-size:.7rem;font-weight:700;letter-spacing:.08em;text-transform:uppercase;color:#2d5be3;margin-bottom:.55rem;\">&#128295; BackendSide Tool<\/div>\n<h4 style=\"margin:0 0 .45rem;font-size:1.15rem;color:#1a1916;font-weight:700;\">MyDBDesk &mdash; See Every Index a Table Already Has<\/h4>\n<p style=\"margin:0 0 1.05rem;color:#3d3c38;font-size:.92rem;line-height:1.65;\">Before adding an index, check what is there. In <strong>MyDBDesk<\/strong>, a table&rsquo;s <strong>Structure<\/strong> tab lists its columns, indexes and foreign keys alongside the full table definition, so a missing or duplicate index is obvious at a glance. Then run <code>EXPLAIN<\/code> in the <strong>SQL console<\/strong> &mdash; select just that line and run the selection &mdash; and every statement you try is kept in the history drop-down across sessions.<\/p>\n<p>  <a href=\"https:\/\/backendside.com\/mydbdesk.php\" style=\"display:inline-flex;align-items:center;gap:.4rem;background:#2d5be3;color:#ffffff;font-weight:600;font-size:.85rem;padding:.6rem 1.2rem;border-radius:6px;text-decoration:none;\">Explore MyDBDesk &rarr;<\/a>\n<\/div>\n<h2>Six ways to accidentally switch an index off<\/h2>\n<p>The most frustrating case is an index that exists but is ignored. These are the usual culprits:<\/p>\n<ol>\n<li><strong>Wrapping the column in a function.<\/strong> <code>WHERE YEAR(created_at) = 2026<\/code> cannot use an index on <code>created_at<\/code>. Rewrite it as a range: <code>WHERE created_at &gt;= '2026-01-01' AND created_at &lt; '2027-01-01'<\/code>.<\/li>\n<li><strong>A leading wildcard.<\/strong> <code>LIKE '%smith'<\/code> cannot use a B-tree index; <code>LIKE 'smith%'<\/code> can. For real text search use a <code>FULLTEXT<\/code> index.<\/li>\n<li><strong>Type mismatches.<\/strong> Comparing a <code>VARCHAR<\/code> column to a number (<code>WHERE phone = 5551234<\/code>) forces a conversion on every row. Quote the value.<\/li>\n<li><strong>Skipping the leftmost column<\/strong> of a composite index, as described above.<\/li>\n<li><strong><code>OR<\/code> across different columns.<\/strong> <code>WHERE a = 1 OR b = 2<\/code> often falls back to a scan; a <code>UNION<\/code> of two indexed queries can be faster.<\/li>\n<li><strong>Low selectivity.<\/strong> If a condition matches a large share of the table (say <code>status = 'active'<\/code> on 90% of rows), the optimizer may rightly decide a scan is cheaper.<\/li>\n<\/ol>\n<h2>Going deeper: EXPLAIN ANALYZE and FORMAT options<\/h2>\n<p><code>EXPLAIN<\/code> shows estimates. To see what really happened, run the query with timing:<\/p>\n<pre style=\"background:#1e1d1b;color:#eceae6;padding:1rem 1.15rem;border-radius:8px;overflow-x:auto;font-size:.85rem;line-height:1.5;\"><code>-- MySQL 8.0.18+ : runs the query and reports actual rows and time per step\nEXPLAIN ANALYZE SELECT ...;\n\n-- MySQL : a readable tree of the plan, without running it\nEXPLAIN FORMAT=TREE SELECT ...;\n\n-- MariaDB : run the query and show estimated vs actual rows\nANALYZE SELECT ...;\nANALYZE FORMAT=JSON SELECT ...;<\/code><\/pre>\n<p>Note the dialect difference: MySQL spells it <code>EXPLAIN ANALYZE<\/code>, MariaDB uses a separate <code>ANALYZE<\/code> statement. Both actually execute the query, so be careful with slow or data-changing statements on production.<\/p>\n<p>When estimates are wildly off from reality, the table statistics may be stale. <code>ANALYZE TABLE orders;<\/code> refreshes them and is cheap on InnoDB.<\/p>\n<h2>Finding the slow queries in the first place<\/h2>\n<p>You cannot tune what you have not found. Turn on the <strong>slow query log<\/strong> to capture every statement above a threshold:<\/p>\n<pre style=\"background:#1e1d1b;color:#eceae6;padding:1rem 1.15rem;border-radius:8px;overflow-x:auto;font-size:.85rem;line-height:1.5;\"><code>SET GLOBAL slow_query_log = ON;\nSET GLOBAL long_query_time = 1;        -- seconds\nSET GLOBAL log_queries_not_using_indexes = ON;   -- optional, can be noisy\n\nSHOW VARIABLES LIKE 'slow_query_log_file';<\/code><\/pre>\n<p>Make the settings permanent in the server configuration file once you are happy with them. Then run <code>EXPLAIN<\/code> on the worst offenders, one at a time.<\/p>\n<div style=\"border:1px solid #c5d3f8;background:linear-gradient(135deg,#eef2fd 0%,#ffffff 72%);border-radius:14px;padding:1.5rem 1.65rem;margin:2rem 0;\">\n<div style=\"font-size:.7rem;font-weight:700;letter-spacing:.08em;text-transform:uppercase;color:#2d5be3;margin-bottom:.55rem;\">&#128295; BackendSide Tool<\/div>\n<h4 style=\"margin:0 0 .45rem;font-size:1.15rem;color:#1a1916;font-weight:700;\">MyDBDesk &mdash; Catch the Slow Query While It Is Running<\/h4>\n<p style=\"margin:0 0 1.05rem;color:#3d3c38;font-size:.92rem;line-height:1.65;\">When the server is crawling right now, <strong>MyDBDesk<\/strong>&rsquo;s <strong>Server Status<\/strong> view shows the live process list with running connections and queries, with a filter box and the ability to end a connection right from the list. Paste the offender into the SQL console, stop it at any time if it runs long, and ask the private, on-device <strong>AI assistant<\/strong> to <em>explain the query in the editor<\/em> in plain English before you start tuning it.<\/p>\n<p>  <a href=\"https:\/\/backendside.com\/mydbdesk.php\" style=\"display:inline-flex;align-items:center;gap:.4rem;background:#2d5be3;color:#ffffff;font-weight:600;font-size:.85rem;padding:.6rem 1.2rem;border-radius:6px;text-decoration:none;\">Explore MyDBDesk &rarr;<\/a>\n<\/div>\n<h2>Do not index everything<\/h2>\n<p>Indexes are not free. Every <code>INSERT<\/code>, <code>UPDATE<\/code> and <code>DELETE<\/code> must update each index on the table, and each one takes disk space and memory in the buffer pool. Signs you have gone too far: write-heavy tables with a dozen indexes, indexes that duplicate the leftmost columns of another (an index on <code>(a)<\/code> is redundant if <code>(a, b)<\/code> exists), and indexes no query uses.<\/p>\n<p>Before dropping a suspect index, hide it and watch for regressions:<\/p>\n<pre style=\"background:#1e1d1b;color:#eceae6;padding:1rem 1.15rem;border-radius:8px;overflow-x:auto;font-size:.85rem;line-height:1.5;\"><code>-- MySQL 8+\nALTER TABLE orders ALTER INDEX idx_old INVISIBLE;\n\n-- MariaDB 10.6+\nALTER TABLE orders ALTER INDEX idx_old IGNORED;<\/code><\/pre>\n<p>The optimizer stops using it, but it is still maintained, so turning it back on is instant. If nothing gets slower after a week, drop it.<\/p>\n<h2>Key takeaways<\/h2>\n<ul>\n<li>Most slow queries are <strong>full table scans<\/strong>; an index lets the server jump straight to matching rows.<\/li>\n<li>Run <strong><code>EXPLAIN<\/code><\/strong> first: watch <code>type<\/code>, <code>key<\/code>, <code>rows<\/code> and <code>Extra<\/code>. <code>ALL<\/code> with <code>key = NULL<\/code> on a big table is the classic problem.<\/li>\n<li>Build <strong>composite indexes<\/strong> with equality columns first and range columns last; remember the leftmost-prefix rule.<\/li>\n<li>Avoid <strong>functions on indexed columns, leading wildcards and type mismatches<\/strong> &mdash; they quietly disable indexes.<\/li>\n<li>Use <strong><code>EXPLAIN ANALYZE<\/code><\/strong> (MySQL) or <strong><code>ANALYZE<\/code><\/strong> (MariaDB) for real timings, and the <strong>slow query log<\/strong> to find what to fix.<\/li>\n<li>Indexes cost writes and space: <strong>hide before you drop<\/strong>, and remove redundant ones.<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>Most slow MySQL and MariaDB queries are not slow because the server is weak; they are slow because there is no index the query can use. Learn how B-tree indexes work, how to read EXPLAIN output, and the common mistakes that silently switch indexes off.<\/p>\n","protected":false},"author":1,"featured_media":407,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[8,2],"tags":[27,24,25,23],"class_list":["post-342","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-tutorials","category-windows","tag-indexing","tag-mariadb","tag-mydbdesk","tag-mysql"],"_links":{"self":[{"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/posts\/342","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/comments?post=342"}],"version-history":[{"count":1,"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/posts\/342\/revisions"}],"predecessor-version":[{"id":408,"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/posts\/342\/revisions\/408"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/media\/407"}],"wp:attachment":[{"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/media?parent=342"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/categories?post=342"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/tags?post=342"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}