When is mariadb creating tmp tables?

I have found, that copying to tmp table is taking most of the time of a slow query, that I wanted to optimize. I watched the output of 'profiling' in a terminal. This happens on a pics table with 6000 rows and a user table with 30 rows.

...
| Copying to tmp table | 0.000263 |
| Copying to tmp table | 0.052237 |
| Sorting result | 0.000035 |
...

Why (or when) is mariadb using tmp tables? Can I change the query to avoid using tmp tables?

SELECT `pics`.*, `users`.`username`, `users`.`displayname` 
FROM `pics` 
LEFT JOIN `users` 
    ON `users`.`id` = `pics`.`user_id` 
ORDER BY RAND() asc 
LIMIT 4;

I have these indexes:

pics:

PRIMARY id
UNIQUE  created_at
INDEX   user_id
INDEX   location

users:

PRIMARY id
UNIQUE  username

edit:

i have another one, also slow, and also because of copying to tmp table:

SELECT t.tag, count(pt.tag_id) as anzahl 
FROM tags t
JOIN pic_tag pt 
    ON t.id = pt.tag_id
GROUP BY t.tag COLLATE utf8_unicode_ci
ORDER BY RAND() 
LIMIT 12;

show profile for query 1;

```
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000149 |
| checking permissions | 0.000009 |
| checking permissions | 0.000008 |
| Opening tables | 0.000031 |
| After opening tables | 0.000010 |
| System lock | 0.000010 |
| Table lock | 0.000008 |
| After table lock | 0.000012 |
| init | 0.000058 |
| optimizing | 0.000028 |
| statistics | 0.000061 |
| preparing | 0.000045 |
| executing | 0.000007 |
| Copying to tmp table | 0.000101 |
| Copying to tmp table | 0.034019 |
| Sorting result | 0.000426 |
| Sending data | 0.000023 |
| end | 0.000006 |
| removing tmp table | 0.000129 |
| end | 0.000004 |
| query end | 0.000003 |
| closing tables | 0.000011 |
| freeing items | 0.000006 |
| updatin

Following the famous article "fixing ORDER BY rand()", and in particular the multiple-selection via union, we can write this:

(
 SELECT   `p1`.*,
          `users`.`username`,
          `users`.`displayname`
 FROM     pics p1
 JOIN
          (SELECT ceil(rand() *
                  (SELECT max(id)
                   FROM   pics)) AS id
          ) AS p2
 JOIN     users
 ON       users.id = p1.user_id
 WHERE    p1.id >= p2.id
 ORDER BY p1.id ASC limit 1
)
UNION ALL
(
 SELECT   `p1`.*,
          `users`.`username`,
          `users`.`displayname`
 FROM     pics p1
 JOIN
          (SELECT ceil(rand() *
                  (SELECT max(id)
                   FROM   pics)) AS id
          ) AS p2
 JOIN     users
 ON       users.id = p1.user_id
 WHERE    p1.id >= p2.id
 ORDER BY p1.id ASC limit 1
)
UNION ALL
(
 SELECT   `p1`.*,
          `users`.`username`,
          `users`.`displayname`
 FROM     pics p1
 JOIN
          (SELECT ceil(rand() *
                  (SELECT max(id)
                   FROM   pics)) AS id
          ) AS p2
 JOIN     users
 ON       users.id = p1.user_id
 WHERE    p1.id >= p2.id
 ORDER BY p1.id ASC limit 1
)
UNION ALL
(
 SELECT   `p1`.*,
          `users`.`username`,
          `users`.`displayname`
 FROM     pics p1
 JOIN
          (SELECT ceil(rand() *
                  (SELECT max(id)
                   FROM   pics)) AS id
          ) AS p2
 JOIN     users
 ON       users.id = p1.user_id
 WHERE    p1.id >= p2.id
 ORDER BY p1.id ASC limit 1
)

This method does not create perfect random numbers because it assumes that the distribution of ids is regular. Also, the union all may select a row twice. It is not the exact same query, either, I used an INNER JOIN in order not to select users without pictures, but that is easily fixable.

Your method (ORDER BY rand()) in 5.6 (which may have been optimized already in respect to 5.5):

mysql> SHOW STATUS like 'Hand%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 1     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 4     |
| Handler_mrr_init           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 1     |
| Handler_read_key           | 6041  |
| Handler_read_last          | 0     |
| Handler_read_next          | 0     |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 4     |
| Handler_read_rnd_next      | 12122 |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 6060  |
+----------------------------+-------+
18 rows in set (0.00 sec)

My method (article's one):

mysql> SHOW STATUS like 'Hand%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 1     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 24    |
| Handler_mrr_init           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 4     |
| Handler_read_key           | 12    |
| Handler_read_last          | 4     |
| Handler_read_next          | 0     |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 9     |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 8     |
+----------------------------+-------+
18 rows in set (0.06 sec)

As you can see, reads and writes have been changed from thousands to 12 reads and 8 writes.
GIVE IT A TRY !!!

StackExchange Database Administrators Q#80712, answer score: 2