Title: prevent duplicate database queries / store loaded data in static variable
Tags: php
Question: I have a ticket table (xxx.xxx entries) and a classification table (table with around 1000 entries) and a simple class for it.
class Classification {
private $id;
private $name;
/* getter */
function __construct($id) {
$item = GET_FROM_DATABASE($id);
$this-&gt;id = $item[&quot;id&quot;];
$this-&gt;name = recursiveDatabaseLookup() /* recursive method to get its parents - For example tree -&gt; branch -&gt; leaf */;
If I load the script and load 10000 tickets including classification, I currently get the classification data for each ticket from the database, where it is often the same.
Is it a good idea to just save the already loaded data in a static variable?
For example like this:
class Classification {
private $id;
private $name;
/* getter */
private static $alreadyLoadedItems = [];
function __construct($id) {
if(count(sel75b5:f2c1:f5d7:b954:8dc3:cee9:baf:a8c0$alreadyLoadedItems) &gt; 0 &amp;&amp; isset(sel75b5:f2c1:f5d7:b954:8dc3:cee9:baf:a8c0$alreadyLoadedItems[$id])) {
/* get data from static var */
$this-&gt;id = sel75b5:f2c1:f5d7:b954:8dc3:cee9:baf:a8c0$alreadyLoadedItems[$id][&quot;id&quot;];
$this-&gt;name = sel75b5:f2c1:f5d7:b954:8dc3:cee9:baf:a8c0$alreadyLoadedItems[$id][&quot;name&quot;];
} else {
$item = GET_FROM_DATABASE($id);
$this-&gt;id = $item[&quot;id&quot;];
$this-&gt;name = recursiveDatabaseLookup() /* recursive method to get its parents - For example tree -&gt; branch -&gt; leaf */;
}
Or is there a better way? The example with the classification is only one, I have very many similar classes in my project. (Ticket categories, Customer Data and so on..)
Comment: I use a similar technique for some database items, like currency information, when it is clear that the same information will be requested multiple times and will not change. Whether it is appropriate in your case, I cannot tell.


Here is another answer: If you load 10000 tickets and load classifications one by one, there will be 10000 queries.
If you use static variable, and your 10000 tickets contains 100 different classifications, and load classifications one by one, there will be 100 queries.
You can try to get all classification id as an unique array after loading 10000 tickets, and load classifications by just one query, like select columns u want from classifications where id in (...).
The better way, use cache like Redis, put all classifications to redis as a hashtable, and use HMGET to get classifications by ids.It will load from memory.

{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 1, 'CommentCount': 1, 'ContentLicense': 'CC BY-SA 4.0', 'CreationDate': '2022:00:08/02/22', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<php>', 'Title': 'prevent duplicate database queries / store loaded data in static variable', 'ViewCount': 29, 'id': 73203729, 'user_AboutMe': '', 'user_AccountId': 7689262.0, 'user_DisplayName': 'Dejan', 'user_Reputation': 59.0, 'user_Views': 11.0, 'user_id': 5826060.0}}