Long Query Load On First Visit

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
WanamakerStudios
Forum Commoner
Posts: 65
Joined: Thu Nov 30, 2006 7:35 am

Long Query Load On First Visit

Post by WanamakerStudios »

feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


I am building a proof of concept site but am having a bit of an issue with the MySQL end of things on the initial page load. You can visit http://www.chriscarsite.com/used/dodge to see it in action. I've included my PHP/MySQL coding for your review to see if I missed anything. Any help would be greatly appreciated!

Code: Select all

if(empty($_GET['p']) OR $_GET['p'] == "1"){ $PAGE['current'] = "0"; }else{$PAGE['current'] = $_GET['p'];}
			$invLimit = "25";
			$invStart = ($PAGE['current']*$invLimit)-$invLimit;
			if($invStart < 0){$invStart = "0";}					
		
			$dbConnect		=	mysql_connect("localhost", "XXXXXX", "XXXXXX");
			$dbSelect		=	mysql_select_db("ndn_chriscarsite");
			
			$makeQuery		=	"
				SELECT cp.CIDCARP AS MAKEID
				FROM `carparts` cp
				WHERE cp.CDESCRIPTION = '" . AddSpaces(strtoupper($_GET['make'])) ."'
				";

			$makeFetch		=	mysql_query($makeQuery);
			$makeResult		=	mysql_fetch_assoc($makeFetch);

			$modelQueryS	=	"
				SELECT uv.CSTOCKNUM AS STOCKNUM, uv.CYEAR AS YEAR, CONCAT(cp.CDESCRIPTION, ' ', uv.CMODEL) AS VEHICLE, uv.CPRICE AS PRICE, uv.CVIN AS VIN, uv.CMILEAGE AS MILEAGE, cp.CDESCRIPTION AS MAKE, uv.CMODEL AS MODEL, CONCAT(uv.CCOLOR, ', ', bs.CDESCRIPTION, ', ', tr.CDESCRIPTION, ', ', o3.DESCRIPTION, ', ', o4.DESCRIPTION, ', ', o5.DESCRIPTION, ', ', o6.DESCRIPTION, ', ', o7.DESCRIPTION) AS DESCRIPTION			
				";
			
			$modelQueryF	=	"
				FROM `usedvehicles` uv
				";
			
			$modelQueryJ	=	"
				LEFT JOIN `carparts` cp
				ON cp.CIDCARP = uv.CMAKE
				LEFT JOIN `carparts` bs
				ON bs.CIDCARP = uv.CBODYTYPE
				LEFT JOIN `carparts` tr
				ON tr.CIDCARP = uv.CTRANSMISS				
				LEFT JOIN `options` o3
				ON o3.CIDOPTI = uv.O3
				LEFT JOIN `options` o4
				ON o4.CIDOPTI = uv.O4	
				LEFT JOIN `options` o5
				ON o5.CIDOPTI = uv.O5
				LEFT JOIN `options` o6
				ON o6.CIDOPTI = uv.O6				
				LEFT JOIN `options` o7
				ON o7.CIDOPTI = uv.O7
				LEFT JOIN `options` o8
				ON o8.CIDOPTI = uv.O8
				";
			
			$modelQueryW	=	"
				WHERE uv.CMAKE = '" . addslashes($makeResult['MAKEID']) ."'
				AND uv.CYEAR >= '1990'
				AND uv.CCOLOR <> ''
				";
			
			if(!empty($_GET['modPrice'])) {
				$modelQueryW	.=	"
					AND uv.CPRICE <= '". $_GET['modPrice'] ."'
					AND uv.CPRICE > '0'
					";				
			} else {
				$modelQueryW	.=	"
					AND uv.CPRICE > '0'
					AND uv.CPRICE < '1000000'
					";			
			}
			
			if(!empty($_GET['modMileage'])) {
				$modelQueryW	.=	"
					AND uv.CMILEAGE <= '". $_GET['modMileage'] ."'
					AND uv.CMILEAGE > '0'
					";				
			} else {
				$modelQueryW	.=	"
					AND uv.CMILEAGE > '0'
					AND uv.CMILEAGE < '1000000'
					";			
			}			
				
			if(!empty($_GET['sortby'])) {
			$modelQueryO	= "
				ORDER BY ". $_GET['sortby'] ." ". $_GET['order'] ."
				";	
			} else {
			$modelQueryO	= "
				ORDER BY CYEAR DESC
				";	
			}
				
			if(empty($invStart)) {
				$modelQueryL	= " LIMIT ". $invLimit ."";
			} else {
				$modelQueryL	= " LIMIT ". $invStart ." , ". $invLimit ."";
			}	

			$modelQuery = $modelQueryS ." ". $modelQueryF ." ". $modelQueryJ ." ". $modelQueryW ." ". $modelQueryO ." ". $modelQueryL;
			##echo $modelQuery;
			$modelFetch		=	mysql_query($modelQuery);
			
			## COLUMN HEADERS
			$column = array("CYEAR" => "Year", "CMODEL" => "Model", "CPRICE" => "Price", "CMILEAGE" => "Mileage");

			## COUNT QUERY SELECT
			$countQueryS	=	"
				SELECT COUNT(*) AS TOTAL
				";
			
			## COUNT QUERY FROM
			$countQueryF	=	$modelQueryF;
			
			## COUNT QUERY WHERE
			$countQueryW	=	$modelQueryW;
			
			## COUNT QUERY LIMIT
			$countQueryL	=	"LIMIT 5000";
			
			## COMBINE COUNT QUERY STRINGS
			$countQuery		=	$countQueryS ." ". $countQueryF ." ". $countQueryW." ". $countQueryL;
			
			##echo $countQuery;
			
			##echo $countQuery;			
			$countFetch = mysql_query($countQuery);
			$countCount = mysql_fetch_assoc($countFetch);
			$modelCount = $countCount['TOTAL'];

feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
Begby
Forum Regular
Posts: 575
Joined: Wed Dec 13, 2006 10:28 am

Post by Begby »

Your query needs some work. Echo the SQL to the screen instead of running the query, then paste it into an SQL editor and run EXPLAIN on it to see if your indexes are setup correctly.

Also your page is open to SQL injection.
WanamakerStudios
Forum Commoner
Posts: 65
Joined: Thu Nov 30, 2006 7:35 am

Post by WanamakerStudios »

How is this open to SQL Injection?
WanamakerStudios
Forum Commoner
Posts: 65
Joined: Thu Nov 30, 2006 7:35 am

Post by WanamakerStudios »

When I did the EXPLAIN, this is what I got ...

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE uv ref CYEAR,CMILEAGE,CCOLOR,CPRICE,CMAKE CMAKE 3 const 131980 Using where; Using filesort
1 SIMPLE cp ref CIDCARP CIDCARP 2 ndn_chriscarsite.uv.CMAKE 1
1 SIMPLE bs ref CIDCARP CIDCARP 2 ndn_chriscarsite.uv.CBODYTYPE 1
1 SIMPLE tr ref CIDCARP CIDCARP 2 ndn_chriscarsite.uv.CTRANSMISS 1
1 SIMPLE o3 ref CIDOPTI CIDOPTI 2 ndn_chriscarsite.uv.O3 1
1 SIMPLE o4 ref CIDOPTI CIDOPTI 2 ndn_chriscarsite.uv.O4 1
1 SIMPLE o5 ref CIDOPTI CIDOPTI 2 ndn_chriscarsite.uv.O5 1
1 SIMPLE o6 ref CIDOPTI CIDOPTI 2 ndn_chriscarsite.uv.O6 1
1 SIMPLE o7 ref CIDOPTI CIDOPTI 2 ndn_chriscarsite.uv.O7 1
1 SIMPLE o8 ref CIDOPTI CIDOPTI 2 ndn_chriscarsite.uv.O8 1 Using index
Post Reply