Optimising data access MYSQL

[williamZanelli]

Hi guys,

 

I have a for loop, that queries my database, sometimes, close to 240 times,

 

I want to minmise the DB access - what would be ideal is if I can query a result set, such as this..

$result = mysql_query($sql,$con);

 

Is it possible for me to now run a query on $result. Such as

 

select * from sometable where age >= '24'

 

So, rather than access the DB over and over again, I just query the result set, which will be much faster.

 

Any ideas on this one guys?

 

Cheers

 

Zain

 

 

 

 

[mikosiko]

you'r wise to be worried about doing multiples queries in a loop... that normally call for an application review.

 

answering you question:

"what would be ideal is if I can query a result set"

afaik, that is not possible using sql over it again, you probably could store the result set in an array and post-process it using php functionality... but my best suggestion for you is to post the code that you have to see if it can be optimized first.

[xyph]

I agree with the above. MySQL is VERY powerful, and chances are you can do exactly what you want in a single query, or with post-processing in PHP

[Psycho]

Yep, most likely, what you need to be doing is querying ALL the records you need and then handle the differentiation of the output in the processing code.

 

using your example query above where you are interested in age, let's say you want to display lists of people in the database but you want to break it out by age groups. So, separate tables for babies, toddlers, children, teens, young adults, etc. etc. You would do just one query to get all the records and order the results by age. Then, as you process the records you would insert new tables as needed when you detect that the age has changed to the next level.