How to optimize an SQL query to avoid while() nested subqueries?

Question

I searched a bit for something to optimize the following nested while() queries, but unfortunately I can't adapt it for my case.

Let's imagine the following database relationships :

ITEM 0,5---1,n ITEM_TAG 1,n---0,n TAG

ITEM (id, name, ...)
ITEM_TAG (id_item, id_tag)
TAG (id, name, ...)

Now, we want to get all the ITEMs with their TAGs (limited at 5).

What I've done is the ugly following wich needs to be optimized :

$req=mysql_query("SELECT id, name FROM ITEM WHERE 1 ORDER BY id DESC");
while($res=mysql_fetch_array($req))
{
  $items.="<h1>".$res['name']."</h1>";
  $req_tag=mysql_query("SELECT id, name 
                        FROM TAG, ITEM_TAG
                        WHERE ITEM_TAG.id_item='".$res['id']."'
                        AND ITEM_TAG.id_tag=TAG.id
                        LIMIT 5");
  while($res_tag=mysql_fetch_array($req_tag))
  $items.="&bull; ".$res_tag['name']."<br/>";
}

Another way could be to select all the items and their tags and filter them with an array, like the following :

$req=mysql_query("SELECT ITEM.id AS 'item_id',
                         ITEM.name AS 'item_name',
                         TAG.id AS 'tag_id',
                         TAG.name AS 'tag_name'
                         FROM ITEM, ITEM_TAG, TAG
                         WHERE ITEM_TAG.id_item=ITEM.id
                         AND ITEM_TAG.id_tag=TAG.id
                         ORDER BY ITEM.id DESC");
  while($res=mysql_fetch_array($req))
  {
    $listTag[$res['item_id']][$res['tag_id']]=$res['tag_name'];
    $listItem[$res['item_id']]=$res['item_name'];
  }
  foreach($listItem as $item_id=>$item_name)
  {
    $items.="<h1>".$val."</h1>";
    foreach($listTag[$id_item] $tag_id=>$tag_name)
    $items.="&bull; ".$tag_name."<br/>";
  }

Any idea or advise, to do it better, faster, lighter (and not stronger as Daft punk ;)) ?

Some LEFT JOIN, RIGHT JOIN or puting tag1, tag2... tag5 fields into ITEM table, or other ? But do note that ITEMs could have 0 to 5 TAGs associated...

I'm a little hesitated when talking about fetching results from DB and caching them in PHP before output; but you can try your current approach with MySQLi/PDO and prepared statement, so you only need to prepare the SELECT id,name FROM tag... once and have a relatively fast performance without rebuilding your logic. — Passerby
– Passerby, Commented Feb 19, 2013 at 4:37
Thanks for your reply, but I guess that the query loop will be the same ? even with some MySQLi or PDO, doctrine... — Valky
– Valky, Commented Feb 19, 2013 at 4:39
You'll need two statements: $item=$mysqli->prepare("SELECT id FROM item...");$tag=$mysqli->prepare("SELECT name FROM tag...WHERE id_item=?"). You'll still need to execute $tag inside loop, but $tag has been prepared, so to database it's quite different than direct mysql_query, and thus a better performance. — Passerby
– Passerby, Commented Feb 19, 2013 at 4:44

Passerby · Accepted Answer · 2013-02-19 04:58:39Z

1

It's too long for this in comment, so I'm posting here.

When speaking of caching DB results in PHP before output, I'll always be a little hesitated, as that isn't "intuitive" for the relationship of *data*base and PHP.

In this case, especially if you have a very large ITEM table, you'll be caching a lot string in PHP before you can output.

So I think you can use the current logic, and take advantage of prepared statement:

$req=$mysqli->query("SELECT id, name FROM ITEM WHERE 1 ORDER BY id DESC");
$tag=$mysqli->prepare("SELECT id, name FROM tag, item_tag WHERE item_tag.id_item=? AND item_tag.id_tag=tag.id LIMIT 5");
$tag->bind_param("i",$tid);
while($res=$req->fetch_assoc())
{
  echo "<h1>".$res['name']."</h1>";
  $tid=intval($res["id"]);
  $tag->execute();
  $req_tag=$tag->get_result();
  while($res_tag=$req_tag->fetch_assoc())
  {
    echo "&bull; ".$res_tag['name']."<br/>";
  }
}

The above code is not tested, but I think you can get the idea.

answered Feb 19, 2013 at 4:58

Passerby

10.1k2 gold badges37 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Valky Over a year ago

Thanks a lot, it seems to be interesting, even if I think (I don't know why lol) the query will be done in loop. Gonna try the preformance of this and I'l tell you. +1 anyway.

Passerby Over a year ago

@Valky The query is in fact be "done" in the loop, but there's a significant different (esp. if you have a large ITEM table) with mysql_query, that mysqli keep executing the same query with different values, while mysql keep proposing a new query every time.

Collectives™ on Stack Overflow

How to optimize an SQL query to avoid while() nested subqueries?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related