php - Better way to do this subquery

608

Let's say I have this query:

<?
     $qi = $db->prepare('SELECT one.id, one.Value, two.Name, three.nfid, temp.Name AS Alias
           FROM one
           INNER JOIN two ON one.fid = two.id
           LEFT OUTER JOIN three ON two.fid = three.fid
           LEFT OUTER JOIN (SELECT id,Name FROM two) AS temp ON three.nfid = temp.id
           WHERE one.rid = ?
           ORDER BY one.id ASC');
      $qi->execute( array( $id ) );
?>

Connections between the tables are:

Table one contains a number of rows with the fieldsone.Value,one.rid andone.fid.

fid is a connection to table two which contains thetwo.Name of the items (one.fid =two.id).

But sometimes the item is an alias for another item, which is why table three exists. It contains the fieldsthree.fid andthree.newfid wherethree.newfid =two.id (but for another item with anothertwo.Name)

The query is supposed to fetch all rows from one with a certainone.rid and getone.Value,two.Name and if there is anthree.fid for thisone.fid, gettwo.Name forthree.newfid and call it Alias.

Is there a way to improve this query or solve the problem in another way? Perhaps reshape the layout of the database? It is currently quite slow. The example here have been simplified to make it more general.

Thank you.

329

Answer

Solution:

The subquery in parentheses forces MYSQL to ignore its indexes, which makes it take a long time. Better to directly join two as temp. As long as you always put two.[field] and temp.[field], it will tell them apart just fine.

  SELECT one.id, one.Value, two.Name, three.nfid, temp.Name AS Alias
  FROM one
  INNER JOIN two ON one.fid = two.id
  LEFT OUTER JOIN three ON two.fid = three.fid
  LEFT OUTER JOIN two AS temp ON three.nfid = temp.id
  WHERE one.rid = ?
  ORDER BY one.id ASC

People are also looking for solutions to the problem: php - How to stop perfoming on XHR requests when the user is logout in Symfony?

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.