SQL FORCE

What is the SQL FORCE keyword used for?

FORCE is a modifier or hint that tells the database to override its default optimizer or DDL behavior, such as mandating an index, preserving join order, or rebuilding a table.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL FORCE: MySQL, MariaDB, SQL Server, Oracle. Not supported in PostgreSQL or SQLite.

SQL FORCE Full Explanation

FORCE is not part of the ANSI-SQL standard but appears in several dialects as a performance-tuning hint or DDL option. In MySQL and MariaDB, FORCE INDEX (or FORCE) can be added to SELECT/UPDATE/DELETE statements to require the optimizer to use one or more specified indexes. ALTER TABLE ... FORCE rebuilds a table and its indexes, similar to OPTIMIZE TABLE, which can reclaim space or defragment storage.In SQL Server, the OPTION (FORCE ORDER) query hint prevents the optimizer from reordering joins, while other hints such as FORCESEEK and FORCE SCAN push the engine toward a particular access method. Oracle uses the keyword in ALTER TABLE ... ENABLE VALIDATE CONSTRAINT ... FORCE to enable a disabled constraint that might otherwise fail.Because FORCE bypasses cost-based choices, use it only after profiling queries and ensuring the change improves performance. Over-using FORCE can backfire when data distributions or table sizes change.

SQL FORCE Syntax

-- MySQL / MariaDB query hint
SELECT column_list
FROM table_name
FORCE INDEX (idx_name [, idx_name2])
WHERE condition;

-- MySQL table rebuild
ALTER TABLE table_name FORCE;

-- SQL Server join-order hint
SELECT columns
FROM t1 JOIN t2 ON ...
OPTION (FORCE ORDER);

SQL FORCE Parameters

Example Queries Using SQL FORCE

-- Require MySQL to read via a composite index
SELECT *
FROM employees FORCE INDEX (idx_dept_status)
WHERE dept_id = 17 AND status = 'ACTIVE';

-- Rebuild a MySQL table to reclaim space
ALTER TABLE employees FORCE;

-- Keep join order in SQL Server
SELECT *
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
INNER JOIN regions r ON c.region_id = r.id
OPTION (FORCE ORDER);

Expected Output Using SQL FORCE

The optimizer follows the mandated hint: the first query uses idx_dept_status, the second rebuilds the employees table and its indexes, and the third query in SQL Server executes joins in the listed order

Use Cases with SQL FORCE

Ensuring a specific composite index is chosen when the optimizer prefers a less efficient plan.
Rebuilding InnoDB tables after heavy DELETE operations to shrink the physical file size.
Forcing a join order in SQL Server for complex snowflake schemas when the cost-based plan is sub-optimal.

Common Mistakes with SQL FORCE

Assuming FORCE will always improve performance without testing.
Using FORCE in unsupported dialects such as PostgreSQL, which will raise a syntax error.
Misspelling the index name inside FORCE INDEX, causing the optimizer to ignore the hint.
Forgetting that ALTER TABLE ... FORCE locks the table for the duration of the rebuild, potentially causing downtime.

First Introduced In

MySQL 4.0 (FORCE INDEX)

Frequently Asked Questions

What performance benefit can I expect from FORCE INDEX?

If the forced index better matches the filter conditions or provides a covering index, queries can speed up by orders of magnitude. Always benchmark both plans.

When should I rebuild a table with ALTER TABLE ... FORCE instead of OPTIMIZE TABLE?

Use ALTER TABLE ... FORCE when OPTIMIZE TABLE is blocked, when you want to change the row format implicitly, or when you suspect corruption in the .ibd file.

Can I combine FORCE INDEX with USE INDEX in the same query?

No. Only one of USE INDEX, IGNORE INDEX, or FORCE INDEX may appear in the table hint list.

Will future MySQL versions ignore FORCE INDEX hints?

Hints are honored as long as the referenced index exists. However, the optimizer team may deprecate a hint in later releases, so review release notes before upgrading.