Not sure if JOIN is right

mikewhy

I have two tables that I want to query, one is Products, the other is Stats. I want to display product information for the most popular products (like a top ten). I wrote a query using a simple join which works, but its not returning the correct data and it takes a long time.

Tables:

products
p_name | img

stats
s_name | popularity

here's my query:

"SELECT products.img,stats.s_name 
  FROM products,stats 
  WHERE products.p_name = stats.s_name 
  ORDER BY stats.popularity DESC 
  LIMIT 0,10";

Like I said, the script works but the popularity rank is wrong and I think it's "joining" the whole product catalog before returning results which makes it slow.

Thanks in advance.

laserlight

mikewhy wrote:
the script works but it's the popularity is wrong

That sounds a little like a contradiction. How is the popularity wrong?

I think it's "joining" the whole product catalog before returning results which makes it slow

Maybe, maybe not. In the presence of a where clause, I doubt that the database engine will actually compute a cartesian product and then eliminate rows; it should be smarter than that, from what I understand.

Is p_name a primary key, or at least indexed? What about s_name? Is popularity indexed?

mikewhy

laserlight;10903564 wrote:
That sounds a little like a contradiction. How is the popularity wrong?

I meant that it returns results, unfortunately not the correct results. The popularity is the number of time the product was purchased.

p_name is indexed, the others are not.

laserlight

mikewhy wrote:
I meant that it returns results, unfortunately not the correct results. The popularity is the number of time the product was purchased.

Are you sure that the table contains the correct data?

mikewhy wrote:
p_name is indexed, the others are not.

If you are always going to order by popularity, then it might be a good idea to index that column. It is also usually a good idea to index columns that are frequently going to be used in a join.

mikewhy

laserlight;10903569 wrote:
Are you sure that the table contains the correct data?

If you are always going to order by popularity, then it might be a good idea to index that column. It is also usually a good idea to index columns that are frequently going to be used in a join.

I indexed the popularity column, the data is correct. I'm still getting the same (wrong) result though.

laserlight

On a hunch: in what way is the result wrong? For example, could it be that the popularity column is say, a VARCHAR instead of an INT column, so the values are ordered lexicographically instead of numerically?

mikewhy

laserlight;10903580 wrote:
On a hunch: in what way is the result wrong? For example, could it be that the popularity column is say, a VARCHAR instead of an INT column, so the values are ordered lexicographically instead of numerically?

I discovered that the p_name and s_name columns are not identical (for example p_name: NEC 3550 vs. s_name: NEC 3550 DVDRW) so the previous script returned only exact matches.

So I changed the script to:

SELECT products.img,stats.s_name
  FROM products,stats
  WHERE MATCH (product.p_name)
  AGAINST ('stats.s_name')
  ORDER BY stats.popularity DESC
  LIMIT 0,10";

The popularity is correct but I'm getting the wrong product.img, the same image is shown for each product.

laserlight

I discovered that the p_name and s_name columns are not identical (for example p_name: NEC 3550 vs. s_name: NEC 3550 DVDRW) so the previous script returned only exact matches.

That sounds like the data is inconsistent. You should make it such that p_name and s_name are identical when matches are expected. If not, indices would probably not be used, and you will find that your joins are slow.

Not only that, but inconsistent data is a problem in itself: how do you know that NEC 3550 really is identical to NEC 3550 DVDRW? Maybe they are actually different product names. Oh, you can tell me that they are identical because you have external knowledge that says that they are identical, but if you want to be correct, you need to incorporate that external knowledge into the database, otherwise your script will just blindly match.

mikewhy

I'll have to change the way the stats table is populated, it was sort of an afterthought but now it's looking useful.

Any thoughts on why the products.img repeats the same value?

$query = "SELECT products.img,stats.s_name
   FROM products,stats
   WHERE MATCH (product.p_name)
   AGAINST ('stats.s_name')
   ORDER BY stats.popularity DESC
   LIMIT 0,10";
$result = mysql_query($query) or die("Couldn't execute query");
$count = 1;
   while ($row= mysql_fetch_array($result)) {
     $title = $row["p_name"];
     $img = $row["img"];
   echo "$count.)&nbsp;<img src='$img' width='80px' height='60px'/>&nbsp;<a href='URL'>$title</a><br/>";
  $count++ ;
  }

mikewhy

laserlight;10903585 wrote:
That sounds like the data is inconsistent. You should make it such that p_name and s_name are identical when matches are expected. If not, indices would probably not be used, and you will find that your joins are slow.

Just to clear up - the intent of the s_name is to be general. To use the previous example we're saying NEC DVDRWs are popular, not just the NEC 3550. So the above script says NEC DVDRW's are most popular, get an image and a link, SONY is second, get an image and a link, HP is third, etc. I'm trying to match an image which resides in the product table to an general product from the stats table.