In a previous article, I got a good response about the mistakes that people made in PHP.
There I forgot to mention the most common mistake made by many developers out there. They write nested queries inside the while loop of PHP.
- Execute the parent query
- Fetching the result using a while loop
- And using another query inside the loop by referring ID of the parent query result.
Trust me MySQL cries a lot during this practice.
So, Let’s explore what actually happens behind the scene.
#1 Bad Practice of querying the Results
Here, We have 2 tables:
- questions
- answers
Both have some data and question_id is a foreign key in the answers table.
As mentioned in the above code, we have to first fetch the result from the parent query, and for each parent again call the child query to fetch the result and then append it to its parent.
parent query -> result -> on each -> child query -> append with parent-> finish
This causes a lot of database queries and makes MySQL cry in the corner of darkness.
#2 Better Practice of querying the Results
We have the same table as mentioned above. But the approach is different. Let’s see.
In the above example, I have called 2 separate queries for parent and child.
- Firstly I fetched results from the parent and then fetched only the ids of the parent result using the array_column() function. (i.e 1,2,3)
- Then used in keyword of MySQL to fetch the result in one shot.
- Then iterate through the questions array and for each question filter answers on the basis of question_id and merged the result.
Now, the question is how this approach is useful, we are still iterating through the questions array and merging the child result. So, the answer is simple, this time we are not calling the database again and again and again, we are handling the operations on the PHP level.
The core idea of the array_filter() function is to just filter out a required result which is nothing but the searching and returning result array behind the scene. This approach uses resources on the PHP side, which is okay.
Programming Language should be capable enough to reduce the overload of database.
#3 Analysis
In the first approach, if there are 500 questions then 500 nested queries will be executed. This can be minimized using pagination as well, but still, it’s bad practice.
In the second approach, we’re using server resources and PHP itself handling the stuff. Which causes a lot of memory usage.
In both scenarios, we can use Pagination. That’s why Google itself does not show all results on one page.
We can use joins in MySQL as well, but it is a completely different story.
So, This was my approach to handling nesting queries in PHP to improve performance. Just wanted to show you the different perspectives on the problem.
Hope You Enjoyed and Learned something new.
Let’s Connect somewhere else as well:
