MariaDB Slave cause "Copying to tmp table" DB: MariaDB 10.0.23 

STOP SLAVE;
run query
SHOW PROFILE;

Status  Duration
starting    0.000028
Waiting for query cache lock    0.000006

init    0.000004
checking query cache for query  0.000011
checking privileges on cached   0.000005
checking permissions    0.000007
checking permissions    0.000005
checking permissions    0.000005
sending cached result to clien  0.000154
updating status 0.000005
cleaning up 0.000003


After starting slave, long "Copying to tmp table" shows up:

START SLAVE;
run query  EXPLAIN outputs same query plan like before starting the slave.
SHOW PROFILE;

Status  Duration
starting    0.000046
Waiting for query cache lock    0.000007
init    0.000006
checking query cache for query  0.000109
checking permissions    0.000007
checking permissions    0.000005
checking permissions    0.000006
Opening tables  0.000020
After opening tables    0.000009
System lock 0.000007
Table lock  0.000005
After opening tables    0.000007
Waiting for query cache lock    0.000005
After opening tables    0.000031
init    0.000050
optimizing  0.000032
statistics  0.000139
preparing   0.000050
executing   0.000007
Copying to tmp table    0.000025
Copying to tmp table    1.760070   &lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;
Sorting result  0.003707
Sending data    0.000013
end 0.000005
removing tmp table  0.000261
end 0.000006
query end   0.000006
closing tables  0.000010
freeing items   0.000013
updating status 0.000068
logging slow query  0.000008
cleaning up 0.000005
Not MariaDB Slave, the Query Cache.

It appears that the first Profile came from a machine where the identical query had already been stored in the Query Cache.  Hence, the query took only a fraction of a millisecond to simply deliver the old resultset to the client.

The second server apparently has not seen the identical query recently, so it had to work on the query (open tables, optimize, use a tmp table, etc).  This took a lot longer.

Addenda

The Query Cache works this way:


If a query is in the QC, and exactly the same query shows up to be run, the resultset is delivered very rapidly from the QC.  However,
When any modification to the tables(s) involved occurs, the all entries in the QC for that table(s) are purged.
Replication is a stream of modifications.


For this reason, then QC is rarely worth turning on for Production servers with a lot of write traffic.

So, I deduce that the some modification (INSERT, UPDATE, etc) occurred to that table, thereby invalidating the entry in the QC before that second copy of the SELECT was presented.

If the stream of changes from Replication had only touched other tables, then the second query would have been just as fast as the first.