Our situation is as follows:
- We've got 1 MySQL (5.5.44) database server.
- The server hosts 2 databases.
- 1 database is the production database.
- The other is a copy database that gets copied from production every night.
Whenever we apply changes to the database, we first run it on the copy database and then on the production database.
But something has gone very wrong earlier this week. We applied this script:
SET FOREIGN_KEY_CHECKS=0; CREATE TABLE users_copy LIKE users; ALTER TABLE users_copy ADD COLUMN sample INT(11); INSERT INTO users_copy (id, name) (SELECT id, name FROM users); DROP TABLE users; RENAME TABLE users_copy TO users; SET FOREIGN_KEY_CHECKS=1;
This was a mistake. CREATE TABLE ... LIKE does not copy foreign keys, so our new users table lacked all foreign keys. We then did basically the same trick:
SET FOREIGN_KEY_CHECKS=0; CREATE TABLE users_copy LIKE users; ALTER TABLE users_copy ADD FOREIGN KEY company_id etc; INSERT INTO users_copy (SELECT * FROM users); DROP TABLE users; RENAME TABLE users_copy TO users; SET FOREIGN_KEY_CHECKS=1;
The table looked perfect. All indexes and foreign keys were there. We ran both scripts on both databases.
However: the production database doesn't seem to be using the foreign keys.
When we run, for example, this query ...
SELECT * FROM users u INNER JOIN companies c ON c.id = u.company_id;
... the query plans on both databases look completely different. Both tables have around 2 million records, and on the production database it joins on all those 2 million records, on the copy database it just takes 1 row as it should do.
At that point we realised that setting FOREIGN_KEY_CHECKS to 0 while inserting may have been (yet another) mistake, so we ran this script on both databases:
CREATE TABLE users_copy LIKE users; ALTER TABLE users_copy ADD FOREIGN KEY company_id etc; INSERT INTO users_copy (SELECT * FROM users); SET FOREIGN_KEY_CHECKS=0; DROP TABLE users; SET FOREIGN_KEY_CHECKS=1; RENAME TABLE users_copy TO users;
Still no relief. The copy database runs smoothly, the production database doesn't. What makes it slightly more strange, is:
- Yesterday's production database did not perform, but today's copy database does. Those databases should be identical!
To sum it up: - 2 databases on the same server. - Exact same schema. - Exact same keys and indexes. - 2 completely different query plans for the same queries.
We ran ANALYZE TABLE on all applicable tables, but still nothing. Our service provider tells us that MySQL distrusts the indexes on the production database but trusts them on the copy database. They don't know how we can get the production database to 'retrust' the indexes.
What can we do to resolve this issue? Where do we look for internal differences? How do we rebuild the indexes?