How to remove a php loop to reduce the number of mysql queries

Question

Within a piece of code I'm refactoring, a while loop iterates through a result set from a database query and performs a subset of queries.

In most cases, these loops can be extracted to perform fewer queries with a larger set of passed parameters.

However, I'm not sure how to handle this specific query where an ORDER BY and LIMIT 1 make ita little trickier to work out how to optimise it. Any points would welcome on how to approach this query type:

Example:

$e   = array( ... );
foreach($e as $i=>j) {
    $sql = "SELECT a FROM b WHERE c = '".(int)$j."' ORDER BY f LIMIT 1";
    $res = $db->query($sql);
}

A possible way would be to select without the ORDER/LIMIT conditions and filter in PHP via PHP loops but only 1 query, but is there a better way to reduce the result set returned?

EDIT: To clarify better, I'm extending with some more information. If there are 5000 records that would otherwise be returned in the simplified structure above, then we'd need 5000 in the final result set as well after optimising the query out of the loop. The LIMIT 1 clause doesn't allow that to be done in the outer SQL, but I haven't worked out a way to do that with an inner query or sub query yet. It was this element that I wanted to sanity check with better/more experienced minds.

Any pointers welcome! Thank you

Doesn't this work? SELECT a FROM b WHERE c in(1,2,3,4,5) GROUP BY c ORDER BY min(f) — Lou
– Lou, Commented Apr 26, 2018 at 23:28
Group by c - didn't cross my mind to do it that way. Can you write that as an answer and I'm going to try that out with the data set. This looks like it will do it though! Thanks for another way to look at the problem. — MyStream
– MyStream, Commented Apr 26, 2018 at 23:31

Lou · Accepted Answer · 2018-04-26 23:33:33Z

3

I think building your query this way should get you the results you are expecting :

SELECT a FROM b WHERE c in(1,2,3,4,5) GROUP BY c ORDER BY min(f);

As GROUP BY will only output a single row of each column "c".

answered Apr 26, 2018 at 23:33

Lou

8765 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MyStream Over a year ago

Thanks @Lou this is the insight I was looking for!

Collectives™ on Stack Overflow

How to remove a php loop to reduce the number of mysql queries

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related