Efficient php/mysql Coding

[Andy_nexus]

Hey all,
need some advice. which would you say was more efficient i've checked the execution times of both scripts and they average out about the same tbh, just wondered if one way was a 'better' way of doing it.

Code 1

<?php
$url_home = 'url';

$top_10 = "SELECT p.products_id, p.products_status, p.products_ordered, pd.products_id, pd.products_name, p.manufacturers_id
FROM products AS p, products_description AS pd
WHERE p.products_id = pd.products_id
AND p.manufacturers_id = '".$brID."'
AND p.products_status = '1'
ORDER BY p.products_ordered DESC 
LIMIT 10";

$result = mysql_query($top_10) or die(mysql_error());

$manufacturer = "SELECT manufacturers_name 
FROM manufacturers
WHERE manufacturers_id = '".$brID."'";

$man_q = mysql_query($manufacturer) or die(mysql_error());
$man_r = mysql_fetch_array($man_q);
$b_name = $man_r['manufacturers_name'];

echo '<div class="blah">'.$b_name.' Sellers</div>
	  <ul>';
while($top_10_r = mysql_fetch_array($result)){

	$prod_id = $top_10_r['products_id'];
	$product_buy_link = $url_home .'/index.php?main_page=product_info&amp;products_id='. $prod_id;
	$name_sml = str_replace($b_name,"",$top_10_r['products_name']);
	$top_10_r = str_replace("&","&amp;",$top_10_r);
	if (strlen($name_sml) > 45 ) { 
 	 $name_sml = substr($name_sml, 0, 42) . "..."; 
	} 
	echo'<li> <a href="'.$product_buy_link.'">' .$name_sml. '</a></li>';
	}
	echo '</ul>';
?>

Code 2

<?php
$url_home = 'url';

$top_10 = "SELECT p.products_id, p.products_status, p.products_ordered, pd.products_id, pd.products_name, p.manufacturers_id, m.manufacturers_id, m.manufacturers_name
FROM products AS p, products_description AS pd, manufacturers AS m
WHERE p.products_id = pd.products_id
AND p.manufacturers_id = '".$brID."'
AND p.manufacturers_id = m.manufacturers_id
AND p.products_status = '1'
ORDER BY p.products_ordered DESC 
LIMIT 10";
$result = mysql_query($top_10) or die(mysql_error());

$first_grabbed = false;
while($top_10_r = mysql_fetch_array($result)){
$b_name = $top_10_r['manufacturers_name'];
	if ($first_grabbed == false){
	echo '<div class="blah">'.$b_name.' Sellers</div><ul>';
	$first_grabbed = true;
	}

	$prod_id = $top_10_r['products_id'];
	$product_buy_link = $url_home .'/index.php?main_page=product_info&amp;products_id='. $prod_id;
	$name_sml = str_replace($b_name,"",$top_10_r['products_name']);
	$top_10_r = str_replace("&","&amp;",$top_10_r);
	
		if (strlen($name_sml) > 45 ) { 
 		 $name_sml = substr($name_sml, 0, 42) . "..."; 
		} 
		
	echo'<li> <a href="'.$product_buy_link.'">' .$name_sml. '</a></li>';
}
	echo '</ul>';
?>

basically is it better to call 2 queries or is it better to do 1 query with an extra loop? or neither :O

any feedback greatly recieved

Andy.

Can you get away with doing a JOIN so you get one query and just one loop? Sorry, I got a bit lost with the logic.

So instead of this:

$top_10 = "SELECT p.products_id, p.products_status, p.products_ordered, pd.products_id, pd.products_name, p.manufacturers_id
FROM products AS p, products_description AS pd
WHERE p.products_id = pd.products_id
AND p.manufacturers_id = '".$brID."'
AND p.products_status = '1'
ORDER BY p.products_ordered DESC 
LIMIT 10";

$manufacturer = "SELECT manufacturers_name 
FROM manufacturers
WHERE manufacturers_id = '".$brID."'";

Have INNER JOINS relating products, product_description and manufacturers. HOWEVER, if no product_name exists, no record will be retrieved for that product, so you'll need a LEFT JOIN for those if some products do not have data in a product_description table.

Just a small point: do you really need a separate table for the product_name. It seems you'd only have one 'name' per product, if so, incorporate that into the products table. Then no problem with INNER JOIN - it would also simplify the sql:

