Oops, missed this one before I sent that other email.
I think it's the order of your joins playing up.
LEFT JOIN categories as c ON chp.cat_id = c.cat_id
LEFT JOIN prices as pr ON pr.prod_id = p.prod_id
That means it will join categories -> prices - not what you want.
Try this query:
SELECT p.prod_id, p.prod_name, p.prod_no, chp.cat_id,
c.cat_name, c.cat_parent, pr.price
FROM
products p,
categories_has_products chp,
categories c,
prices pr
WHERE
chp.prod_id = p.prod_id
AND chp.cat_id = c.cat_id
AND pr.prod_id = p.prod_id
AND prod_featured = '1'
ORDER BY RAND(), pr.price ASC
LIMIT 6
Afan Pasalic wrote:
The result display is bad (at least on at email). Here is a snapshot:
http://afan.net/bhw/snapshot1.png
-afan
On Wed, 2006-02-08 at 19:39 -0500, Afan Pasalic wrote:
Hm. Have no idea what does it means:
table
type
possible_keys
key
key_len
ref
rows
Extra
p
ALL
NULL
NULL
NULL
NULL
1268
Using
where;
Using
temporary; Using filesort
chp
index
NULL
PRIMARY
8
NULL
1741
Using
index
c
eq_ref
PRIMARY
PRIMARY
4
chp.cat_id
1
pr
ALL
NULL
NULL
NULL
NULL
10231
-afan
On Thu, 2006-02-09 at 09:53 +1100, Chris wrote:
Hi,
Grab that query and run it through mysql with 'explain', so:
explain SELECT p.prod_id, p.prod_name, p.prod_no, chp.cat_id,
c.cat_name, c.cat_parent, pr.price
FROM products as p
LEFT JOIN categories_has_products as chp ON chp.prod_id = p.prod_id
LEFT JOIN categories as c ON chp.cat_id = c.cat_id
LEFT JOIN prices as pr ON pr.prod_id = p.prod_id
WHERE prod_featured = '1'
ORDER BY RAND(), pr.price ASC
LIMIT 6
That will show you whether you are using the database indexes or not.
If you can't work it out then post the explain output.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
