• tomekf
  • Senior Member
  • Offline
  • From: Poland
  • Registered: 2005-10-30
  • Posts: 104

Topic: Slow query on new forum 1.2.15

Hi,

I have problem with first query on NEW forum:

3.57019     SELECT u.*, g.*, o.logged, o.idle FROM users AS u INNER JOIN groups AS g ON u.group_id=g.g_id LEFT JOIN online AS o ON o.user_id=u.id WHERE u.id=2
0.07237     UPDATE online SET logged=1182514762 WHERE user_id=2
0.07926     SELECT * FROM online WHERE logged<1182514464
0.00008     SELECT t.id FROM pun_1_topics AS t INNER JOIN forums AS f ON f.id=t.forum_id LEFT JOIN forum_perms AS fp ON (fp.forum_id=f.id AND fp.group_id=1) WHERE (fp.read_forum IS NULL OR fp.read_forum=1) AND t.last_post>1182514365 AND t.moved_to IS NULL
0.00007     SELECT COUNT(id) FROM reports WHERE zapped IS NULL
Total query time: 3.72197 s

Have you any ideas to fix this ?

Darmowe forum - Polish free forum hosting

tomekf's Website

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: Slow query on new forum 1.2.15

I would run an EXPLAIN on the query and see if anything looks wrong with it. If not, I would guess that the issue is with the MySQL server

  • tomekf
  • Senior Member
  • Offline
  • From: Poland
  • Registered: 2005-10-30
  • Posts: 104

Re: Slow query on new forum 1.2.15

Where should I have INDEX for

SELECT u.*, g.*, o.logged, o.idle FROM users AS u INNER JOIN groups AS g ON u.group_id=g.g_id LEFT JOIN online AS o ON o.user_id=u.id WHERE u.id=274;
Darmowe forum - Polish free forum hosting

tomekf's Website

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: Slow query on new forum 1.2.15

Smartys wrote:

I would run an EXPLAIN on the query and see if anything looks wrong with it. If not, I would guess that the issue is with the MySQL server

Without that data, there's no way to determine what, if anything, needs to be tweaked.

  • tomekf
  • Senior Member
  • Offline
  • From: Poland
  • Registered: 2005-10-30
  • Posts: 104

Re: Slow query on new forum 1.2.15

I run explain for you when server load is more then 10 hmm 

mysql> explain SELECT u.*, g.*, o.logged, o.idle FROM users AS u INNER JOIN groups AS g ON u.group_id=g.g_id LEFT JOIN online AS o ON o.user_id=u.id WHERE u.id=5;
+----+-------------+-------+-------+--------------------+--------------------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys      | key                | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+--------------------+--------------------+---------+-------+------+-------+
|  1 | SIMPLE      | u     | const | PRIMARY,group_id   | PRIMARY            | 4       | const |    1 |       |
|  1 | SIMPLE      | g     | const | PRIMARY            | PRIMARY            | 4       | const |    1 |       |
|  1 | SIMPLE      | o     | ref   | online_user_id_idx | online_user_id_idx | 4       | const |    1 |       |
+----+-------------+-------+-------+--------------------+--------------------+---------+-------+------+-------+
3 rows in set (31.84 sec)
Darmowe forum - Polish free forum hosting

tomekf's Website

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: Slow query on new forum 1.2.15

That's a MySQL server issue, not a query issue wink

  • tomekf
  • Senior Member
  • Offline
  • From: Poland
  • Registered: 2005-10-30
  • Posts: 104

Re: Slow query on new forum 1.2.15

But don't you think that u.*, g.* give to many result?

How should I change it?

Darmowe forum - Polish free forum hosting

tomekf's Website

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: Slow query on new forum 1.2.15

No, I don't. It returns one row of information. The issue is your server, not the query.

  • tomekf
  • Senior Member
  • Offline
  • From: Poland
  • Registered: 2005-10-30
  • Posts: 104

Re: Slow query on new forum 1.2.15

Smartys wrote:

The issue is your server, not the query.

... but my admin says that it's query problem ... and I don't know now what to do hmm

I have over 20k forums PunBB on my server and I need do something to fix it ...

Darmowe forum - Polish free forum hosting

tomekf's Website

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: Slow query on new forum 1.2.15

Show your administrator the output of the EXPLAIN statement and ask them to explain how it's the query's problem. wink
And post the response if they still insist it is, I would be interested to hear the justification.