MySQL Insert Performance – MySQL Stuck on INSERT INTO … SELECT

insertMySQL

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);

Best Answer

Give this formulation a try:

INSERT INTO `hlh_record_status_submission`
SELECT rse.* FROM `hlh_record_status_events` AS rse
    JOIN ( SELECT MIN(id) AS min_id
              FROM `hlh_record_status_events`
              WHERE status IN (21,24,31,40)
              GROUP BY record_id
         ) AS x  ON min_Id = rse.id;

and replace INDEX(status) with

INDEX(status, record_id, id)

Also, you should really change all your tables to InnoDB.

The subquery (a "derived" table) will be faster because of the index starting with what is in its WHERE clause and because that index is "covering". The JOINing to the outer part is (I assume) using the PRIMARY KEY(id) making that efficient.

Historically, the construct IN ( SELECT ... ) has been notoriously inefficient, so I try to avoid it. JOIN and EXISTS ( SELECT ... ) are sometimes viable alternatives.

More discussion of building optimal indexes: http://mysql.rjweb.org/doc.php/index_cookbook_mysql

Related Solutions

Mysql – find and insert row to another table using thesql trigger

This should do the trick for you:

DELIMITER $$
DROP TRIGGER IF EXISTS `employee_INSERT` $$
CREATE TRIGGER `employee_INSERT` 
AFTER INSERT ON `employee`
FOR EACH ROW
BEGIN
    INSERT INTO employee_tools (Id, Tool)
    SELECT new.Id, tools.Tool_Name 
            FROM tools
            WHERE tools.Division = new.Division;
END $$
DELIMITER ;

MySQL looking up more rows than needed (indexing issue)

Your indexes are fine for the two types of queries you mentioned.

This query will be satisfied by traversing the clustered index on the primary key...

[...] WHERE participant_id = x AND question_id = y AND given_answer_id = z;

...and this one is satisfied by the index on 'question_id':

[...] WHERE question_id = x;

The output of EXPLAIN SELECT is not telling you what you think it is telling you, because the value shown in rows is an estimate of the number of rows the server will need to consider, not the actual rows it will examine. For InnoDB these are based on index statistics.

rows

The rows column indicates the number of rows MySQL believes it must examine to execute the query.

For InnoDB tables, this number is an estimate, and may not always be exact.

^{— http://dev.mysql.com/doc/refman/5.5/en/explain-output.html#explain_rows}

The optimizer gathers information about different possible query plans, and chooses the one with the lowest cost. The information shown in EXPLAIN is the information the optimizer gathered about the plan it selected.

When type is ref and key is not NULL, this means that the name listed in the key column is the name of the index that the optimizer has chosen to use to find the desired rows, so your query plan looks exactly as it should.

Note, sometimes you will see Using index in the Extra column and a lot of people assume that this means an index is being used, or that no index is being used when that doesn't appear, but that's not correct, either. Using index describes a special case called a "covering index" -- it does not indicate whether an index is being used to locate the rows of interest.

It's possible that running ANALYZE [LOCAL] TABLE would cause the numbers in rows shown by EXPLAIN to differ, but this is a simple query and selecting this index is an obvious choice for the optimizer to make, so ANALYZE TABLE is unlikely to make any actual difference in performance.

It is possible, however, that your overall performance might see some marginal improvement with an occasional OPTIMIZE [LOCAL] TABLE, because you are not inserting rows in primary key order (as would be the case with an auto_increment primary key)... but on large tables this can be time-consuming because it rebuilds a new copy of the table... but, again, I wouldn't expect any significant change.

Best Answer

Related Solutions

Mysql – find and insert row to another table using thesql trigger

MySQL looking up more rows than needed (indexing issue)

Related Question