I want to know what is the best way to obtain multiple records from a mysql database. Here is a simple example of my situation. I have a static method that queries a database and converts the result to 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 multiple id's and queries them one by one using the above 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; }
Connections to the database are very time consuming, so a single query that returns multiple results ALWAYS takes less time (and can be considerably less when the amount to retrieve is large).
However, other software quality attributes, such as maintainability, are also often weighed. 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 later, there are 2 places to modify.
It is normally not a good practice to do premature optimizations , as one does not know a priori where the bottlenecks will be in a system. Then, if there are performance problems, measurements are taken and the corresponding is fixed.
In this case, for example, if when people are searched 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, to duplicate the logic of creating people .