Make this query faster?

atokatim

is there any way to make this query faster? sometimes it takes 2 seconds to run this query.

$sql=mysql_query("UPDATE members SET used_bandwidth=used_bandwidth+1 WHERE member_id = '$folder'");
$member_result = mysql_query("SELECT filename, songname, bandname FROM images WHERE mid = '$folder' ORDER BY ord");
while($file = mysql_fetch_object($member_result)){
$url = $file->filename;
$name = $file->songname;
$band = $file->bandname;
$xmlstring = $xmlstring . $song . 'name="'.$name.'" band="'.$band.'" file="'.$url.'"/>';
}
mysql_free_result($member_result);
mysql_close($db);

member_id is indexed
mid is indexed

Thanks!

Roger_Ramjet

Which query, the update or the select?

atokatim

any of it..... my server is telling me that it may take up to 4 seconds to proccess the mysql.

leatherback

Atokatim:
4 seconds for these simply queries is a lot...

How many record do you have in the database?
Is folder an indexed column?

Piranha

There are 2 queries and handling of the result afterwards. The first thing you need to know is exactly where the problem is, then it is possible to solve it. Use [man]microtime[/man] to find out how long the different parts take.

By the way, I think that $xmlstring won't work after the loop. You close the tag, but you never open it.

atokatim

sometimes it is the UPDATE takes a few seconds, and sometimes the Select takes a few seconds.... there are over 180,000 rows in the tables

Sxooter

How many rows are you usually updating, and how many rows are you usually selecting.

If it's one or two, then you've got a problem that can be solved with indexes. If it's 99% of the table, not so much.

atokatim

Here is one of my slow queries :

Query_time: 4 seconds Lock_time: 0 Rows_sent: 6 Rows_examined: 12

SELECT filename, songname, bandname FROM images WHERE mid = '20437' ORDER BY ord

Only 1 row is being updated per query. The row MID is indexed and so is member_id. The script is usually called 3 times a second so i need to make it faster so that my serverload goes down.

Sxooter

What table handler are you using?

atokatim

handler? what is that? all i know is that i have Mysql 4. something and phpMyAdmin

Sxooter

Myisam tables, or innodb table handler. This almost sounds like a problem with innodb table bloating, but it could be in myisam too I guess.

atokatim

i have no idea what those are 🙁 i am on a shared host and am only allowed 20 connections at a time.... i need to make the connections VERY fast so that i decrease the odds of 20 connections happening at the same time.

Sxooter

They're the types of tables mysql supports. you're almost certainly using isam tables, so don't worry about it.

Are you on a shared mysql host as well? It could just be that other people are beating up the db server so bad your performance sucks.