Search code examples
mysqltable-relationships

MySQL: Difference between relating and not relating tables

I have 2 tables: "catalog" and "press" being "press" the child table and I have them related by id_catalog (pointing to "id" in catalog) inside "press".

I managed to get one field on catalog ("name") and their "press" childs like this for testing porpouses:

$query="SELECT catalog.book_title, press.* FROM catalog, press WHERE press.id_catalog = $id";

$book_title = mysql_result($result,$i,"_catalog.book_title");
$media_name = mysql_result($result,$i,"_press.media");
$type = mysql_result($result,$i,"_press.type");
$url = mysql_result($result,$i,"_press.url");

echo $book_title $media_name $type $url;

$book_title = mysql_result($result,$i+1,"catalog.book_title");
$media_name = mysql_result($result,$i+1,"press.media");
$type = mysql_result($result,$i*+1*,"press.type");
$url = mysql_result($result,$i*+1*,"press.url");

echo $book_title $media_name $type $url;

In the query, $id is passed by GET_[] It returns what's expected

Here's the question: What if I don't do the relation in the database? (with the INDEX on id_catalog) Would the query work?

I'm answering this 'cause I think if I don't do the relation I could match the fields and make it work anyway... or not?

Solution

  • I don't think your issue is with the index (although it would help increase performance). I think the issue is this query is producing a Cartesian product -- you aren't relating the 2 tables:

    SELECT catalog.book_title, press.* 
FROM catalog, press WHERE press.id_catalog = $id

    Instead, it should be something like:

    SELECT catalog.book_title, press.* 
FROM catalog 
   JOIN press ON catalog.id = press.id_catalog
WHERE press.id_catalog = $id