---

Hi

Undskyld for ubelejligheden at mit spoergsmaal er paa egelsk, men
da jeg har skrevet saa meget og skrev det ind paa et engelsk
forum foerst ville jeg ikke ogsaa skrive det paa dansk.

QUESTION:
Why is the code running so slow and what can I do to improve it?

THE CODE:

$currentTable = "SS_products";
$q1 = db_query("SELECT FLOOR(RAND() * COUNT(*)) AS rand_row FROM
".
$currentTable) or die (db_error());

$row1 = db_fetch_row($q1);
$q2 = db_query("SELECT categoryID, name, default_picture FROM ".
$currentTable." WHERE categoryID=7 Limit $row1[0], 1") or die
(db_error());

Background Infomation on the Code:
I have a website where I use a shopping cart system called
ShopScript Premium. This system works more then fine with my
current webhost BlueHost. But a problem arises when I add
customized the code.

I have added to the website home page that every time the browser
is refreshed 10 new random products will be the customers. The
ShopScript system only offers products which are to be changed by
the administrator manually. When the code for the random product
is executed the website runs considerably slower then without the
random products, although sometimes and does run with no problems
and fast speed for a minute or so. Even if the user is using the
other pages the website is still slow. I suspect this has to do
with the fact that the ShopScript system uses a smarty engine,
although that does not fully explain why.

In my database the table called has 1373 rows. I use the code
above to randomly select one of the rows to be viewed on the
website. According to my log on mySQL slow queries, the above
mentioned code, is the source of the problems. The log states
that the code uses 2 to 4 seconds query time. My webhost does not
allow any single process taking more than 30 CPU seconds to run
or if the total processes take more than 40 CPU seconds in any 60
second window to avoid server lockups. Such an event triggers a
CPU Quota/Suspension Error, where my website is accessable for 5
to 10 minutes. This is a big problem as I lose customers this
way.

--
Vil du lære at kode HTML, XHTML, CSS, SSI, ASP eller ASP.NET?
- Pædagogiske tutorials på dansk
- Kom godt i gang med koderne
KLIK HER! => http://www.html.dk/tutorials

---

Heylow wrote:
> Hi
>
> Undskyld for ubelejligheden at mit spoergsmaal er paa egelsk, men
> da jeg har skrevet saa meget og skrev det ind paa et engelsk
> forum foerst ville jeg ikke ogsaa skrive det paa dansk.
>
> QUESTION:
> Why is the code running so slow and what can I do to improve it?
>
> THE CODE:
>
> $currentTable = "SS_products";
> $q1 = db_query("SELECT FLOOR(RAND() * COUNT(*)) AS rand_row FROM
> ".
> $currentTable) or die (db_error());
>
> $row1 = db_fetch_row($q1);
> $q2 = db_query("SELECT categoryID, name, default_picture FROM ".
> $currentTable." WHERE categoryID=7 Limit $row1[0], 1") or die
> (db_error());

Prøv med

SELECT ... FROM table WHERE ... ORDER BY RAND() LIMIT 7


>
> Background Infomation on the Code:
> I have a website where I use a shopping cart system called
> ShopScript Premium. This system works more then fine with my
> current webhost BlueHost. But a problem arises when I add
> customized the code.
>
> I have added to the website home page that every time the browser
> is refreshed 10 new random products will be the customers. The
> ShopScript system only offers products which are to be changed by
> the administrator manually. When the code for the random product
> is executed the website runs considerably slower then without the
> random products, although sometimes and does run with no problems
> and fast speed for a minute or so. Even if the user is using the
> other pages the website is still slow. I suspect this has to do
> with the fact that the ShopScript system uses a smarty engine,
> although that does not fully explain why.
>
> In my database the table called has 1373 rows. I use the code
> above to randomly select one of the rows to be viewed on the
> website. According to my log on mySQL slow queries, the above
> mentioned code, is the source of the problems. The log states
> that the code uses 2 to 4 seconds query time. My webhost does not
> allow any single process taking more than 30 CPU seconds to run
> or if the total processes take more than 40 CPU seconds in any 60
> second window to avoid server lockups. Such an event triggers a
> CPU Quota/Suspension Error, where my website is accessable for 5
> to 10 minutes. This is a big problem as I lose customers this
> way.
>

