通过多次迭代优化MySql查询

Question

I have a query that I am using to construct a set of data, which supposed to contain exactly the top 3 users by rating per each admin ID. Now because I am clueless how to achieve this using SQL, I am fetching the top users for each admin separately and then pushing them into an array. More over, since calling sth->fetchAll(), and then array_merge(), will lead to having duplicate array keys on the second iteration and onward, and thus will cause a fatal error, I also have an internal iteration(loop) within the first one, which fetches each row from the result set and pushes it into the array where I keep the formatted result. which cause n *3 iterations, which are n * 3 -1 too many, in my humble opinion.

Also, a BTW question that has been bothering me for quite a while now: Is it true that there is no way to bind a parameter or a value to SQL language components such as LIMIT and such with PDO emulated prepared statements disabled?. code:

private function getHotUsers($admins, $count = 3)
    {
        try{
            $conn = DBLink::getInstance();
            $rows = array();
            $sql = "SELECT user_name, user_id, user_group_id FROM users
            WHERE admin_id= :uid  AND status=1 ORDER BY is_hot_user DESC,last_updated DESC LIMIT {$count}";
            $sth = $conn->prepare($sql);
        foreach ($admins as $admin)
        {
            $sth->bindParam(':uid', $admin, PDO::PARAM_INT);
            $sth->execute();
            while($row = $sth->fetch(PDO::FETCH_ASSOC)){
                $rows[] = $row;
            }
        }
        return $rows;   
        }
}

| Field                | Type             | Null | Key | Default | Extra          |
+----------------------+------------------+------+-----+---------+----------------+
| user_id               | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| admin_id              | int(20)          | NO   |     | NULL    |                |
| user_title            | varchar(450)     | NO   |     | NULL    |                |
| user_desc             | varchar(5000)    | NO   |     | NULL    |                |
| user_data             | longtext         | NO   |     | NULL    |                |
| user_requirements     | varchar(5000)    | YES  |     | NULL    |                |
| user_experience       | varchar(100)     | NO   |     | NULL    |                |
| location_id           | int(11) unsigned | NO   |     | NULL    |                |
| comp_id               | int(11)          | NO   |     | NULL    |                |
| role_id               | int(10) unsigned | NO   |     | NULL    |                |
| user_pass_time        | varchar(100)     | YES  |     | NULL    |                |
| last_updated          | datetime         | NO   |     | NULL    |                |
| is_hot_user           | tinyint(1)       | NO   |     | 0       |                |
| user_internal_id      | int(10)          | YES  |     | NULL    |                |
+----------------------+------------------+------+-----+---------+----------------+

INSERT INTO USERS(admin_id, last_updated, is hot_user) VALUES (1, NOW() - INTERVAL 10 DAY, 1),(1, NOW() - INTERVAL 1 DAY, 0), (1, NOW() - INTERVAL 100 DAY, 1), (1, NOW() - INTERVAL 8 DAY, 0),

(2, NOW() - INTERVAL 1 DAY, 1), (2, NOW() - INTERVAL 100 DAY, 1), (2, NOW() - INTERVAL 5 DAY, 1), (2, NOW(), 0),

(3, NOW(), 0), (3, NOW() - INTERVAL 1 DAY, 0), (3, NOW() - 100 DAY, 1), (3, NOW() - INTERVAL 4 DAY, 0), (3, NOW() - INTERVAL 5 DAY, 0)

Edited as requested by @VolkerK, in bold are the rows that should be selected by the query, the first 3 hot users, that also have the most recent value in their last_updated column, or just the newest users if there are less hot-users tan 3 for this specific admin

Answer 1

Nope. it seems that you are already using the right way.
Though you can bind $count as well. Also, although calling $sth->fetchAll(), and then array_merge(), will not lead to having duplicate keys (which is just impossible, mind you), I wouldn't merge all users into single array anyway, but rather group them by their admin

private function getHotUsers($admins, $count = 3)
{
    $conn = DBLink::getInstance();
    $rows = array();
    $sql = "SELECT user_name, user_id, user_group_id FROM topUsers
            WHERE admin_id= :uid  AND status=1 
            ORDER BY is_hot_user DESC,last_updated DESC 
            LIMIT :coint";
    $sth = $conn->prepare($sql);
    foreach ($admins as $admin)
    {
        $sth->bindParam(':uid',   $admin, PDO::PARAM_INT);
        $sth->bindParam(':count', $count, PDO::PARAM_INT);
        $sth->execute();
        $rows[$admin] = $sth->fetchAll();
    }
    return $rows;   
} 

To make some your confusions straight:

• as a matter of dact, fetchAll does no magic creating an array out of nowhere. It does the same loop, internally. So, there is no overhead.
• to get top 3 of anything you need to run a separate query anyway. So, it's ok to tun your queries in a loop
• do not trust your feelings but measure certain numbers. IF this function runs so slow to hinder whole application - go on with optimizations. If not - there are some other things you can spend time for, I believe