very simple code - way 2 slow at times

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

Post Reply
malcolmboston
DevNet Resident
Posts: 1826
Joined: Tue Nov 18, 2003 1:09 pm
Location: Middlesbrough, UK

very simple code - way 2 slow at times

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
User avatar
mudkicker
Forum Contributor
Posts: 479
Joined: Wed Jul 09, 2003 6:11 pm
Location: Istanbul, TR
Contact:

Post 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..
Breckenridge
Forum Commoner
Posts: 62
Joined: Thu Sep 09, 2004 11:10 pm
Location: Breckenridge, Colorado

Post 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:
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post 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.
User avatar
Calimero
Forum Contributor
Posts: 310
Joined: Thu Jan 22, 2004 6:54 pm
Location: Milky Way

...

Post 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.
User avatar
BDKR
DevNet Resident
Posts: 1207
Joined: Sat Jun 08, 2002 1:24 pm
Location: Florida
Contact:

Post 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
Findus
Forum Newbie
Posts: 7
Joined: Tue Apr 20, 2004 8:47 am

Post 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.
Post Reply