3

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 |
| updating status      | 0.000012 |
| cleaning up          | 0.000003 |
+----------------------+----------+

SHOW STATUS like 'Hand%';

+----------------------------+-------+
| Handler_commit             | 0     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 0     |
| Handler_icp_attempts       | 0     |
| Handler_icp_match          | 0     |
| Handler_mrr_init           | 0     |
| Handler_mrr_key_refills    | 0     |
| Handler_mrr_rowid_refills  | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 0     |
| Handler_read_key           | 13759 |
| Handler_read_last          | 0     |
| Handler_read_next          | 11501 |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 12    |
| Handler_read_rnd_deleted   | 0     |
| Handler_read_rnd_next      | 4292  |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_tmp_update         | 9495  |
| Handler_tmp_write          | 2031  |
| Handler_update             | 0     |
| Handler_write              | 0     |
+----------------------------+-------+

explain

+------+-------------+-------+------+---------------+--------+---------+--------------+------+---------------------------------+
| id   | select_type | table | type | possible_keys | key    | key_len | ref          | rows | Extra                           |
+------+-------------+-------+------+---------------+--------+---------+--------------+------+---------------------------------+
|    1 | SIMPLE      | t     | ALL  | PRIMARY       | NULL   | NULL    | NULL         | 2258 | Using temporary; Using filesort |
|    1 | SIMPLE      | pt    | ref  | tag_id        | tag_id | 4       | website.t.id |    5 | Using index                     |
+------+-------------+-------+------+---------------+--------+---------+--------------+------+---------------------------------+

it takes about 23ms. the tags table has about 2000 rows and the pic_tag is a pivot(?) table where the relation of pics and tags is saved. (pic_id - tag_id) it has about 11000 rows.

I think UNION would almost be too much code, for 12 or 15 or something tags that i want to show. Is there no way to somehow deal with the tmp table? Or is the tmp table itself not the problem?

  • 2
    I'm not familiar enough with MySQL, but I'd try the random limited select on pics in an inner query, and join on users outside - looks like it is materializing that "where-less" join. – Mat Oct 21 '14 at 8:34
2

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 !!!

  • it is indeed decreasing query time from about 40ms to about 2-5ms – haheute Oct 21 '14 at 11:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.