Part 2 Trade Arcade Pro Speed Up/Reduce 80-90% Query Overhead On Average

lfhost · 06-15-2011, 11:22 AM

As a followup to my first thread of improving listGames() function. Here is an improvement to

topPlayed.html, newest.html and categories. This is a little more involved than the other thread, but is worth it in the end.

First I will show the stat tests of queries, memory and script execution

Default = Vanilla install of Tap, Updated = new queries used.

topPlayed.html

Default

PHP Code:


		
			
Initial: 711168bytes | Final: 898152bytes | Peak: 1068320bytes | 22 queries used

Script Execution Time: 0.00982403755188

Updated

PHP Code:


		
			
Initial: 711416bytes | Final: 898368bytes | Peak: 1068568bytes | 6 queries used

Script Execution Time: 0.00626993179321

newest.html

Default

PHP Code:


		
			
Initial: 711192bytes | Final: 898344bytes | Peak: 1068408bytes | 22 queries used

Script Execution Time: 0.00947499275208

Updated

PHP Code:


		
			
Initial: 711016bytes | Final: 898192bytes | Peak: 1068200bytes | 6 queries used

Script Execution Time: 0.00607585906982

Categories

Default

PHP Code:


		
			
Initial: 713160bytes | Final: 901464bytes | Peak: 1070328bytes | 23 queries used

Script Execution Time: 0.00823092460632

Updated

PHP Code:


		
			
Initial: 712776bytes | Final: 901104bytes | Peak: 1069968bytes | 8 queries used

Script Execution Time: 0.00677704811096

To do this, open includes/engine.php

find

PHP Code:


		
			
$sql="SELECT * FROM games WHERE gameStatus=1 AND gameCat='$id' ORDER BY gamePlaysTotal LIMIT $start,$limit";

replace with

PHP Code:


		
			
/**edited by lfhost**/

    //$sql="SELECT * FROM games WHERE gameStatus=1 AND gameCat='$id' ORDER BY gamePlaysTotal LIMIT $start,$limit";

    $sql="SELECT games.*,catName FROM games LEFT OUTER JOIN categories ON catId = gameCat WHERE gameStatus=1 AND gameCat='$id' ORDER BY gamePlaysTotal LIMIT $start,$limit";

find

PHP Code:


		
			
$totalPages=ceil(mysql_num_rows(mysql_query("SELECT * FROM games WHERE gameStatus=1 AND gameCat='$id' ORDER BY gamePlaysTotal"))/$limit);

replace with

PHP Code:


		
			
/**edited by lfhost**/

//$totalPages=ceil(mysql_num_rows(mysql_query("SELECT * FROM games WHERE gameStatus=1 AND gameCat='$id' ORDER BY gamePlaysTotal"))/$limit);

$totalPages=ceil(mysql_num_rows(mysql_query("SELECT games.*,catName FROM games LEFT OUTER JOIN categories ON catId = gameCat WHERE gameStatus=1 AND gameCat='$id' ORDER BY gamePlaysTotal"))/$limit);

find

PHP Code:


		
			
$sql="SELECT * FROM games WHERE gameName LIKE '%$q%' OR  gameDesc LIKE '%$q%' OR gameTags LIKE '%$q%' AND gameStatus=1 ORDER BY gamePlaysTotal LIMIT $start,$limit";

replace with

PHP Code:


		
			
/**edited by lfhost**/

//$sql="SELECT * FROM games WHERE gameName LIKE '%$q%' OR  gameDesc LIKE '%$q%' OR gameTags LIKE '%$q%' AND gameStatus=1 ORDER BY gamePlaysTotal LIMIT $start,$limit";

$sql="SELECT games.*,catName FROM games LEFT OUTER JOIN categories ON catId = gameCat WHERE gameName LIKE '%$q%' OR  gameDesc LIKE '%$q%' OR gameTags LIKE '%$q%' AND gameStatus=1 ORDER BY gamePlaysTotal LIMIT $start,$limit";

find

PHP Code:


		
			
$sql="SELECT * FROM games WHERE gameStatus=1 ORDER BY gamePlaysTotal DESC LIMIT $start,$limit";

replace with

PHP Code:


		
			
/**edited by lfhost**/

//$sql="SELECT * FROM games WHERE gameStatus=1 ORDER BY gamePlaysTotal DESC LIMIT $start,$limit";

$sql="SELECT games.*,catName FROM games LEFT OUTER JOIN categories ON catId = gameCat WHERE gameStatus=1 ORDER BY gamePlaysTotal DESC LIMIT $start,$limit";

find

