I have a problem with my database. I have a production server running MySQL 5.5.33 and a local test server running MySQL 5.5.5. The only other difference I can think of is that the prod DB is being replicated
They both run the same exact SQL databases (local is a replica of the prod environment).
I need to create a materialized view in MySQL which will contain a subset of the record_status_events table. Below you will find the query that does this. This same exact query works in under a second on local, but halts on prod. It gets stuck in the "Sending data" status. If I export the data and import it manually it works fine. It is just the INSERT INTO ... SELECT combo causing the problems.
The underlying query I am trying to insert runs under 150ms and has 50k entries.
- Changing the DB engine
- Wrapping it in a transaction
- Removing all indexes
- Removing the primary key
- Using the auto increment and ignoring the key from the source table
DROP TABLE IF EXISTS `hlh_record_status_submission`; CREATE TABLE `hlh_record_status_submission` ( `id` int(10) unsigned NOT NULL, `user_id` int(10) unsigned NOT NULL, `record_id` int(10) unsigned NOT NULL, `status` tinyint(3) unsigned NOT NULL, `created_at` datetime NOT NULL, PRIMARY KEY (`id`), KEY `Created At` (`created_at`), KEY `status` (`status`), ) ENGINE=MyISAM DEFAULT CHARSET=utf8; INSERT INTO `hlh_record_status_submission` SELECT * FROM `hlh_record_status_events` WHERE id IN (SELECT MIN(id) FROM `hlh_record_status_events` WHERE status IN (21,24,31,40) GROUP BY record_id);