
e hajd sad vi skontajte šta su mi napisali.
Hello,
We do apologize for the inconvenience this has caused; we have created an additional index on table 'dzombaa_karma' from database 'vidovita_forum' which appears to effectively relieve the mysqltmp disk space contention:
create index hg_index1 on dzombaa_karma (poster_id, user_id);
It was noted that the queries of the following form were pulling a considerable number of rows per execution:
mysql> explain SELECT u.*, z.friend, z.foe, p.*, k.karma_time, k2.karma_time as karmaed, gu.personal_album_id, gu.user_images FROM (dzombaa_users u, dzombaa_posts p) LEFT JOIN dzombaa_zebra z ON (z.user_id = 1 AND z.zebra_id = p.poster_id) LEFT JOIN dzombaa_gallery_users gu ON (gu.user_id = p.poster_id) LEFT JOIN dzombaa_karma k ON (k.poster_id = 1 AND k.user_id = p.poster_id) LEFT JOIN dzombaa_karma k2 ON (k2.poster_id = 1 AND k2.post_id = p.post_id AND k2.user_id = p.poster_id) WHERE p.post_id IN (203457, 203515, 203538, 203546, 217562, 217638, 234988, 289407, 289413, 289416, 289420, 289422, 289424, 289445, 290076, 290081, 290088, 290236, 290237, 290246)
AND u.user_id = p.poster_id
+----+-------------+-------+--------+-------------------+---------+---------+----------------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-------------------+---------+---------+----------------------------------+------+-------------+
| 1 | SIMPLE | p | range | PRIMARY,poster_id | PRIMARY | 3 | NULL | 20 | Using where |
| 1 | SIMPLE | z | eq_ref | PRIMARY | PRIMARY | 6 | const,vidovita_forum.p.poster_id | 1 | |
| 1 | SIMPLE | gu | eq_ref | PRIMARY | PRIMARY | 3 | vidovita_forum.p.poster_id | 1 | |
| 1 | SIMPLE | u | eq_ref | PRIMARY | PRIMARY | 3 | vidovita_forum.p.poster_id | 1 | |
| 1 | SIMPLE | k | ALL | NULL | NULL | NULL | NULL | 6653 | |
| 1 | SIMPLE | k2 | ALL | NULL | NULL | NULL | NULL | 6653 | |
+----+-------------+-------+--------+-------------------+---------+---------+----------------------------------+------+-------------+
6 rows in set (0.00 sec)
After the index was created, the performance of the query appears to have improved considerably. The number of rows parsed is considerably lower, and we have noted no further mysqltmp-related issues:
+----+-------------+-------+--------+-------------------+-----------+---------+----------------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-------------------+-----------+---------+----------------------------------+------+-------------+
| 1 | SIMPLE | p | range | PRIMARY,poster_id | PRIMARY | 3 | NULL | 20 | Using where |
| 1 | SIMPLE | z | eq_ref | PRIMARY | PRIMARY | 6 | const,vidovita_forum.p.poster_id | 1 | |
| 1 | SIMPLE | gu | eq_ref | PRIMARY | PRIMARY | 3 | vidovita_forum.p.poster_id | 1 | |
| 1 | SIMPLE | k | ref | hg_index1 | hg_index1 | 6 | const,vidovita_forum.p.poster_id | 1 | |
| 1 | SIMPLE | k2 | ref | hg_index1 | hg_index1 | 6 | const,vidovita_forum.p.poster_id | 1 | |
| 1 | SIMPLE | u | eq_ref | PRIMARY | PRIMARY | 3 | vidovita_forum.p.poster_id | 1 | |
+----+-------------+-------+--------+-------------------+-----------+---------+----------------------------------+------+-------------+
6 rows in set (0.00 sec)
We cannot guarantee that this singular index will resolve all problems going forward, but the present issue appears to be resolved. Please let us know if you observe any further issues with the site, or if you have any questions or concerns.
Sincerely,
Michael H
Server Monitor
HostGator.com LLC
http://support.hostgator.com