mysql query

[kev@num]

i have this mysql query set up to pull the lastest products added from a certain category (in this case my category has an id=22)

 

"<?php
$maxRows_newmp3s = 10;
$pageNum_newmp3s = 0;
if (isset($_GET['pageNum_newmp3s'])) {
 $pageNum_newmp3s = $_GET['pageNum_newmp3s'];
}
$startRow_newmp3s = $pageNum_newmp3s * $maxRows_newmp3s;

mysql_select_db($database_#####, $######);
$query_newmp3s = "SELECT products.products_model, products.products_image, products_description.products_description FROM products RIGHT JOIN products_description on products.products_id = products_description.products_id RIGHT JOIN products_to_categories on products.products_id = products_to_categories.products_id RIGHT JOIN categories on products_to_categories.categories_id = categories.categories_id WHERE products_date_added > SUBDATE( now(), INTERVAL '14' DAY)  and products_status = '1' and categories.parent_id = '22' ORDER BY products_date_added DESC";
$query_limit_newmp3s = sprintf("%s LIMIT %d, %d", $query_newmp3s, $startRow_newmp3s, $maxRows_newmp3s);
$newmp3s = mysql_query($query_limit_newmp3s, $######) or die(mysql_error());
$row_newmp3s = mysql_fetch_assoc($newmp3s);

if (isset($_GET['totalRows_newmp3s'])) {
 $totalRows_newmp3s = $_GET['totalRows_newmp3s'];
} else {
 $all_newmp3s = mysql_query($query_newmp3s);
 $totalRows_newmp3s = mysql_num_rows($all_newmp3s);
}
$totalPages_newmp3s = ceil($totalRows_newmp3s/$maxRows_newmp3s)-1;

 

 

i then also have a repeating table which displays the products with an image an description like this:

 

<?php do { ?>
 <table width="276" border="0" cellspacing="0" cellpadding="0">
   <tr>
     <td width="50" rowspan="2" align="left" valign="middle"><img src="shop/images/<?php echo $row_newmp3s['products_image']; ?>" width="50" height="50"></td>
     <td width="226" align="left"><span style="font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 9px; font-weight: bold;"><?php echo $row_newmp3s['products_model']; ?></span></td>
     </tr>
   <tr>
     <td align="left" valign="top"><span style="font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 10px;"><?php echo $row_newmp3s['products_description']; ?></span></td>
   </tr>
   </table>
 <?php } while ($row_newmp3s = mysql_fetch_assoc($newmp3s)); ?>

 

this code works fine to display a list of products outside of the oscommerce directory but the only problem is that it takes ages to query the DB.. much longer than normal.. is there anyway to speed this up or does anyone know how to make my SELECT statment more efficiant?

 

any help or suggestions much appreciated!

thanks in advance :) kev.

[Guest]

Well it is probably pointless having all those RIGHT joins as you are trying to get all products related to a category anyway so there will never be any null product rows for a category. MySQL realises this and optimises the query to EQUI joins in the where clause (and if you think about it all products have a description and a category so all joins are really EQUI joins, so rather than letting MySQL figure it out and take time to do it it is better to write the query as such)

 

I took your query and ran it against my database (changing category id = 23 and the 14 in the subdate to 365) it took 3.8 seconds to return 54 rows

 

changing it to this query and it takes 0.03 seconds to return the 54 rows (thats a 12667% improvement :thumbsup: )

 

SELECT p.products_model, p.products_image, pd.products_description

FROM

categories c,

products_to_categories pc,

products p,

products_description pd

WHERE

c.categories_id = pc.categories_id and

pc.products_id = p.products_id and

p.products_id = pd.products_id and

p.products_date_added > SUBDATE( now(), INTERVAL '365' DAY) and

p.products_status = '1' and c.parent_id = '23'

ORDER BY p.products_date_added DESC

[kev@num]

Well it is probably pointless having all those RIGHT joins as you are trying to get all products related to a category anyway so there will never be any null product rows for a category.? MySQL realises this and optimises the query to EQUI joins in the where clause (and if you think about it all products have a description and a category so all joins are really EQUI joins, so rather than letting MySQL figure it out and take time to do it it is better to write the query as such)

 

I took your query and ran it against my database (changing category id = 23 and the 14 in the subdate to 365) it took 3.8 seconds to return 54 rows

 

changing it to this query and it takes 0.03 seconds to return the 54 rows (thats a 12667% improvement? :thumbsup: )

 

SELECT p.products_model, p.products_image, pd.products_description

FROM

categories c,

products_to_categories pc,

products p,

products_description pd

WHERE

c.categories_id = pc.categories_id and

pc.products_id = p.products_id and

p.products_id = pd.products_id and

p.products_date_added > SUBDATE( now(), INTERVAL '365' DAY)? and

p.products_status = '1' and c.parent_id = '23'

ORDER BY p.products_date_added DESC

<{POST_SNAPBACK}>

 

 

 

Genius!

 

You are the mysql bad bwoy :thumbsup:

 

and i thought i was doing so well, following the instructions i had for joining tables!

 

you've shown me the light! i'll be trying my select statments out this way from now on.. thanks again.. this should help me out a lot! much appreciated.

 

:)

 

ps. it works!

[Guest]

Genius!

 

You are the mysql bad bwoy :thumbsup:

 

and i thought i was doing so well, following the instructions i had for joining tables!

 

you've shown me the light! i'll be trying my select statments out this way from now on.. thanks again.. this should help me out a lot! much appreciated.

 

:)

 

ps. it works!

<{POST_SNAPBACK}>

 

Its not always best to use EQUI joins, there are time when you need LEFT/RIGHT joins depending on what you are trying to achieve. There is a fair bit on optimisation (or how MySQL will try to optimise) on thier site here http://dev.mysql.com/doc/mysql/en/mysql-optimization.html

 

also putting the keyword explain before your query (this would be done from the command line or from a tool that you can run queries)

 

such as

 

explain SELECT p.products_model, p.products_image, pd.products_description

FROM

categories c,

products_to_categories pc,

products p,

products_description pd

WHERE

c.categories_id = pc.categories_id and

pc.products_id = p.products_id and

p.products_id = pd.products_id and

p.products_date_added > SUBDATE( now(), INTERVAL '365' DAY) and

p.products_status = '1' and c.parent_id = '23'

ORDER BY p.products_date_added DESC

 

this would then return an explain plan of the query, which tables its accessing first, which indexes it uses (if any), whether its doing a full table scan etc.