PHP/MySQL performance issues

Hi All,

I'm having trouble with MySQL falling over on my dedicated server and I believe it may be due to some inefficient SQL queries.

Here's what I started with:

$numresults=mysql_query("select * from links where catagory=".$catagory." order by linknum");
$numrows=mysql_num_rows($numresults);

Code (markup):

But I realised that the 'order by' clause was unnecessary because I'm only looking for a count and I'm not interested in the actual content.

So now I have this:

$numresults=mysql_query("select * from links where catagory=".$catagory."");
$numrows=mysql_num_rows($numresults);

Code (markup):

I've been informed that the 'select count(*)...' approach would be more efficient BUT I don't know how to extract this using PHP - can anyone help?

EDIT: I think I've actually found a solution here: http://us3.php.net/mysql_num_rows but the question below still stands:

I'd also like to know if there are any logs that I could look in or any MySQL tools that may help me determine exactly why MySQL keeps crashing...

Thanks in advance,

p.

 

$numresults=mysql_query("SELECT COUNT(*) FROM links where catagory=".$catagory."");

Thats all

Regards

Alex

 

The Query above is exactly how I would do it.

However I would make some indexes on the table. Multi index, full text and unique where possible.

Also be sure not to store null values into the table.

I have explained why in another post:

http://forums.digitalpoint.com/showthread.php?t=629690

 

$numresults=mysql_query("SELECT COUNT(*) as cn FROM links where catagory=".$catagory."");

$array= mysql_fetch_array($numresults, MYSQL_ASSOC);

echo $array["cn"] ;

 

Without indexes this query is no better then the previous one you posted in my opinion.

 

Except that the previous one doesn't work (his '$numresults' variable was set to a result set, not the count that he was after). The new one extends the old one to the point where he can actually get the count value.

 

Thanks guys - that's exactly the problem I was having

I'll update my scripts when I get home and see how they fair - although I've been doing some googling on the subject of MySQL performance and monitoring and the more I learn the more I feel that these simple queries aren't the issue... needless to say it's worth making these selects more efficient.

The following document looks pretty handy and should enable me to get a handle on what's really happening: http://www.scribd.com/doc/264633/Monitoring-and-Logging-a-MySQL-Database-Server

Oh, and littlejon - I have indexes on each table but not multiple ones - each query uses the indexes... My indexes aren't sequential tho because some rows get deleted - is this an issue?

Cheers, p.