Table join is slow - PLEASE HELP!

[discostudio]

<?php $theQuery = "SELECT  HIGH_PRIORITY COUNT(`ps`.ID) AS ProductHits, 
c.cat_id AS `maincat_id`, c.cat_name AS maincat_name, 
psc.cat_id AS subcat_id, psc.cat_name AS subcat_name 
FROM `Product_Stats` AS `ps` 
INNER JOIN `Product_Category_Links` AS `pcl` ON `ps`.`ProductID` = `pcl`.`ProductID` 
INNER JOIN `Product_Categories` AS `psc` ON `pcl`.`CatID` = `psc`.`cat_id`  

INNER JOIN `Product_Categories` AS `c` ON `c`.cat_id = `psc`.`master_id` 
 WHERE `c`.`dept_id` = '".$_GET['dept_id']."' 
GROUP BY `c`.`cat_id` ORDER BY `c`.`cat_name`; "; ?>

I'm starting to pull my hair out! I have used the cactushop style product stats system.

This should show number of hits per product by Master category, except it's really slow.

Am I joining tables wrongly? - Some one please help!

[discostudio]

table type possible_keys key key_len ref rows Extra

pcl ALL NULL NULL NULL NULL 2439 Using temporary; Using filesort
ps ALL NULL NULL NULL NULL 47011 Using where
psc eq_ref PRIMARY PRIMARY 4 pcl.CatID 1

c eq_ref PRIMARY PRIMARY 4 psc.master_id 1 Using where

[jbx]

You need to index any id columns that are being used in table joins - then your query will be lightning quick!

[discostudio]

Great, that worked.

I created an INDEX on all columns included in the ON clause.