Help finding mysql error

[masterofollies]

I've been having server load problems lately. My load limit is 10.00 and it's been between 18.00 and 45.00 and my webhost says it is sql queries causing it. Would anybody be willing to browse a few scripts and see if you notice any problems like the code query being repeated or something?

[guelphdad]

are you doing queries within queries? ie getting all your categories and then while looping through the output do a separate query to get all items in those categories?

have you run EXPLAIN against each of your queries?

do you have your tables properly indexed?

do you have the ability to edit the my.cnf/my.ini file to tweak your server settings?

[masterofollies]

I don't know how to use while statements. I use a bunch of IF, ELSEIF and ELSE statements.

I never heard of EXPLAIN function.

I don't have any table errors.

I am pretty sure I can edit that file.

[guelphdad]

are your tables properly indexed? primary key columns don't need additional indexes, but columns you are joining on should have them.

if you don't use a while loop to fetch your results perhaps you can give us an example of one of your queries and show us how you output the results.

EXPLAIN is a mysql command that tells you how the query will be performed, just add it to the very beginning of your query and run that in mysql client or using the sql tab on phpmyadmin if you use that. it will let you know if it is using a file sort or an index or whatever.

if you don't have shell access and this isn't your machine you most likely can't edit the my.ini/my.cnf file as you would then be changing the performance for anyone else using the database.

[masterofollies]

Since I had the website online April 25th 2007 until up to 10 days ago. I never had server load problems at all. It just recently started happening.

An example of queries would be something like this,

if ($class == "1") {
$updatequery = doquery("UPDATE {{table}} SET class='2' WHERE id='".$userrow["id"]."' LIMIT 1", "users");
}

elseif ($class == "2") {
$updatequery = doquery("UPDATE {{table}} SET class='3' WHERE id='".$userrow["id"]."' LIMIT 1", "users");
}

elseif ($class == "3") {
$updatequery = doquery("UPDATE {{table}} SET class='4' WHERE id='".$userrow["id"]."' LIMIT 1", "users");
}

elseif ($class == "4") {
$updatequery = doquery("UPDATE {{table}} SET class='5' WHERE id='".$userrow["id"]."' LIMIT 1", "users");
}

else {
$updatequery = doquery("UPDATE {{table}} SET class='0' WHERE id='".$userrow["id"]."' LIMIT 1", "users");
}


I will try a explain command sometime.

Yes I do have shell access.