Skip to main content
added 457 characters in body
Source Link
ypercubeᵀᴹ
  • 98.6k
  • 13
  • 215
  • 305

With InnoDB tables, all secondary indexes include the PK columns of the clustered index (appendedwhich is the primary key), appended in the end). So your unique index has actually 4 columns, the 3 you have defined plus the 1 primary key column.

When running a query that needs a full table scan, both indexes have all the data needed, so the optimizer is free to choose any one of the two indexes. It's just coincidence that it choseIn fact, as explained in InnoDB Table and Index Structures, the clustered index contains some additional information that costs 13 more bytes per row:

Records in the clustered index contain fields for all user-defined columns. In addition, there is a 6-byte transaction ID field and a 7-byte roll pointer field.

This makes your unique index this time. It may choseless wide than the primary key next time. It doesn't make a differenceindex, which is why it is chosen by the optimizer.

With InnoDB tables, all secondary indexes include the PK columns (appended in the end). So your unique index has actually 4 columns, the 3 you have defined plus the 1 primary key column.

When running a query that needs a full table scan, both indexes have all the data needed, so the optimizer is free to choose any one of the two indexes. It's just coincidence that it chose that index this time. It may chose the primary key next time. It doesn't make a difference.

With InnoDB tables, all secondary indexes include the columns of the clustered index (which is the primary key), appended in the end. So your unique index has actually 4 columns, the 3 you have defined plus the 1 primary key column.

When running a query that needs a full table scan, both indexes have all the data needed, so the optimizer is free to choose any one of the two indexes. In fact, as explained in InnoDB Table and Index Structures, the clustered index contains some additional information that costs 13 more bytes per row:

Records in the clustered index contain fields for all user-defined columns. In addition, there is a 6-byte transaction ID field and a 7-byte roll pointer field.

This makes your unique index less wide than the primary key index, which is why it is chosen by the optimizer.

Source Link
ypercubeᵀᴹ
  • 98.6k
  • 13
  • 215
  • 305

With InnoDB tables, all secondary indexes include the PK columns (appended in the end). So your unique index has actually 4 columns, the 3 you have defined plus the 1 primary key column.

When running a query that needs a full table scan, both indexes have all the data needed, so the optimizer is free to choose any one of the two indexes. It's just coincidence that it chose that index this time. It may chose the primary key next time. It doesn't make a difference.