How can I improve the following mysql selects?

Question

I am currently refactoring a legacy application and converting piece by piece over to zend framework 1.12.

I am scratching my head as to how to convert this over to zend db, is there a way that this can be done in one query?

Right now I see that it fetches a list of folders first and then runs an additional query per folder... Running this as one query will improve performance, right?

$folders_query = DB::Query("select * from contacts_folders order by sort_order, name");
while($folders = DB::FetchArray($folders_query)){
    $counts_total = DB::QueryOne("SELECT count(cm.messages_id) AS total
    FROM contacts_basics cb, contacts_messages cm
    WHERE cb.contacts_id = cm.contacts_id
    AND cm.folders_id =  '" . $folders['folders_id'] . "'
    AND cm.status = '1'
    AND cm.mark =  '0'");


    if ($counts_total >0){ 
        $folders_name = '<strong>' . $folders['name'] . ' (' . $counts_total . ')</strong>'; 
    } else { 
        $folders_name = $folders['name']; 
    }

    echo '<li><a href="messages.php?fID=' . $folders['folders_id'] . '">';

    echo $folders_name;

    echo '</a></li>';
}

Gordon Linoff · Accepted Answer · 2013-06-23T18:24:15.503

Yes, you can do both in the same query

SELECT cf.*, count(cm.messages_id) AS total
FROM contacts_folders cf left outer join
     contacts_messages cm
     on cf.id = cm.folders_id and
        cm.status = '1' AND cm.mark =  '0' left outer join
     contacts_basics cb
     on cb.contacts_id = cm.contacts_id
group by cf.folders_id
order by cf.sort_order, cf.name;

This uses a left outer join to be sure that you get all folders, even if there are no messages (which is how the original code works). Because of the left outer join, the conditions need to be moved into on clauses.

It also fetches all the information from the folders as well as the total. If there are no messages, then it should return 0 for that folder.

score 0 · Answer 2 · answered Jun 23 '13 at 18:22

There was a minor bug in Gordon's answer but I figured it out thanks to him.

I changed

contacts_basics cb left outer join

To:

left outer join contacts_basics cb

The following code works as expected:

public function getMenuCounts(){
    $raw = "SELECT cf.*, count(cm.messages_id) AS total
    FROM contacts_folders cf left outer join
    contacts_messages cm
    on cf.folders_id = cm.folders_id and
    cm.status = '1' AND cm.mark =  '0'
    left outer join contacts_basics cb
    on cb.contacts_id = cm.contacts_id
    group by cf.folders_id
    order by cf.sort_order, cf.name;";
    $db = Zend_Db_Table::getDefaultAdapter();
    $stmt = $db->query($raw);
    return $stmt->fetchAll();
}

. . Amusing. That was because I originally wrote the joins in the order you have in the original query. Then I realized that the table that with the right field for the join was `cm`, not `cb`. One solution would have been `right outer join`, but I much prefer `left outer join` (habits), and I guess I didn't fully fix the syntax. — Gordon Linoff, Jun 23 '13 at 18:25

How can I improve the following mysql selects?

2 Answers2