MySQL very slow.

[altendew]

Hi, I have read a lot about optimization, and I actually own some books but I am thrown for a loop on this one.

I have this one site that slows down all my others because the queries are so massive.

For example one of these queries I use to-do a search by a user's account number. I also get the position he is at on the list, and in order to-do that I need to select ALL the rows.

For example, I filter out the other queries in php.

$num = 1;
$q = mysql_query('SELECT * FROM lists');
while($r = mysql_fetch_assoc($q))
{

   if($r['account']=='accountNum')
   {
         print 'You are at pos. num '.$num.'<br>';
   }
   $num++;
}

But my point is this one query should not be slowing down my site, I have a lot more queries running now, because this one used to be fine. I know about optimization and my MySQL settings look fine to me.

Any Suggestions,
Thanks,
Andrew

 

[guelphdad]

There are two things wrong with your query.

1) you are selecting all data from all rows when what you need to know is the position

2) more importantly you do not have an order by clause in your query. your "position" information thus makes the assumption that the rows are returned in the same order each time. They are not. what data are you comparing (i.e. in which column) that you determine your order or rank for that account?

 

[altendew]

Sorry I just made up that code real quick beacuse I did not want to post the real code in.

$num = 1;
$q = mysql_query('SELECT * FROM lists ORDER BY id');
while($r = mysql_fetch_assoc($q))
{

   if($r['account']=='accountNum')
   {
         print 'You are at pos. num '.$num.'<br>';
   }
   $num++;
}

So how could I get the position without having to-do all that?

 

[guelphdad]

So you want to know, according to id, which position your user is with accountNum equal to a specific value right?

Something as simple as this:

select 
accountNum,
id,
(select count(id)+1 as whatplace
from lists
where id <= X.id) as Position
from lists as X
where accountNum=somevalue

this way you are only returning what you need. you can even leave the accountNum and id out of the select if all you need to know is the position. I'd say to leave it in while you test of course so you can see you are returning the correct row.

 

[altendew]

but wont that run a extra query for each row.. that might be worst.

 

[guelphdad]

you are selecting all of the data from every column for every row and then matching that to a variable inside a loop to see if it matches. That is significantly slower than my query.

 

[altendew]

I currently only have MySQL 4.0, and it does not support sub queries.

 

[guelphdad]

Since you are using a very outdated version of mysql you should mention that in any threads you start on discussion boards. That way people won't come up with solutions that don't work for you. MySQL 4.1 (which was the first to support subqueries), was the full production release version of mysql in October 2004. That has since been supplanted by version 5.0 in October 2005.

 

[altendew]

I am very sorry, thank you for your help. I will use this solution when I Upgrade to 5.0