CodeIgniter Forums

Full Version: Optimization on Nested Loop
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
I have some concerns on Nested loop with sub query for example i have a main query that states like this

$main_query = "select fname,lname,dept from employee AS e INNER JOIN dept AS d on d.emp_id = e.emp_id"

foreach($main_query as $val):

then adding some query loops like
$sub_query = select sum(users) as total from assignees where emp_id = {$val->emp_id}

echo $sub_query ? $sub_query->total : "0";


is this a bad habit? My fix for this was to create and array on all assignments then loading it once then on the forloop i just called the array and check if its equal then i'll get the data on it?

is this the best approach?
Best approach is to avoid queries inside loops, at all costs.
I would say that sometimes I have reverted to a loop through one results doing more database queries on one of the values. Although only when I know the loop will only have a few items in it.

Generally it is a bad idea. If the first query returns 50 results you are now doing 50 database queries in your loop. What if it returns 5000 results at some point in the future?

The answer is to do a more complex SQL query, where one of your selects has a sub query in it. Personally I find these very difficult to get the format right, so I cannot give an example.

Here are some examples from a quick google search:

May not be an example in there for what you are looking for but the point is not to do looped database queries, but to do better sql queries in the first place.

Hope that helps,


PS Disclaimer - I am no expert on this stuff at all.
PSS Narf posted above while I was writing. It seems it is right that you should avoid looped queries.
Well, I said best approach, but that was an understatement.
Database querying inside a loop is an inherently bad practice.
bad idea as Narf said queries in loop are bad practice, as I see you are trying to get employees with total users assigned group by query would be ideal in this case.

this might help you
Thanks for the enlightenment this really helps me a lot, aws for 32cores still has issues on these even though our specs are great
Data intensive operations are easier to optimize inside the database. If possible your looping/subquery should be done in SQL.
The first big hit this avoids is the repetitive database I/O between your PHP code and the database.]
Secondly, you can experiment with EXPLAIN (query) and indexes. (emp_id is probably already indexed, however)
Avoid PHP, when you can solve the with the help of pure MySQL.