Summary
When using the database tool to inspect a table, composite indexes are displayed correctly in the UI. However, when right-clicking the index and selecting "Generate SQL", the generated statement is split into multiple single-column CREATE INDEX statements instead of one composite index definition.
Environment
Example
Assume a table has this composite index:
CREATE INDEX `idx_orders_lookup`
ON `orders` (
`customer_id`,
`status`,
`created_at`,
`id`
);
The index appears correctly in the database/index view as one multi-column index.
But "Generate SQL" produces something like:
CREATE INDEX `idx_orders_lookup` ON `orders` (`customer_id`);
CREATE INDEX `idx_orders_lookup` ON `orders` (`status`);
CREATE INDEX `idx_orders_lookup` ON `orders` (`created_at`);
CREATE INDEX `idx_orders_lookup` ON `orders` (`id`);
Expected behavior
The generated SQL should preserve the composite index as a single CREATE INDEX statement:
CREATE INDEX `idx_orders_lookup`
ON `orders` (
`customer_id`,
`status`,
`created_at`,
`id`
);
Actual behavior
The generated SQL creates one CREATE INDEX statement per indexed column.
This changes the meaning of the index. Four single-column indexes are not equivalent to one composite index, especially for queries that rely on the left-prefix order of the composite index.
It can also generate invalid SQL on databases that require index names to be unique per table, because each generated statement reuses the same index name.
Why this matters
A composite index such as:
(customer_id, status, created_at, id)
can support queries like:
WHERE customer_id = ?
AND status = ?
AND created_at >= ?
ORDER BY id
Splitting it into separate single-column indexes changes query planning and can cause significantly worse performance.
Suggested fix
When generating SQL for an index, group all columns that belong to the same index definition and emit one CREATE INDEX statement with the columns in their original index order.
Summary
When using the database tool to inspect a table, composite indexes are displayed correctly in the UI. However, when right-clicking the index and selecting "Generate SQL", the generated statement is split into multiple single-column
CREATE INDEXstatements instead of one composite index definition.Environment
Example
Assume a table has this composite index:
The index appears correctly in the database/index view as one multi-column index.
But "Generate SQL" produces something like:
Expected behavior
The generated SQL should preserve the composite index as a single
CREATE INDEXstatement:Actual behavior
The generated SQL creates one
CREATE INDEXstatement per indexed column.This changes the meaning of the index. Four single-column indexes are not equivalent to one composite index, especially for queries that rely on the left-prefix order of the composite index.
It can also generate invalid SQL on databases that require index names to be unique per table, because each generated statement reuses the same index name.
Why this matters
A composite index such as:
can support queries like:
Splitting it into separate single-column indexes changes query planning and can cause significantly worse performance.
Suggested fix
When generating SQL for an index, group all columns that belong to the same index definition and emit one
CREATE INDEXstatement with the columns in their original index order.