PHP PDO mySQL query - the most efficient method

Question

I have a database that contains 4 relationship tables used to construct the content of a page:

content    rel     theme     theme_meta

The rel table matches the contentID from the content table to the corresponding rel field of the theme table. theme_meta has a field called themeID that links it to the theme table.

So

When constructing a page at the moment I JOIN the content table to the del table, joining that to the theme table and that to the theme_meta table.

It gives me around 24 rows for each matched row of the content table.

I then use a some php foreach loops to restructure the results into multidimensional arrays per content row.

Is that efficient? Would it be faster and more efficient to make 2 calls to the database, one for content and one for theme. This would produce far fewer rows and be easier to work with but require a second call to the database.

Your current approach (a single query) is usually the best way. Database queries incur a lot of overhead. — eggyal
– eggyal, Commented Oct 27, 2013 at 9:58
Fewer rows? Both methods should result in the same amount of rows overall!?!? Oh, and what eggyal said +1 — Strawberry
– Strawberry, Commented Oct 27, 2013 at 11:43

eggyal · Accepted Answer · 2013-10-27 12:17:39Z

1

As mentioned above, an approach that uses a single query is usually the best way (since database queries incur a lot of overhead).

Indeed, it sounds as though your alternative approach would loop over the results of one query (on the content table) each time calling some other query (on the other tables) to fetch the joined data: such an approach will prove very costly in the long-term and will not scale well.

Therefore, to assemble a multi-dimensional array from the data, you merely need sort the joined results accordingly and keep track of the last seen identifier as you loop over the resultset (in order to detect when one needs to traverse up a level within the resulting array):

$qry = $dbh->query('
  SELECT   *
  FROM     content
      JOIN rel        USING (contentID)
      JOIN theme      USING (rel)
      JOIN theme_meta USING (themeID)
  ORDER BY contentID
');

$arr = array();

$row = $qry->fetch();
while ($row) {
  array_push($arr, array());
  $cid = $row['contentID'];
  do {
    array_push(end($arr), $row);
  } while ($row = $qry->fetch() and $row['contentID'] == $cid);
}

echo var_export($arr);

I would however caution that it is often unnecessarily costly to build such a PHP data structure from the results of a database query, as one can might be able to build and dispatch the requisite output whilst reading the resultset.

answered Oct 27, 2013 at 12:17

eggyal

127k18 gold badges220 silver badges244 bronze badges

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

1 Comment

Walrus Over a year ago

Thanks. Very informative and I have managed it with something very similar.

Collectives™ on Stack Overflow

PHP PDO mySQL query - the most efficient method

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related