Hello,
I use invisionboard on a bid web site (20 000 visitors each day)
But my server has high load average.
Could you help me to optimize these select command.
1)
SELECT p.*, m.id,m.name,m.m group,m.email,m .joined,m.avata r,m.avatar_size ,m.posts,m.aim_ name,m.icq_numb er,
m.signature, m.website,m.yah oo,m.title,m.hi de_email,m.msnn ame, g.g_id,
g.g_title, g.g_icon FROM ibfposts p LEFT JOIN ibfmembers m ON
(p.author_id=m. id) LEFT JOIN ibfgroups g ON (g.g_id=m.mgrou p) WHERE
p.topic_id='178 49' and p.queued !='1' ORDER BY p.pid LIMIT 3555, 15
table | type | possible_keys | key | key_len | ref |
rows | Extra
--------------------------------------------------------------------------------------------------------
p | ref | topic_id | topic_id | 8 | const |
3505 | where used; Using filesort
m | eq_ref | PRIMARY | PRIMARY | 8 | p.author_id |
1 |
g | eq_ref | PRIMARY | PRIMARY | 4 | m.mgroup |
1 |
mySQL time: 1.56941
2)
SELECT DISTINCT ibfposts.author _id, ibftopics.* from ibftopics LEFT
JOIN ibfposts ON (ibftopics.tid = ibfposts.topic_ id AND
ibfposts.author _id = '1') WHERE ibftopics.forum _id='2' and
(ibftopics.last _post > '0' OR ibftopics.pinne d='1') and
ibftopics.appro ved='1' ORDER BY pinned DESC, last_post DESC LIMIT 0,18
table |type | possible_keys | key | key_len | ref
| rows | Extra
--------------------------------------------------------------------------------------------------------------------------------
ibftopics | ref | forum_id | forum_id | 4 |
const,const | 4266 | where used; Using temporary; Using filesort
ibfposts | ref | topic_id,author _id | topic_id | 12 |
ibftopics.tid,c onst | 2 | Using index
mySQL time: 1.62815
3)
SELECT t.*, f.id as forum_id, f.name as forum_name FROM ibftopics t
LEFT JOIN ibfforums f ON (f.id=t.forum_i d) WHERE t.tid
IN(0,364,20756, 24279,22728,224 99,22754,22795, 22830,23022,230 31,23128,23152, 23174,23201,232 17,23256,23816, 23290,23295,234 16,23334,23430, 23463,23469,235 00,23551,23721, 23579,23594,236 07,23639,23746, 23750,23776,238 26,23894,23923, 23924,23981,240 02,24007,24115, 24077,24104,241 20,24128,24154, 24173,24190,242 76,24359,24462, 24449,24500,245 13,24572,24522, 24684,24686,227 18,106,22729,17 849,22618,22698 ,22724,227
7,22777,22781,2 2782,22783,2278 4,22788,22798,2 2802,22732,2282 4,22856,22866,2 2868,22871,2287 2,22873,22919,1 0384,22947,2295 5,22958,22957,2 2965,22978,1257 ,22986,23000,23 007,22865,23034 ,17194,16081,23 067,19146,23087 ,23088,23099,23 132,23133,23142 ,23147,23045,23 154,23156,23159 ,23160,23162,17 417,23186,23187 ,23169,23195,23 206,23203,23164 ,23192,23232,23 234,23248,23244 ,23261,23270,23 266,23271,23273 ,23282,232
7,23084,23293,2 3306,23322,2332 4,22106,23338,2 3340,23341,2334 2,23349,23356,2 3360,23361,2333 6,23369,22745,2 3389,23395,2340 8,23428,23440,2 3441,22579,2344 7,20526,23457,2 3445,23458,2338 5,23448,23474,2 3479,23482,2348 7,23490,23493,2 3494,6757,23526 ,23533,23525,23 538,23547,23545 ,23517,23562,23 557,23552,23577 ,23578,23588,23 595,23601,23615 ,23603,23617,23 496,4035,23644, 23648,23645,236 49,23655,23650, 23660,2366
,23671,23632,23 658,23629,23684 ,23688,23692,23 704,23702,23720 ,23724,23740,23 520,23755,23760 ,23773,12844,23 777,23792,23793 ,23808,23801,23 640,23819,23814 ,23781,23835,23 849,23851,23828 ,23869,23871,23 878,23879,23881 ,23859,23884,23 888,23890,23891 ,1866,23907,239 20,23921,23922, 23940,23941,235 87,23935,23958, 23929,23965,239 03,23976,23973, 23947,24004,240 10,24017,23990, 24022,24023,240 24,24027,24029, 24035,2403
,24039,24045,24 046,24053,24055 ,24057,23352,24 048,24085,24086 ,24087,24088,10 008,23605,12061 ,24097,24100,24 109,24108,23893 ,24111,24113,24 112,24118,24119 ,24122,24130,24 157,24164,24174 ,24166,24185,24 199,24175,24206 ,22009,24204,24 208,24211,24212 ,9348,24215,242 21,24236,24239, 24240,24244,240 99,24271,24272, 24290,24294,243 00,24308,24298, 24315,19080,243 28,24333,24336, 24337,24320,243 38,24323,24362, 24365,2427
,24381,24380,24 379,24382,24394 ,24180,24403,24 427,24444,24445 ,24443,24472,24 483,24488,24490 ,24482,24499,24 505,24509,24511 ,24518,24539,24 544,24533,24537 ,24552,24569,24 575,24579,24589 ,24602,24601,32 3,24614,24624,2 1536,24651,2464 4,4689,0)
ORDER BY t.last_post desc LIMIT 0,25
table | type | possible_keys | key | key_len | ref | rows
|Extra
----------------------------------------------------------------------------------------------------
t | range | PRIMARY | PRIMARY | 8 | | 375
| where used; Using filesort
f | eq_ref | PRIMARY | PRIMARY | 2 | t.forum_id | 1
|
mySQL time: 1.76363
Thanks for help !
Best Regards
RG
I use invisionboard on a bid web site (20 000 visitors each day)
But my server has high load average.
Could you help me to optimize these select command.
1)
SELECT p.*, m.id,m.name,m.m group,m.email,m .joined,m.avata r,m.avatar_size ,m.posts,m.aim_ name,m.icq_numb er,
m.signature, m.website,m.yah oo,m.title,m.hi de_email,m.msnn ame, g.g_id,
g.g_title, g.g_icon FROM ibfposts p LEFT JOIN ibfmembers m ON
(p.author_id=m. id) LEFT JOIN ibfgroups g ON (g.g_id=m.mgrou p) WHERE
p.topic_id='178 49' and p.queued !='1' ORDER BY p.pid LIMIT 3555, 15
table | type | possible_keys | key | key_len | ref |
rows | Extra
--------------------------------------------------------------------------------------------------------
p | ref | topic_id | topic_id | 8 | const |
3505 | where used; Using filesort
m | eq_ref | PRIMARY | PRIMARY | 8 | p.author_id |
1 |
g | eq_ref | PRIMARY | PRIMARY | 4 | m.mgroup |
1 |
mySQL time: 1.56941
2)
SELECT DISTINCT ibfposts.author _id, ibftopics.* from ibftopics LEFT
JOIN ibfposts ON (ibftopics.tid = ibfposts.topic_ id AND
ibfposts.author _id = '1') WHERE ibftopics.forum _id='2' and
(ibftopics.last _post > '0' OR ibftopics.pinne d='1') and
ibftopics.appro ved='1' ORDER BY pinned DESC, last_post DESC LIMIT 0,18
table |type | possible_keys | key | key_len | ref
| rows | Extra
--------------------------------------------------------------------------------------------------------------------------------
ibftopics | ref | forum_id | forum_id | 4 |
const,const | 4266 | where used; Using temporary; Using filesort
ibfposts | ref | topic_id,author _id | topic_id | 12 |
ibftopics.tid,c onst | 2 | Using index
mySQL time: 1.62815
3)
SELECT t.*, f.id as forum_id, f.name as forum_name FROM ibftopics t
LEFT JOIN ibfforums f ON (f.id=t.forum_i d) WHERE t.tid
IN(0,364,20756, 24279,22728,224 99,22754,22795, 22830,23022,230 31,23128,23152, 23174,23201,232 17,23256,23816, 23290,23295,234 16,23334,23430, 23463,23469,235 00,23551,23721, 23579,23594,236 07,23639,23746, 23750,23776,238 26,23894,23923, 23924,23981,240 02,24007,24115, 24077,24104,241 20,24128,24154, 24173,24190,242 76,24359,24462, 24449,24500,245 13,24572,24522, 24684,24686,227 18,106,22729,17 849,22618,22698 ,22724,227
7,22777,22781,2 2782,22783,2278 4,22788,22798,2 2802,22732,2282 4,22856,22866,2 2868,22871,2287 2,22873,22919,1 0384,22947,2295 5,22958,22957,2 2965,22978,1257 ,22986,23000,23 007,22865,23034 ,17194,16081,23 067,19146,23087 ,23088,23099,23 132,23133,23142 ,23147,23045,23 154,23156,23159 ,23160,23162,17 417,23186,23187 ,23169,23195,23 206,23203,23164 ,23192,23232,23 234,23248,23244 ,23261,23270,23 266,23271,23273 ,23282,232
7,23084,23293,2 3306,23322,2332 4,22106,23338,2 3340,23341,2334 2,23349,23356,2 3360,23361,2333 6,23369,22745,2 3389,23395,2340 8,23428,23440,2 3441,22579,2344 7,20526,23457,2 3445,23458,2338 5,23448,23474,2 3479,23482,2348 7,23490,23493,2 3494,6757,23526 ,23533,23525,23 538,23547,23545 ,23517,23562,23 557,23552,23577 ,23578,23588,23 595,23601,23615 ,23603,23617,23 496,4035,23644, 23648,23645,236 49,23655,23650, 23660,2366
,23671,23632,23 658,23629,23684 ,23688,23692,23 704,23702,23720 ,23724,23740,23 520,23755,23760 ,23773,12844,23 777,23792,23793 ,23808,23801,23 640,23819,23814 ,23781,23835,23 849,23851,23828 ,23869,23871,23 878,23879,23881 ,23859,23884,23 888,23890,23891 ,1866,23907,239 20,23921,23922, 23940,23941,235 87,23935,23958, 23929,23965,239 03,23976,23973, 23947,24004,240 10,24017,23990, 24022,24023,240 24,24027,24029, 24035,2403
,24039,24045,24 046,24053,24055 ,24057,23352,24 048,24085,24086 ,24087,24088,10 008,23605,12061 ,24097,24100,24 109,24108,23893 ,24111,24113,24 112,24118,24119 ,24122,24130,24 157,24164,24174 ,24166,24185,24 199,24175,24206 ,22009,24204,24 208,24211,24212 ,9348,24215,242 21,24236,24239, 24240,24244,240 99,24271,24272, 24290,24294,243 00,24308,24298, 24315,19080,243 28,24333,24336, 24337,24320,243 38,24323,24362, 24365,2427
,24381,24380,24 379,24382,24394 ,24180,24403,24 427,24444,24445 ,24443,24472,24 483,24488,24490 ,24482,24499,24 505,24509,24511 ,24518,24539,24 544,24533,24537 ,24552,24569,24 575,24579,24589 ,24602,24601,32 3,24614,24624,2 1536,24651,2464 4,4689,0)
ORDER BY t.last_post desc LIMIT 0,25
table | type | possible_keys | key | key_len | ref | rows
|Extra
----------------------------------------------------------------------------------------------------
t | range | PRIMARY | PRIMARY | 8 | | 375
| where used; Using filesort
f | eq_ref | PRIMARY | PRIMARY | 2 | t.forum_id | 1
|
mySQL time: 1.76363
Thanks for help !
Best Regards
RG