A message from our CEO about the future of Stack Overflow and Stack Exchange. Read now.

Hot answers tagged

2

As the manual says (emphasis mine): MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically ...


1

Add INDEX(last_error_code), then do SELECT COUNT(*) FROM logos, it will run a lot faster because it will use the much smaller BTree for that secondary index. SHOW TABLE STATUS LIKE 'logos' will give you an approximate count. Why is the PK bloated? InnoDB orders the data by the PRIMARY KEY; hence the PK, as an INDEX is essentially included in the data. So ...


1

FK is a constraint. To facilitate checking that constraint, an INDEX is created. Run SHOW CREATE TABLE for confirmation. An InnoDB INDEX is expanded to include PK column(s). Also, if there is an index with the leftmost column(s) matching the FK's column(s), the FK is happy. When doing a SELECT, the Optimizer has many possible ways to do the query. If ...


Only top voted, non community-wiki answers of a minimum length are eligible