Page 1 of 1

very simple code - way 2 slow at times

Posted: Sun Oct 03, 2004 5:47 am
by malcolmboston
ok i have this code running

Code: Select all

<?php
$time = time();
$query = "SELECT id, name, views, auction_start, auction_end FROM items WHERE views >= 1 AND auction_end < $time ORDER BY views DESC LIMIT 5";
$result = mysql_query($query) or die (mysql_error());
$num = mysql_num_rows($result);
if ($num >= 1)
{
while ($array = mysql_fetch_array($result))
{
   print {$array['id']};
   print {$array['name']};
   print {$array['views']};
}
}
?>
now this code is fine, i get no errors or nothing.

however, at times (mainly when i have not "used" the browser in +10 mins, then the parse time solely for this function can get upto 43 secs (usually only goes to around 17 when its going badly)

usually this parses at 0.09 - 0.22, i use microtime function to verify this and have it running in all code, whilst creating the site to view bottle necks and it is definitely down to this function.

So im wondering why this is the case?

I was thinking because it is searching a 50k item database, but i have other queries searching that database also that return super-fast.

Also i looked into MySQL caching and established that the resultset it always the same so it cant be that.

Does anyone have any ideas at all??

Thanks
Mal

Posted: Sun Oct 03, 2004 9:08 am
by feyd
are all these fields you are selecting indices of some sort? If not, I believe the server starts tossing it's cache of non indices after a period of nonuse.

Posted: Sun Oct 03, 2004 10:37 am
by mudkicker
maybe with some tweaking you can get it faster?

instead using mysql_fetch_Array you can use mysql_fetch_assoc and write

...WHERE views > 0 ... instead >=1 ??

these both could spped up a bit i think.. tell me if i'm wrong..

Posted: Sun Oct 03, 2004 12:42 pm
by Breckenridge
I have to ask this: Are you using a shared server to run this code?

Unless you are selecting from 10000s at a time your code should fetch results real fast.

Delays might be related to server load issues, just keep that in mind

:roll:

Posted: Mon Oct 04, 2004 1:23 pm
by Christopher
The first thing I would want to know is do you have indexes for the views and auction_end columns? If you don't, add indexes and your query will instantly be much, much faster.

...

Posted: Mon Oct 04, 2004 1:27 pm
by Calimero
Just a suggestion,

Use now() inside mysql to get current time,
instead of using time() in php and passing it as a variable.

For other problems, you gave little info.

Posted: Mon Oct 18, 2004 12:08 pm
by BDKR
MySQL gives you the ability to get an idea of how the engine is going to deal with a query by putting the 'EXPLAIN' keyword at the front of it. You may also want to start up the slow query log if you have the ability to do so.

Cheers,
BDKR

Posted: Tue Oct 19, 2004 3:36 am
by Findus
Do you print 3 fields from 50k rows to the browser? If so, that is really slow.
Cut the print part and check.
Echoing lots of data to the browser is really slow.
I have been working with some huge scripts lately, one that printed over 300000 lines to the browsers, and the difference from printing them out and not then was huge. In such cases you only use the printing for debug i assume, thats why i use it at least.

Searching a 50k rows table with mysql should be really quick, thats nothing for a table. Unless, as previously mentioned, you are on shared hosting where it could be slow due to many sharing the db.