Is better parse and Array or many query to Mysql?

Question

I have a tables with many actions, a field is "created" with the datetime of the action. I need to get back an array with how many actions I registered per HOUR, WEEK DAY, DAY, MONTH in a periode range.

Is better doing a unique db request and parsing all results

    foreach($scans as $s) {
        $data['total']++;
        $t = strtotime($s['Scan']['created']);
        //week day
        @$data['weekday'][date('w',$t)]++;
        @$data['hour'][date('H',$t)]++;
        @$data['day'][date('d',$t)]++;
        @$data['month'][date('n',$t)]++;
    }

or 5 queries with count() function? Thanks

Palantir · Accepted Answer · 2013-01-25 13:44:15Z

2

A database is equipped to perform exactly this sort of tasks with the best efficiency. You should offload most of data processing to the database system, which will do all sort of optimizations for you, and leave PHP for managing and presentation. In principle you should avoid looping over data in PHP to do processing, unless you are doing presentation stuff.

So you should do your entire thing in ONE query, and get the final result from your database. Look at your query and try to combine all the things you want into it. Maybe you can try to get help from Stack Overflow if you can't come up with the right query (but open another question for it).

answered Jan 25, 2013 at 13:44

Palantir

24.2k11 gold badges79 silver badges88 bronze badges

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

5 Comments

Igor Over a year ago

I think that could be max 10000 rows at time

Palantir Over a year ago

So you need to come up with one query to do all your stuff in one step. 10.000 rows is a number which could slow your script down.

Igor Over a year ago

Sorry how I could do all in ONE query?

Igor Over a year ago

I did a test untill 700 rows same time, over 700 queries Win. But I did with 5 queries, I don't understand how to only ONE.

Palantir Over a year ago

Simplest way? Chain your queries with UNION :)

TJF · Accepted Answer · 2013-01-25 13:40:57Z

0

A Unique db request and parsing all results. There would be less network overhead.

answered Jan 25, 2013 at 13:40

TJF

1,1011 gold badge12 silver badges27 bronze badges

Collectives™ on Stack Overflow

Is better parse and Array or many query to Mysql?

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related