$ guides / mysql / mysql-select-full-join ▌

Operations Guides

MySQL Select_full_join > 0: joins running without an index

Select_full_join is climbing. In an OLTP system, this counter should stay at zero. Any sustained nonzero rate means at least one query is executing a join without a usable index, effectively performing a full cross-product scan on the joined table. The damage scales with table size. A single unindexed join against a million-row table can stall an otherwise healthy instance.

This is not a buffer pool problem or a connection storm. It is a query correctness issue. The optimizer cannot find an index to resolve the join predicate, so it reads every row of the referenced table for each row of the driving table. In MySQL 8.0.20 and later, the execution engine uses hash join for these cases (block nested loop was removed), but the underlying pathology remains identical: missing index, catastrophic read amplification.

Because Select_full_join is cumulative, a point-in-time value is less useful than its rate of change. You need to know whether the counter is increasing steadily or whether it spiked once and stopped.

What this means

Select_full_join increments every time MySQL executes a join that uses no index on the joined table. This is distinct from Select_scan, which counts full scans of the first table in a join. Select_full_join specifically targets the referenced table in a join operation: the right side of a JOIN clause, or the inner table in nested-loop terms.

The counter exists at both global and session scope. SHOW GLOBAL STATUS returns the server-wide count since startup. SHOW SESSION STATUS returns the count for the current connection since the session began or since the last FLUSH STATUS. For incident response, watch the global rate.

A nonzero rate correlates with rising Slow_queries, rising Threads_running, and increasing Handler_read_rnd_next. The unindexed join may also force temporary table materialization, which shows up as Created_tmp_disk_tables. Do not treat join_buffer_size as the fix. Increasing it only allocates more per-connection memory to hide the scan. The real fix is the missing index.

Common causes

Cause	What it looks like	First thing to check
Missing index on join column	Counter starts climbing after data growth or a new feature release	SHOW CREATE TABLE on the joined table
Implicit type conversion	Query text looks correct but the optimizer skips the index	EXPLAIN output for type mismatches between join columns
Index dropped during schema migration	Select_full_join begins increasing immediately after a deployment	Schema diff against the previous release
ORM or query builder missing join predicate	Small, steady increments tied to application traffic patterns	performance_schema.events_statements_current for recent JOIN statements

Quick checks

These commands are read-only and safe to run on a production instance.

-- Check Select_full_join and related counters
SHOW GLOBAL STATUS WHERE Variable_name IN (
  'Select_full_join', 'Select_scan',
  'Select_full_range_join', 'Select_range_check'
);

-- Check slow query log readiness
SHOW GLOBAL VARIABLES LIKE 'log_queries_not_using_indexes';
SHOW GLOBAL VARIABLES LIKE 'slow_query_log';
SHOW GLOBAL VARIABLES LIKE 'long_query_time';

-- Check join buffer and connection memory risk
SHOW GLOBAL VARIABLES WHERE Variable_name IN (
  'join_buffer_size', 'max_connections'
);

-- Find long-running transactions that may compound the issue
SELECT trx_mysql_thread_id, trx_started, trx_query
FROM information_schema.INNODB_TRX
ORDER BY trx_started ASC LIMIT 5;

-- Sample currently running statements containing joins
SELECT THREAD_ID, SQL_TEXT, TIMER_WAIT/1000000000 AS wait_ms
FROM performance_schema.events_statements_current
WHERE SQL_TEXT LIKE '%JOIN%'
ORDER BY TIMER_WAIT DESC LIMIT 10;

# If slow log is enabled and you have file access, grep for full joins
grep -i "Full_join: Yes" /var/lib/mysql/slow.log | tail -20

How to diagnose it

Confirm the rate is sustained, not a transient blip.
Sample SHOW GLOBAL STATUS LIKE ‘Select_full_join’; twice over a 60-second window. If the delta is zero, the incident may have passed. If the delta is rising, proceed.
Correlate with Slow_queries and Threads_running.
Rising Select_full_join alongside rising Slow_queries and Threads_running confirms the bad joins are active and user-facing. If Threads_running is high but Select_full_join is flat, the bottleneck is elsewhere.
Enable log_queries_not_using_indexes temporarily to capture the query.
If it is not already enabled, run SET GLOBAL log_queries_not_using_indexes = 1;. This can generate significant log volume. Use log_throttle_queries_not_using_indexes to cap per-minute slow log output if the workload is heavy. Review the slow query log for lines annotated with # Full_join: Yes.
Run EXPLAIN on the captured query.
Look for Using join buffer (hash join) on a large table and verify whether the join column appears in the key column. If possible_keys is NULL and key is NULL, the optimizer found no usable index.
Inspect the schema for index presence and type alignment.
Run SHOW CREATE TABLE on the joined table. Verify an index exists on the exact column used in the join predicate. Check that both sides of the join use identical types. Implicit conversion (for example, VARCHAR compared to INT) silently prevents index use.
Assess the blast radius.
Check the ratio of Innodb_rows_read to Com_select. If this ratio has jumped by an order of magnitude, the unindexed join is scanning a significant fraction of the table. Check Created_tmp_disk_tables to see if the join is also spilling to disk.

