473,326 Members | 2,108 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Help required to optimize few select command.

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
0 1642

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: rg | last post by:
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.*,...
0
by: Andreas Falck | last post by:
Hi, I ran the code below on two different versions, 4.0.14 and 4.0.16 respectively, both running RH 7.3 on intel. In version mysql server version 4.0.14 the SELECT privelege suffices for...
6
by: Ryan Smith | last post by:
I am having a problem with a datagrid I am trying to use. I have an Access DB with two records I want to display. All that is displayed is the heading for the datagrid. I can't seem to display...
7
by: Aaron | last post by:
Complete code follows. I am new to .NET programming (and programming in general) and I am having a difficult time understanding how to fill a variable in one sub, and then access it from...
3
by: Adriano | last post by:
Hello, when I try to print something, either DataGrid or from Crystal Report viever the folowing error message appears and cancels printing: Object reference not set to an instance of an...
4
by: Huaer.XC | last post by:
>From the following MySQL command: EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id) JOIN t3 ON t3.name = t1.name WHERE t1.id IN(123, 124); which result is:...
2
by: this one | last post by:
I have the following code <html> <head> <title>Untitled Document</title> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> <script language="JavaScript"...
0
by: gunimpi | last post by:
http://www.vbforums.com/showthread.php?p=2745431#post2745431 ******************************************************** VB6 OR VBA & Webbrowser DOM Tiny $50 Mini Project Programmer help wanted...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.