Search code examples
phpmysqlperformanceinnodbmysql-slow-query-log

MySQL's EXPLAIN INSERT unavailable: how to understand if INSERT is slow or not?

Initially I'm trying to find slow queries among my scripts. And one of the things that bothering me is INSERT queries as I have couple of indexes in the table (as I can understand insert will not be instantaneous in this case as the indexes should be recalculated each time).

My hoster company (siteground) is limiting me to access to MySQL logs to investigate real situation with slow queries. So I need to search any bypass way to find the problems.

Now I am simply executing suspicious query in phpmyadmin and check if the time taken by the query is OK or not.

However I see some weak points of this approach:

  • I will get execution time basing on the current server load. So if the server was busy at the time I will get overstated time.

  • If the query was cached I will get underestimated time.

  • I need to change DB data for real to check the query. It is OK for queries like SELECT, but it is getting tricky for DELETE and UPDATE.

So it is much better to use another approach. EXPLAIN can be a good solution unless:

  • EXPLAIN will work only for SELECT query on my DB server (I have version 5.5.32 of MySQL server, citation from the DOCs: Before MySQL 5.6.3, SELECT is the only explainable statement). I could EXPLAIN UPDATE and DELETE by EXPLAIN SELECT substitution (is that correct?) but EXPLAIN INSERT is still a big question.

So the questions are:

  1. Am I right that INSERT can be slow if there is at least one index?

  2. Can I EXPLAIN UPDATE and DELETE by EXPLAIN SELECT substitution?

  3. Which approaches can be used to investigate query speed in addition to mentioned phpmyadmin execution and EXPLAIN?

I'm using InnoDB engine.

Solution

  • INSERT must update all the indexes for each row inserted. However, for a single row, we are talking milliseconds at most.

    INSERT ... SELECT ... can be inserting arbitrarily many rows -- either the SELECT or the INSERT could be the problem.

    INSERT ... VALUES (1,2,3), (4,5,6), ... (a 'batched' insert) is faster than individual inserts, but still should not be much of a problem.

    DELETE and UPDATE can, of course, be touching arbitrarily many rows, hence be quite slow. Turn them into a SELECT, then do EXPLAIN SELECT ... and time it.

    For more specific help, please show us SHOW CREATE TABLE, tell us how much RAM you have and the value of innodb_buffer_pool_size (which should be about 70% of available RAM).

    What are your 'scripts' written in? Put timers around them. For example, in PHP, use microtime(true). In Perl use Time::HiRes.