flowchart TD
    A[Select_full_join > 0] --> B[Sample delta over 60s]
    B -->|Delta = 0| C[Transient. Return to baseline monitoring.]
    B -->|Delta > 0| D[Check Slow_queries and Threads_running]
    D --> E[Enable log_queries_not_using_indexes]
    E --> F[Find Full_join: Yes in slow log]
    F --> G[EXPLAIN join plan]
    G -->|Missing index| H[Add index on join column]
    G -->|Type mismatch| I[Align column types]
    G -->|No predicate| J[Fix query or ORM]

Metrics and signals to monitor

Signal	Why it matters	Warning sign
Select_full_join rate	Direct measure of cartesian-style joins	Sustained nonzero rate in OLTP
Slow_queries rate	Confirms the joins are hurting users	Rate increasing in step with Select_full_join
Handler_read_rnd_next rate	Tracks full table scan row reads	Step-change above baseline
Threads_running	Shows concurrency pressure from long joins	Rising while Questions rate drops
Innodb_rows_read / Com_select ratio	Efficiency of row access	Ratio climbing significantly
Created_tmp_disk_tables rate	Unindexed joins often spill to temp tables	Sustained increase correlating with full joins

Fixes

Add the missing index

This is the correct fix. Identify the join column on the referenced table and create an index on it. If the join uses multiple columns, use a composite index matching the predicate order. After adding the index, verify with EXPLAIN that the optimizer selects it.

Adding an index to a large table increases I/O and can stall writes. Plan this during a low-traffic window or use an online schema change tool if the table is hot.

Kill the offending query for immediate relief

If a single long-running query is dominating the counter and you cannot deploy an index immediately, use KILL <thread_id>; on the connection. WARNING: This terminates the query and returns an error to the client. Use it to protect the rest of the workload. Find the thread ID from SHOW PROCESSLIST or information_schema.INNODB_TRX.

Do not increase join_buffer_size

Raising join_buffer_size is a band-aid, not a fix. The buffer is allocated per connection, so raising it multiplies memory consumption by max_connections. With max_connections set to 200 and join_buffer_size increased to 1 MB, MySQL can allocate up to 200 MB just for join buffers. Additionally, on MySQL 5.5 and 5.6 a join_buffer_size of 8 MB or larger can trigger Bug #68899, causing the optimizer to skip available spatial indexes on joined tables and fall back to a join-buffer plan (hash join on MySQL 8.0+). The bug, reported and verified against MySQL 5.5.30 and 5.6.10, has no published fix.

Fix implicit type conversion

If the join column is indexed but the optimizer ignores it, check for implicit type conversion. Ensure the columns on both sides of the predicate use identical types, lengths, and collations. Casting in the query (CAST(… AS …)) or schema alignment resolves this.

Fix ORM or query builder output

If the query originates from an ORM, verify the generated SQL includes a proper ON clause with indexed columns. Missing or dynamically generated predicates can produce unindexed joins that look correct at the application layer.

Prevention

Require index verification for schema changes. Any new foreign key relationship or join introduced in application code should ship with a corresponding index on the joined column.
Enable log_queries_not_using_indexes in pre-production. Catch unindexed joins in staging before they reach production. Use log_throttle_queries_not_using_indexes to prevent log flooding.
Monitor Select_full_join as a rate, not a point value. Alert on any sustained increase above your established baseline. For OLTP, the baseline should be zero.
Review deployment diffs for dropped indexes. An index removed during a migration is a common root cause of sudden Select_full_join spikes.

How Netdata helps

Netdata charts Select_full_join as a per-second rate, surfacing sustained increases that cumulative counters hide.
It correlates Select_full_join with Slow_queries, Handler_read_rnd_next, and Threads_running on the same dashboard, so you can confirm whether the join scans are the primary driver of load.
Alerts can fire when Select_full_join rises above a baseline threshold, catching the regression before connection pools saturate.
It tracks Innodb_rows_read and Com_select together, surfacing row-scan efficiency drops that often precede full-join detection.

The Netdata solution

MySQL monitoring with Netdata

Netdata monitors MySQL and MariaDB with per-second metrics and ML anomaly detection. Track connection usage, query throughput, slow queries, redo-log pressure, and replication lag alongside the host and storage signals that explain them.

See MySQL monitoring → Start monitoring free