By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,407 Members | 1,753 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,407 IT Pros & Developers. It's quick & easy.

Help required to optimize few select command.

P: n/a
rg
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
Jul 19 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.