PHP Code:


		
			
$sql="SELECT * FROM games WHERE gameStatus=1 ORDER BY gameAdded DESC LIMIT $start,$limit";

replace with

PHP Code:


		
			
/**edited by lfhost**/

//$sql="SELECT * FROM games WHERE gameStatus=1 ORDER BY gameAdded DESC LIMIT $start,$limit";

$sql="SELECT games.*,catName FROM games LEFT OUTER JOIN categories ON catId = gameCat WHERE gameStatus=1 ORDER BY gameAdded DESC LIMIT $start,$limit";

find

PHP Code:


		
			
$sqlTotal="SELECT * FROM games WHERE gameStatus=1";

replace with

PHP Code:


		
			
/**edited by lfhost**/

//$sqlTotal="SELECT * FROM games WHERE gameStatus=1";

$sqlTotal="SELECT games.*,catName FROM games LEFT OUTER JOIN categories ON catId = gameCat WHERE gameStatus=1";

open /templates/[Your Template]/browse.php
find

PHP Code:


		
			
$gameCatName=catId2Name($gameCat);

replace with

PHP Code:


		
			
/**edited by lfhost**/

    //$gameCatName=catId2Name($gameCat);

    $gameCatName=$row['catName'];

Save and upload the new updated files to

/includes/engine.php
/templates/[Your Template]/browse.php

JanArve · 06-15-2011, 05:34 PM

Good call, I wasn't aware that using an outer join in your query could do that much difference.. Learned something new today too!

lfhost · 06-15-2011, 07:09 PM

Yeah, the outer joins are useful, you just have to make sure you select the fields you want and not just use * as that would be too much overhead and a waste of resources. Here were just pull the game fields and the catname.

So its much lighter than pulling the query on every record.

if you dont want to use a join, you could query the cat DB once, then from the array, pull back the category name by using the cat id as the array index. As the category table is small then the array wouldnt be too heavy.

Theres more than one way to approach it, I just prefer joining and let mysql do the work.
Caching the SQL would be even better :P

JanArve · 06-16-2011, 03:26 AM

Indeed, another way to do it would be sub queries which I've been a fan of for a while.

http://dev.mysql.com/doc/refman/5.1/en/subqueries.html

It would be interesting to see if outer join or sub queries are faster if you are up for a challenge

lfhost · 06-16-2011, 06:14 AM

Hi Jan,

Im not the biggest fan of subqueries as you cant pull fields from the subqueries table i.e catName you can only really compare the data. Unless im mistaken.

But here is the result with out the catName pulled back on the homepage through listGame

PHP Code:


		
			
Initial: 705800bytes | Final: 899048bytes | Peak: 1062624bytes | 8 queries used
Script Execution Time: 0.0070960521698

Category Page

PHP Code:


		
			
Initial: 712592bytes | Final: 900808bytes | Peak: 1047568bytes | 8 queries used
Script Execution Time: 0.00722503662109

Compared to (From Above with Join)

PHP Code:


		
			
Initial: 712776bytes | Final: 901104bytes | Peak: 1069968bytes | 8 queries used 
Script Execution Time: 0.00677704811096

dmm2020 · 06-16-2011, 09:12 AM

Now this is what I like to see and has been missing for some time. I don't care whose script this is, posts like this help make us all better script writers. Thanks for your renewed efforts Paul.

lfhost · 06-16-2011, 09:16 AM

@dmm yes, sharing and comparing always helps improves ones skill set. Im always learning as well as its always ongoing.

Im always open to helping out our community.

