Join and Multi-Table Query Guidelines

Place indexes on the driven table when using left joins, and on the left table for right joins. Reduce the total iterations within nested-loop joins by always using the smaller result set as the driver. Prioritize optimization of the inner loop, and ensure that the join columns in the driven table are indexed. If indexing the join column on the driven table is impossible and memory permits, allocate a generous join buffer to mitigate performance degradation.

Building a Sample Table and Composite Index

Start with a user table definition that includes a primary key and a composite secondary index.

CREATE TABLE `employee` (
  `id` varchar(64) NOT NULL COMMENT 'Primary key',
  `full_name` varchar(64) DEFAULT NULL COMMENT 'Full name',
  `years` int(64) DEFAULT NULL COMMENT 'Age',
  `role` varchar(64) DEFAULT NULL COMMENT 'Position',
  PRIMARY KEY (`id`),
  KEY `idx_emp_fullname_years_role` (`full_name`,`years`,`role`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Employee information';

Conditions That Disable an Index

Violating the Leftmost Prefix Principle
For a composite index, the query must reference the leftmost column first without skipping any indexed columns. If the leading column is missing, the index will not be used.

-- Adds a composite index on three columns
ALTER TABLE employee ADD INDEX idx_emp_fullname_years_role(full_name, years, role);

Examine how different WHERE clauses affect index usage:

-- Uses all three indexed columns (full index match)
EXPLAIN SELECT * FROM employee WHERE full_name='Alice' AND years = 28 AND role ='engineer';

-- Uses two indexed columns
EXPLAIN SELECT * FROM employee WHERE full_name='Alice' AND years = 28;

-- Uses only the leading column
EXPLAIN SELECT * FROM employee WHERE full_name='Alice' AND role ='engineer';

Omitting the leading column causes a full table scan:

EXPLAIN SELECT * FROM employee WHERE years = 28;
EXPLAIN SELECT * FROM employee WHERE role ='engineer';
EXPLAIN SELECT * FROM employee WHERE years = 28 AND role ='engineer';

Applying Functions, Calculations, or Implicit Type Conversions on Indexed Columns
Operations on an indexed column force a full scan.

EXPLAIN SELECT * FROM employee WHERE LEFT(full_name,1)='A';

Range Conditions Break Access to Subsequent Index Columns
A range filter on years prevents the optimizer from using the index for role.

EXPLAIN SELECT * FROM employee WHERE full_name='Alice' AND years < 30 AND role ='engineer';

The execution plan shifts to a range type, and the index on role is ignored.

Favoring Covering Indexes Over SELECT *
When the query references only the columns present in the index, a covering index can serve the entire query without accessing the table.

-- Accessing all columns forces a table lookup
EXPLAIN SELECT * FROM employee WHERE full_name='Alice' AND years = 28 AND role ='engineer';

-- Using only the indexed columns avoids extra lookups
EXPLAIN SELECT full_name, years, role FROM employee WHERE full_name='Alice' AND years = 28 AND role ='engineer';

Using Not-Equals or Null Checks
Comparisons with != or <>, and tests for IS NULL or IS NOT NULL, usually result in a full scan.

EXPLAIN SELECT * FROM employee WHERE full_name !='Alice';
EXPLAIN SELECT * FROM employee WHERE full_name IS NOT NULL;

Leading Wildcards in LIKE Patterns
Prefix wildcards invalidate the index; trailing wildcards preserve it. If a leading wildcard is unavoidable, a covering index may rescue performance.

-- Index disabled, full scan
EXPLAIN SELECT * FROM employee WHERE full_name LIKE '%ice%';

-- Trailing wildcard, index intact
EXPLAIN SELECT * FROM employee WHERE full_name LIKE 'Ali%';

-- Forced leading wildcard but covered by index columns
EXPLAIN SELECT full_name, years, role FROM employee WHERE full_name LIKE '%ice%';

Omitting Quotes Around String Values
If the column is a string type, failing to quote the literal forces an implicit conversion, which disables the index.

EXPLAIN SELECT * FROM employee WHERE full_name=123;

OR Conditions Without Index Coverage on All Branches
An OR can rander the index useless unless each branch can be served by an index.

EXPLAIN SELECT * FROM employee WHERE full_name='Alice' OR years = 28;

Guidacne for Index Design

Prefer indexes that are highly selective for the query's filter conditions. In a composite index, place the most selective column first. Build indexes that cover as many WHERE clause columns as possible. Use execution statistics and EXPLAIN output to refine both query structure and index choice. For grouping and sorting, remember that GROUP BY often triggers a sort and temporary tables; aligning the index order with the GROUP BY and ORDER BY columns can improve performance.