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.mgroup,m.email,m.joined,m.avatar,m.a vatar_size,m.posts,m.aim_name,m.icq_number,
m.signature, m.website,m.yahoo,m.title,m.hide_email,m.msnname, 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.mgroup) WHERE
p.topic_id='17849' 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.pinned='1') and
ibftopics.approved='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,const | 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_id) WHERE t.tid
IN(0,364,20756,24279,22728,22499,22754,22795,22830 ,23022,23031,23128,23152,23174,23201,23217,23256,2 3816,23290,23295,23416,23334,23430,23463,23469,235 00,23551,23721,23579,23594,23607,23639,23746,23750 ,23776,23826,23894,23923,23924,23981,24002,24007,2 4115,24077,24104,24120,24128,24154,24173,24190,242 76,24359,24462,24449,24500,24513,24572,24522,24684 ,24686,22718,106,22729,17849,22618,22698,22724,227
7,22777,22781,22782,22783,22784,22788,22798,22802, 22732,22824,22856,22866,22868,22871,22872,22873,22 919,10384,22947,22955,22958,22957,22965,22978,1257 ,22986,23000,23007,22865,23034,17194,16081,23067,1 9146,23087,23088,23099,23132,23133,23142,23147,230 45,23154,23156,23159,23160,23162,17417,23186,23187 ,23169,23195,23206,23203,23164,23192,23232,23234,2 3248,23244,23261,23270,23266,23271,23273,23282,232
7,23084,23293,23306,23322,23324,22106,23338,23340, 23341,23342,23349,23356,23360,23361,23336,23369,22 745,23389,23395,23408,23428,23440,23441,22579,2344 7,20526,23457,23445,23458,23385,23448,23474,23479, 23482,23487,23490,23493,23494,6757,23526,23533,235 25,23538,23547,23545,23517,23562,23557,23552,23577 ,23578,23588,23595,23601,23615,23603,23617,23496,4 035,23644,23648,23645,23649,23655,23650,23660,2366
,23671,23632,23658,23629,23684,23688,23692,23704,2 3702,23720,23724,23740,23520,23755,23760,23773,128 44,23777,23792,23793,23808,23801,23640,23819,23814 ,23781,23835,23849,23851,23828,23869,23871,23878,2 3879,23881,23859,23884,23888,23890,23891,1866,2390 7,23920,23921,23922,23940,23941,23587,23935,23958, 23929,23965,23903,23976,23973,23947,24004,24010,24 017,23990,24022,24023,24024,24027,24029,24035,2403
,24039,24045,24046,24053,24055,24057,23352,24048,2 4085,24086,24087,24088,10008,23605,12061,24097,241 00,24109,24108,23893,24111,24113,24112,24118,24119 ,24122,24130,24157,24164,24174,24166,24185,24199,2 4175,24206,22009,24204,24208,24211,24212,9348,2421 5,24221,24236,24239,24240,24244,24099,24271,24272, 24290,24294,24300,24308,24298,24315,19080,24328,24 333,24336,24337,24320,24338,24323,24362,24365,2427
,24381,24380,24379,24382,24394,24180,24403,24427,2 4444,24445,24443,24472,24483,24488,24490,24482,244 99,24505,24509,24511,24518,24539,24544,24533,24537 ,24552,24569,24575,24579,24589,24602,24601,323,246 14,24624,21536,24651,24644,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
