Long queries... very long

[SecondSight]

Hello !

In my myslq-slow.log file, I have very very very long queries.

For instance, here is the beginning and the end of one of these queries :

# Time: 100803 17:20:04

# User@Host: myboard_forums[myboard_forums] @ localhost []

# Query_time: 4 Lock_time: 0 Rows_sent: 25 Rows_examined: 191613

SELECT t.*,p.*,m.members_display_name, m.members_seo_name, m.member_id FROM ibf_topics t LEFT JOIN ibf_posts p ON ( p.pid=t.topic_firstpost )

LEFT JOIN ibf_members m ON ( m.member_id=p.author_id ) WHERE t.forum_id IN (139,140,138,137,148,154,149,175,150,151,152,153,147,146,141,143,142,144,145,30,22,98,59,161,33,29,28,31,99,56,15,177,70,1,35,19,4,158,44,21,53,43,

165,163,159,5,64,164,97,9,174,10,125,126,127,128,160,12,66,130,132,135,14,18,162,129,16,41,84,85,86,179,88,87,89,91,92,104,105,106,107,108,109,110,

111,112,113,114,115,116,117,118,119,120,121,122,123,124,100,101,102,103,171,168,169,170,90,83,184,185,182,191,192,193,194,195,196,197,198,199,200,

201,180,203,204,205,206,207,208,209,210,183,187,188,189)

AND ((t.forum_id=52 AND t.last_post > 0) OR (t.forum_id=45 AND t.last_post > 0) OR (t.forum_id=48 AND t.last_post > 0) OR (t.forum_id=167 AND t.last_post > 0) OR (t.forum_id=49 AND t.last_post > 0) OR (t.forum_id=181 AND t.last_post > 0) OR (t.forum_id=6 AND t.last_post > 0) OR (t.forum_id=50 AND t.last_post > 0) OR (t.forum_id=51 AND t.last_post > 0) OR (t.forum_id=27 AND t.last_post > 0) OR (t.forum_id=47 AND t.last_post > 0) OR (t.forum_id=139 AND (( t.tid <> 215832 OR ( t.tid=215832 AND t.last_post > 1263055281 ) ) OR ( t.tid <> 58117 OR ( t.tid=58117 AND t.last_post > 1263997560 ) ) OR ( t.tid <> 216025 OR ( t.tid=216025 AND t.last_post > 1263397927 ) ) OR ( t.tid <> 210379 OR ( t.tid=210379 AND t.last_post > 1263634836 ) ) OR ( t.tid <> 216327 OR ( t.tid=216327 AND t.last_post > 1264160799 ) ) OR ( t.tid <> 216392 OR ( t.tid=216392 AND t.last_post > 1264001741 ) )

...

OR ( t.tid=229651 AND t.last_post > 1277331959 ) ) OR ( t.tid <> 229312 OR ( t.tid=229312 AND t.last_post > 1277380831 ) )) AND t.last_post > 0)) AND t.state != 'link' AND t.approved=1 ORDER BY t.last_post DESC LIMIT 0,25;

I copied and pasted the query in a txt file but it's 10 Mo big... so I can't upload it here. Isn't 10 Mo big for only one query ?

Where's my mistake ?
Can you advise me ?

Thank you ! :)

[Michael]

That looks like the IP.Board 3.0.5 View New Posts query. That should be cleaned up in 3.1.2, have you, or can you upgrade?

[SecondSight]

Yes, I'm still running IPB 3.0.5.

I intend to have my board (and blogs, gallery,...) upgraded soon. I will open a ticket for this because my database is 8 Giga big and I think it will have to be done through SSH...

Thank you ! :)

[Robulosity2]

Yes, I'm still running IPB 3.0.5.

I intend to have my board (and blogs, gallery,...) upgraded soon. I will open a ticket for this because my database is 8 Giga big and I think it will have to be done through SSH...

Thank you ! :)

The Manaul Upgrade is pretty easy, you would run the installer put tell it to show the manual queries. You'd want to be SSH'd in at the same time

From SSH

Run "mysql" with out the quotes

You would than take your database (which you can find in conf_global.php) and run

use database_name;

Press Enter

From here you'd just copy/paste the MySQL queries the upgrader gives

The important part is with SSH "right click" is the same as Paste (in say putty, secureCRT etc) and do not go to the next step in the installer until the database query is completed.

[SecondSight]

Hello ! :)

My board has already been upgraded. :)

Anyway, thanks a lot for the explanations. It's very interesting to know how it is done. :)