06-15-2011, 11:22 AM	#1 (permalink)
lfhost Senior Member Join Date: Aug 2008 Posts: 270	Part 2 Trade Arcade Pro Speed Up/Reduce 80-90% Query Overhead On Average As a followup to my first thread of improving listGames() function. Here is an improvement to topPlayed.html, newest.html and categories. This is a little more involved than the other thread, but is worth it in the end. First I will show the stat tests of queries, memory and script execution Default = Vanilla install of Tap, Updated = new queries used. topPlayed.html Default PHP Code: `Initial: 711168bytes \| Final: 898152bytes \| Peak: 1068320bytes \| 22 queries used Script Execution Time: 0.00982403755188` Updated PHP Code: `Initial: 711416bytes \| Final: 898368bytes \| Peak: 1068568bytes \| 6 queries used Script Execution Time: 0.00626993179321` newest.html Default PHP Code: `Initial: 711192bytes \| Final: 898344bytes \| Peak: 1068408bytes \| 22 queries used Script Execution Time: 0.00947499275208` Updated PHP Code: `Initial: 711016bytes \| Final: 898192bytes \| Peak: 1068200bytes \| 6 queries used Script Execution Time: 0.00607585906982` Categories Default PHP Code: `Initial: 713160bytes \| Final: 901464bytes \| Peak: 1070328bytes \| 23 queries used Script Execution Time: 0.00823092460632` Updated PHP Code: `Initial: 712776bytes \| Final: 901104bytes \| Peak: 1069968bytes \| 8 queries used Script Execution Time: 0.00677704811096` To do this, open includes/engine.php find PHP Code: `$sql="SELECT * FROM games WHERE gameStatus=1 AND gameCat='$id' ORDER BY gamePlaysTotal LIMIT $start,$limit";` replace with PHP Code: `/edited by lfhost/ //$sql="SELECT * FROM games WHERE gameStatus=1 AND gameCat='$id' ORDER BY gamePlaysTotal LIMIT $start,$limit"; $sql="SELECT games.,catName FROM games LEFT OUTER JOIN categories ON catId = gameCat WHERE gameStatus=1 AND gameCat='$id' ORDER BY gamePlaysTotal LIMIT $start,$limit";` find PHP Code: `$totalPages=ceil(mysql_num_rows(mysql_query("SELECT FROM games WHERE gameStatus=1 AND gameCat='$id' ORDER BY gamePlaysTotal"))/$limit);` replace with PHP Code: `/edited by lfhost/ //$totalPages=ceil(mysql_num_rows(mysql_query("SELECT * FROM games WHERE gameStatus=1 AND gameCat='$id' ORDER BY gamePlaysTotal"))/$limit); $totalPages=ceil(mysql_num_rows(mysql_query("SELECT games.,catName FROM games LEFT OUTER JOIN categories ON catId = gameCat WHERE gameStatus=1 AND gameCat='$id' ORDER BY gamePlaysTotal"))/$limit);` find PHP Code: `$sql="SELECT FROM games WHERE gameName LIKE '%$q%' OR gameDesc LIKE '%$q%' OR gameTags LIKE '%$q%' AND gameStatus=1 ORDER BY gamePlaysTotal LIMIT $start,$limit";` replace with PHP Code: `/edited by lfhost/ //$sql="SELECT * FROM games WHERE gameName LIKE '%$q%' OR gameDesc LIKE '%$q%' OR gameTags LIKE '%$q%' AND gameStatus=1 ORDER BY gamePlaysTotal LIMIT $start,$limit"; $sql="SELECT games.,catName FROM games LEFT OUTER JOIN categories ON catId = gameCat WHERE gameName LIKE '%$q%' OR gameDesc LIKE '%$q%' OR gameTags LIKE '%$q%' AND gameStatus=1 ORDER BY gamePlaysTotal LIMIT $start,$limit";` find PHP Code: `$sql="SELECT FROM games WHERE gameStatus=1 ORDER BY gamePlaysTotal DESC LIMIT $start,$limit";` replace with PHP Code: `/edited by lfhost/ //$sql="SELECT * FROM games WHERE gameStatus=1 ORDER BY gamePlaysTotal DESC LIMIT $start,$limit"; $sql="SELECT games.,catName FROM games LEFT OUTER JOIN categories ON catId = gameCat WHERE gameStatus=1 ORDER BY gamePlaysTotal DESC LIMIT $start,$limit";` find PHP Code: `$sql="SELECT FROM games WHERE gameStatus=1 ORDER BY gameAdded DESC LIMIT $start,$limit";` replace with PHP Code: `/edited by lfhost/ //$sql="SELECT * FROM games WHERE gameStatus=1 ORDER BY gameAdded DESC LIMIT $start,$limit"; $sql="SELECT games.,catName FROM games LEFT OUTER JOIN categories ON catId = gameCat WHERE gameStatus=1 ORDER BY gameAdded DESC LIMIT $start,$limit";` find PHP Code: `$sqlTotal="SELECT FROM games WHERE gameStatus=1";` replace with PHP Code: `/edited by lfhost/ //$sqlTotal="SELECT * FROM games WHERE gameStatus=1"; $sqlTotal="SELECT games.,catName FROM games LEFT OUTER JOIN categories ON catId = gameCat WHERE gameStatus=1";` open /templates/[Your Template]/browse.php find PHP Code: `$gameCatName=catId2Name($gameCat);` replace with PHP Code: `/edited by lfhost/ //$gameCatName=catId2Name($gameCat); $gameCatName=$row['catName'];` Save and upload the new updated files to /includes/engine.php /templates/[Your Template]/browse.php __________________ Thanks Paul I code for FOOOOOD Your coding errors make me Orgasm LOL Arcade Themes and Mods Sniperz Login with Fb and submit your GAMES* ABC Arcade \| Youlose \| Wicked Games \| GirlyCade \| Gurl Arcade \| Zombie Arcade