$sql = "SELECT p.products_id, p.products_status, p.products_ordered, [B]p.products_name[/B], p.manufacturers_id, m.manufacturers_name FROM products AS p INNER JOIN manufacturers AS m ON p.manufacturers_id = m.manufacturers_id WHERE p.manufacturers_id = '".$brID."' AND p.products_status = '1' ORDER BY p.products_ordered DESC LIMIT 10";

I don't know if the above works, but seems more succinct. If you have 'orphan' products, i.e. no manufacturer, just replace INNER JOIN with LEFT JOIN.

[Andy_nexus]

Hi ardav,

thanks for responding so quickly, your point about the product name being in the same table - i would LOVE for this to be the case, but alas the stupid shopping cart they use set the tables up like this :(.

left joins, this would be a solution i only ever used this one other time and spent the first part of it crashing the mysql server cos i was a NUB!!!!

im gonna try your suggestion, so you reckon a left join on Code 2?

would it be easier if i commented the code?

regards

Andy.

Comment the code if you like - it should be straightforward.

I don't know how your actual data relates so difficult for me to suggest, but a LEFT JOIN will take care of orphans.

If you're doing a complicated JOIN - on 3 tables with different JOINs (Left/INNER etc), see the mysql online manual - it has some nice examples.

The target is to create ONE statement and one loop. Good luck!

[Atli]

Hey.

Just wanted to point out:

FROM products AS p, products_description AS pd
FROM products AS p, products_description AS pd, manufacturers AS m

Adding multiple tables like that is the same as doing a INNER JOIN without an ON clause. Basically, it creates a massive table of all possible joins. (Usually not a very good idea.)
You should always try to use a specific JOIN with an ON clause.

You should be able to easily adapt the query in your second example to get the name of the manufactures by being more specific with your JOINS.
Something like this should work, and be much more efficient.

SELECT 
    p.products_id, 
    p.products_status,
    p.products_ordered, 
    pd.products_name,
    p.manufacturers_id, 
    m.manufacturers_name
FROM products AS p,
INNER JOIN products_description AS pd
    ON p.products_id = pd.products_id
LEFT JOIN manufacturers_name AS m
    ON m.manufacturers_id = p.manufactures_id
WHERE 
    p.manufacturers_id = '".$brID."'
AND p.products_status = '1'
ORDER BY p.products_ordered DESC 
LIMIT 10

[Andy_nexus]

Hey guys i've tried using the joins and i keep getting an mysql error returned.

I then tried to optimise it further to:

SELECT p.products_id, p.products_status, p.products_ordered, p.manufacturers_id, pd.products_name, m.manufacturers_name
FROM products AS p,
INNER JOIN products_description AS pd
    USING (products_id)
LEFT JOIN manufacturers_name AS m
    USING (manufacturers_id)
WHERE p.manufacturers_id = '2'
AND p.products_status = '1'
ORDER BY p.products_ordered DESC 
LIMIT 10

but this still comes back as an error on line 3 - syntax error.

i have mysql version 4.1.22 installed, and i can't seem to see any compatability issues as left join and inner join all seem to be included as valid syntax unless im mistaken.

any ideas?

Andy

[Atli]

On line #2: FROM products AS p, The comma shouldn't be there.

[jomanlk]

You optimization testing will need to be carried out alongside load testing for your database server. If this is for a production/live server, I suggest you run some software to simulate the loads and then run both scripts to time it.

During work (I work for a large ecom store) we found that in some cases, it's better to run multiple queries than to run one large query. The problem arises when the DB has to create temporary tables to map the joins and this slows down the DB server. This happens only when the load is a bit high (around 40 requests per second).

Just keep in mind that if you want accurate benchmarks it's better to do it under realistic conditions.

[Andy_nexus]

Hey all,

@Atli - thanks for pointing that out was going slowly mad *zombie eyes*

@jomanlk - yeah this is for a medium eccormerce platform but we are growing v-rapidly. Things i have noticed so far:

1) there doesn't seem to be any clear winner between the 3 scripts however on refreshing all 3 scripts quite quickly and repeatedly yeha this caused quite a big slowdown on the server as you suggested it might.

2) In terms of file size they're all the same at 2KB

3) They all spike and flatten out much at the sametimes in terms of time taken to load.

4) think the only real way to know is to go with 1 and see what happens (unless you have a more sure way to do it)

ill let you know.

Andy.

[jomanlk]

Carry out some load testing and while looking at your database servers log times (specially look at the execution times for each of the queries). This should give you something concrete to work with.

The first two results in this search looked pretty good.