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.
I tried:
- 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);