06-15-2011, 05:34 PM	#2 (permalink)
JanArve phpAS Staff Arcade Warrior Join Date: Jun 2008 Posts: 798 MarketScore (6)	Good call, I wasn't aware that using an outer join in your query could do that much difference.. Learned something new today too! __________________ -= Check it out: Php Arcade Script =- -= Php Arcade Script Templates: Neon Blue \| Neon Green \| Neon Pink \| Dark Charred \| Colorful =-

06-15-2011, 07:09 PM	#3 (permalink)
lfhost Senior Member Join Date: Aug 2008 Posts: 270	Yeah, the outer joins are useful, you just have to make sure you select the fields you want and not just use * as that would be too much overhead and a waste of resources. Here were just pull the game fields and the catname. So its much lighter than pulling the query on every record. if you dont want to use a join, you could query the cat DB once, then from the array, pull back the category name by using the cat id as the array index. As the category table is small then the array wouldnt be too heavy. Theres more than one way to approach it, I just prefer joining and let mysql do the work. Caching the SQL would be even better :P __________________ Thanks Paul I code for FOOOOOD Your coding errors make me Orgasm LOL Arcade Themes and Mods Sniperz Login with Fb and submit your GAMES ABC Arcade \| Youlose \| Wicked Games \| GirlyCade \| Gurl Arcade \| Zombie Arcade

06-16-2011, 03:26 AM	#4 (permalink)
JanArve phpAS Staff Arcade Warrior Join Date: Jun 2008 Posts: 798 MarketScore (6)	Indeed, another way to do it would be sub queries which I've been a fan of for a while. http://dev.mysql.com/doc/refman/5.1/en/subqueries.html It would be interesting to see if outer join or sub queries are faster if you are up for a challenge __________________ -= Check it out: Php Arcade Script =- -= Php Arcade Script Templates: Neon Blue \| Neon Green \| Neon Pink \| Dark Charred \| Colorful =-

06-16-2011, 06:14 AM	#5 (permalink)
lfhost Senior Member Join Date: Aug 2008 Posts: 270	Hi Jan, Im not the biggest fan of subqueries as you cant pull fields from the subqueries table i.e catName you can only really compare the data. Unless im mistaken. But here is the result with out the catName pulled back on the homepage through listGame PHP Code: `Initial: 705800bytes \| Final: 899048bytes \| Peak: 1062624bytes \| 8 queries used Script Execution Time: 0.0070960521698` Category Page PHP Code: `Initial: 712592bytes \| Final: 900808bytes \| Peak: 1047568bytes \| 8 queries used Script Execution Time: 0.00722503662109` Compared to (From Above with Join) PHP Code: `Initial: 712776bytes \| Final: 901104bytes \| Peak: 1069968bytes \| 8 queries used Script Execution Time: 0.00677704811096` __________________ Thanks Paul I code for FOOOOOD Your coding errors make me Orgasm LOL Arcade Themes and Mods Sniperz Login with Fb and submit your GAMES ABC Arcade \| Youlose \| Wicked Games \| GirlyCade \| Gurl Arcade \| Zombie Arcade

06-16-2011, 09:12 AM	#6 (permalink)
dmm2020 Arcade Elite Join Date: Apr 2009 Location: Cheyenne, WY Posts: 1,021	Now this is what I like to see and has been missing for some time. I don't care whose script this is, posts like this help make us all better script writers. Thanks for your renewed efforts Paul. __________________ Military Combat Games - come check my new website out!!! - The Casino Arcade - DMSOFT-WY.COM - Webmaster Blog Site

06-16-2011, 09:16 AM	#7 (permalink)
lfhost Senior Member Join Date: Aug 2008 Posts: 270	@dmm yes, sharing and comparing always helps improves ones skill set. Im always learning as well as its always ongoing. Im always open to helping out our community. __________________ Thanks Paul I code for FOOOOOD Your coding errors make me Orgasm LOL Arcade Themes and Mods Sniperz Login with Fb and submit your GAMES ABC Arcade \| Youlose \| Wicked Games \| GirlyCade \| Gurl Arcade \| Zombie Arcade

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Trade Arcade Pro Speed Up/Reduce 80-90% Query Overhead On Average	lfhost	TradeArcadePro	5	06-21-2011 11:20 AM