Improve performance by obtaining multiple Mysql records

Question

Improve performance by obtaining multiple Mysql records

Navigation

#1 by (3 votes)

2

I want to know what is the best way to get several records of a mysql database, I leave a simple example of my situation. I have a static method that queries a database and converts the result into an object that I have defined.

class Persona {

    public $id;
    public $nombre;

    function __construct($id,$name){
        $this->id = $id;
        $this->nombre = $name;

    }

    public static function getPersona($personid){
        $db = new ConnectionDB();
        $stmt = $db->prepare("SELECT * FROM Persona WHERE id = ?");
        $stmt->bind_param('i',$personid);
        $stmt->execute();
        $stmt->bind_result($id,$name);
        $stmt->fetch();
        return new Persona($id,$name);
    }

}

Which option would have better performance?

Option 1: Create a method that receives several ids and check them one by one using the previous method

public static function getPersonas($ids){
    $personas = array();
    foreach($ids as $id){
        $personas[] = new getPersona($id);
    }
    return $personas;
}

Option 2: Create a method that uses IN in the query.

public static function getPersonas($ids){
    $db = new ConnectionDB();
    $sql = "SELECT * FROM personas WHERE id IN (" . implode(',',$ids) .")";
    $persons = array();
    if($result = $db->query($sql)){
        while($row = $result->fetch_assoc()){
            $persons[] = $row;
        }
    }
    $oPersons = array();
    foreach($persons as $p){
        $oPersons[] = new Persona($p['id'],$p['name']);
    }
    return $oPersons;
}

mysql php

asked by Edwin V 01.03.2017 в 19:04

source

1 answer

Problem when using the AudioManager if other app open C ++ Initialization of an arrangement of structures

score 3 · Accepted Answer

The connections to the base are very expensive in a matter of time, so it ALWAYS takes a single query that returns several results ALWAYS (and can be considerably less when the amount to recover is much).

However, other attributes of software quality, such as maintainability, are also often put on the scale. In this case, it would be good not to duplicate the points from where a person is created from the database, since if it is necessary to add a new attribute, there are 2 places to modify.

Normally is not a good practice to do premature optimizations , since one does not know a priori where You will find bottlenecks in one system. Then, if there are performance problems, measurements are taken and what is appropriate is fixed.

In this case, for example, if when people are going to look for us we are talking about 2, 3 or 20, in that case it would not have a significant impact and it would be much worse, in my opinion, the duplication of the logic of creation of people.