0

I am working on an ad management system, and recently, we launched on a high traffic website to do some testing. I am storing each impression as a record in the db with a timestamp, ad ID, zone ID, and website ID.

I would select all the data between a date range, and use PHP to organize the records (by days, etc). That was inefficient so I make two queries that

GROUP BY date(`impressions`.timestamp)

First query to retrieve a count of impressions, second query to get a list of associated labels.

My problem is this:

I need to query to get not just the overall impressions, but the impressions by zone AND by day, and I cannot use PHP to organize this array of data because it is millions of points.

SELECT `zones`.name as zoneName, `impressions`.*
from `impressions`, `zones` 
WHERE `impressions`.website_id = "14" 
AND `zones`.website_id="14" 
AND `zones`.zone_id=`impressions`.zone_id 
AND `impressions`.timestamp 
BETWEEN "2012-10-08 00:00:00" AND "2012-10-15 23:59:59"
ORDER BY `impressions`.timestamp ASC

That may return millions of records. Then I use PHP to sort the returned data into zones.

For example, this is the resulting array after my PHP sorting. This array represents the zones and the impressions they received for the given days. What would be a good query to use to get as close to this as possible to minimize memory overhead when dealing with large arrays?

Array
(
[labels] => Array
    (
        [Oct 8th] => "Oct 8th"
        [Oct 9th] => "Oct 9th"
        [Oct 10th] => "Oct 10th"
    )

[final] => Array
    (
        [0] => Array
            (
                [name] => Blog Skyscraper
                [data] => Array
                    (
                        [0] => 449
                        [1] => 499
                        [2] => 558
                    )

            )

        [1] => Array
            (
                [name] => Latest News Right
                [data] => Array
                    (
                        [0] => 805
                        [1] => 809
                        [2] => 760
                    )

            )

        [2] => Array
            (
                [name] => Photos Right
                [data] => Array
                    (
                        [0] => 788
                        [1] => 786
                        [2] => 743
                    )

            )

        [3] => Array
            (
                [name] => Banner Zone
                [data] => Array
                    (
                        [0] => 793
                        [1] => 796
                        [2] => 747
                    )

            )

        [4] => Array
            (
                [name] => Mini Right Bottom
                [data] => Array
                    (
                        [0] => 784
                        [1] => 778
                        [2] => 742
                    )

            )

        [5] => Array
            (
                [name] => Mini Right Top
                [data] => Array
                    (
                        [0] => 790
                        [1] => 787
                        [2] => 743
                    )

            )

    )

)

mickmackusa
  • 43,625
  • 12
  • 83
  • 136
Jacob Kelley
  • 395
  • 3
  • 13
  • Post your `EXPLAIN` from your query. – Kermit Oct 15 '12 at 23:09
  • 4
    Why are you looping through millions of arrays? Why not use `GROUP BY`, sorting, etc, ... to get the final array? You should be using a more complex MySQL query instead of using huge arrays in PHP... – cegfault Oct 15 '12 at 23:32
  • What you want to do with those data? what's the requirement? – Gohel Kiran Dec 15 '12 at 06:55
  • `GROUP BY` is useful for setting up aggregate functions in MySQL. Do you actually need `ORDER BY` with multiple rules? I think we need to better understand the task requirements and have access to some sample data. – mickmackusa May 10 '23 at 05:26

0 Answers0