0
\$\begingroup\$

I'm working on a directory that lists employee information. Each employee can belong to multiple departments and each employee can have multiple job titles. I have 5 MySQL tables that I am pulling information out of, including:

  • employee
  • employee_has_department
  • department
  • employee_has_jobTitle
  • jobTitle

The employee table has a many-to-many relationship with department and jobTitle. I currently have nested queries, as follows:

$stmt = $mysqli->prepare("SELECT employeeId, firstName, middleName, lastName, suffix, profilePhoto FROM employee");
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($employeeId, $firstName, $middleName, $lastName, $suffix, $profilePhoto);
while($stmt->fetch()){

  echo "<h4>$firstName</h4>";

  $stmt2 = $mysqli->prepare("SELECT jobName FROM jobTitle INNER JOIN employee_has_jobTitle ON jobTitleId = jobTitle_jobTitleId WHERE employee_employeeId = ?");    
  $stmt2->bind_param("i", $employeeId);
  $stmt2->execute();
  $stmt2->store_result();
  $stmt2->bind_result($jobName);
  while($stmt2->fetch()) {
    echo "$jobName<br>";   
  }

  $stmt3 = $mysqli->prepare("SELECT departmentName, departmentURL FROM department INNER JOIN employee_has_department ON departmentId = department_departmentId WHERE employee_employeeId = ?");    
  $stmt3->bind_param("i", $employeeId);
  $stmt3->execute();
  $stmt3->store_result();
  $stmt3->bind_result($deptName, $deptURL);
  while($stmt3->fetch()) {
      echo "<a href='$deptURL'>$deptName</a>";
  }   
}

Clearly the output needs formatting, but it's getting the information and outputting as expected. I know that this can probably be done in a single sql statement, but the complexity and ugliness of performing a join on 5 separate tables with multiple many-to-many relationships leaves me wondering. Would taking the time to figure out such a query worth the hassle, or is this an "acceptable" way of handling the situation?

\$\endgroup\$
2
\$\begingroup\$

The answer is that there is a definite performance gain in having it all in a single join. Just the reduction in back and forth should greatly decrease the latency, and the database engine should be way more effective.

For a more general view on that topic :

  • Will the request be hard to understand later ? This is the most important question, as it will be the difference between maintainable code and code nobody will dare touch. In this particular case, I would say there is an obvious request to write, and that it will be fairly easy to understand. If you don't feel this is the case, then you probably should not go this way (premature optimization, etc.).

  • How complex will be the whole solution ? Displacing logic from PHP to SQL is often a good idea, but might have drawbacks :

    • hidden logic (triggers, or opaque stored procedures) that is hard to understand later
    • vendor lock-in (when using advanced features that are not standard).
    • poor performance : with complex queries, it becomes hard to evaluate the run-time performance

    It seems to me that a straightforward 5 tables join is far from complex, and is a lot less ugly than the PHP code displayed here. You would get rid of all the content of the while loop ...

  • How much will the performance be impacted ? For a low-traffic web application, which is the most common case, this should be the last concern.

\$\endgroup\$
  • \$\begingroup\$ The initial join may not be too bad, but this is just the initial query that displays all. There will be pagination added, sorting by department and/or job title, searching by first or last name, etc. I think you're right that a single query will be a performance boost, but I also think that at the end of the day this is going to be a hellacious query. Thanks for your time and input! \$\endgroup\$ – Matthew Johnson Apr 15 '14 at 23:48
  • \$\begingroup\$ I edited my question, though I still consider it answered. I added what the single query is for those that may stumble across this question later. If you have time, would you check it to see if it's done efficiently? Running it in phpmyadmin works, and it appears to be returning all the information correctly. Thanks again for your help! \$\endgroup\$ – Matthew Johnson Apr 16 '14 at 14:39
  • 2
    \$\begingroup\$ It seems good to me after a quick glance, but if you want the final word on performance, you should check what the EXPLAIN command tells you. \$\endgroup\$ – bartavelle Apr 16 '14 at 15:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.