---

Hej Martin

> Prøv med
>
> SELECT ... FROM table WHERE ... ORDER BY RAND() LIMIT 7
>
Jeg har faktisk brugt ORDER BY RAND() foer, inden Jeg brugte
nuvaerende kode, men den metode brugte meget laengere tid.



--
Vil du lære at kode HTML, XHTML, CSS, SSI, ASP eller ASP.NET?
- Pædagogiske tutorials på dansk
- Kom godt i gang med koderne
KLIK HER! => http://www.html.dk/tutorials

---

Heylow wrote:
> Hej Martin
>
>> Prøv med
>>
>> SELECT ... FROM table WHERE ... ORDER BY RAND() LIMIT 7
>>
> Jeg har faktisk brugt ORDER BY RAND() foer, inden Jeg brugte
> nuvaerende kode, men den metode brugte meget laengere tid.

Hvor mange produkter har du i kategori 7 ?

Anyways, det som du gør nu, er at du henter 1 random produkt, men hvis
du skal bruge 7, så skal du loope denne 7 gange... klart det tager langtid

Men hvis du virkelig har så mange produkter i kategori 7, så prøv
følgende - den er fundet inde på mysql bug report

SELECT * FROM Table T JOIN (SELECT MAX(ID) AS ID FROM Table) AS x ON
T.ID >= FLOOR(x.ID*RAND()) LIMIT 10;

Denne finder 10 ID'er

---

"Heylow" skrev
>
> Why is the code running so slow and what can I do to improve it?
>
> $currentTable = "SS_products";
> $q1 = db_query("SELECT FLOOR(RAND() * COUNT(*)) AS rand_row FROM
> ".
> $currentTable) or die (db_error());
>
Kik på den her artikel
Skribenten forslår at man laver sin randomizer i php i stedet

> $q2 = db_query("SELECT categoryID, name, default_picture FROM ".
> $currentTable." WHERE categoryID=7 Limit $row1[0], 1") or die
> (db_error());
>
Jeg er forholdsvis ny udi php :)
Men ...

Vil ovenstående kode ikke genere en sql kode ala
SELECT categoryID, name, default_picture FROM mytable WHERE categoryID=7
Limit $row1[0], 1

I stedet for den korrekte - fx
SELECT categoryID, name, default_picture FROM mytable WHERE categoryID=7
Limit 87, 1

Jeg mener det burde være

$q2 = db_query("SELECT categoryID, name, default_picture FROM ".
$currentTable." WHERE categoryID=7 Limit " . $row1[0] . ", 1") or die
(db_error());

?

- Peter

---

Heylow skrev:

> $q1 = db_query("SELECT FLOOR(RAND() * COUNT(*)) AS rand_row FROM
> ".
> $currentTable) or die (db_error());

Her tæller du ALLE rækker...

> $row1 = db_fetch_row($q1);
> $q2 = db_query("SELECT categoryID, name, default_picture FROM ".
> $currentTable." WHERE categoryID=7 Limit $row1[0], 1") or die
> (db_error());

.... men du søger kun dem, hvor categoryID=7.

Så hvis categoryID kun er 7 for en lille del af basen, vil din random
værdi ofte være højere end antallet af rækker med categoryID=7.

Det undrer mig nu, at det skulle tage så lang tid. Kalder du koden i et
loop?

M.v.h.

Jonathan

--
Er din email vigtig? Er du træt af, at din hjemmeside er nede?
Stabilt webhotel på redundant setup med daglig backup.
POP3, IMAP, PHP, JSP, Java, Perl, Python, Telnet, SSH, Cron-jobs m.v.
http://www.jsp-hotel.dk/