Mysql – How to handle nested groupings with possible null elements in one query

group bygroup-concatenationMySQL

As you can see in this sql-fiddle, I have three tables: events, event_dates, and event_times.

The original tables contain various other fields, which I omitted as they aren't relevant, I just need to have them in my final result, but I do no operation/sort/filtering/grouping on them.

An event has 1+ event_dates and an event_date has 0+ event_times.

From an object point of view, an event object has a list of event_date objects, which in turn can have a list of event_time objects.

My final goal is to obtain an array of event objects.

My plan is to design a query returning the ids of matching records and from these results fetch the records from their tables in 3 queries and assemble everything in code (this is necessary because of various data-transformations outside the scope of SQL).

My problem is getting the first query right

At the moment I have the following query

SELECT events.id,
CONCAT('[', GROUP_CONCAT(event_dates.id SEPARATOR ','), ']') AS event_date_ids,
CONCAT('[', COALESCE(GROUP_CONCAT(event_times_per_event_date.event_time_ids SEPARATOR ','), '[]'), ']') AS event_time_ids
FROM events
INNER JOIN event_dates ON event_dates.event_id = events.id
LEFT JOIN (
  SELECT date_id AS event_date_id, CONCAT('[', COALESCE(GROUP_CONCAT(event_times.id SEPARATOR ','), ''), ']') AS event_time_ids
  FROM event_dates
  LEFT JOIN event_times ON event_times.date_id = event_dates.id
  GROUP BY event_dates.id
) AS event_times_per_event_date ON event_times_per_event_date.event_date_id = event_dates.id
GROUP BY events.id

which fails in the following situation:

Given event e with three dates d1,d2 and d3, where d1 has two times (t1,t2), d2 has zero times, d3 has two times(t3,t4).
The result of the query would be:

event_id  |  event_date_ids  |  event_time_ids
1         |  [1,2,3]         |  [[1,2],[3,4]]

What I'd expect would be an empty array in event_time_ids, so that I could match the indices of the array in event_date_ids with the one for the array in event_time_ids:

event_id  |  event_date_ids  |  event_time_ids
1         |  [1,2,3]         |  [[1,2], [], [3,4]]

How can I obtain this?

P.S.: I deeply apologise if my question is chaotic or unclear and I'm ready to provide every other necessary information.

Thank you!

UPDATE 1

Thanks a lot to @Andriy M for spotting my mistake.

Moving on, I have to add to the equation event_categories where events have 1+ categories.

Adding a DISTINCT in the GROUP_CONCAT would break my current solution as empty arrays would be removed.
The solution is to make this query into a subquery, and join its results to a query fetching all other event data and grouping by events.id.
The event_time_ids would be duplicate for every event row, thus selecting any row for that column is fine.

Best Answer

You need to apply one small change to your query which is emphasised below:

SELECT events.id,
CONCAT('[', GROUP_CONCAT(event_dates.id SEPARATOR ','), ']') AS event_date_ids,
CONCAT('[', COALESCE(GROUP_CONCAT(event_times_per_event_date.event_time_ids SEPARATOR ','), '[]'), ']') AS event_time_ids
FROM events
INNER JOIN event_dates ON event_dates.event_id = events.id
LEFT JOIN (
  SELECT event_dates.id AS event_date_id, CONCAT('[', COALESCE(GROUP_CONCAT(event_times.id SEPARATOR ','), ''), ']') AS event_time_ids
  FROM event_dates
  LEFT JOIN event_times ON event_times.date_id = event_dates.id
  GROUP BY event_dates.id
) AS event_times_per_event_date ON event_times_per_event_date.event_date_id = event_dates.id
GROUP BY events.id;

In the derived table you are correctly grouping by event_dates.id but retrieving event_times.date_id instead. That is a mistake because when there is no match, it is null. And it is because one value is returned as null that the outer query omits the corresponding empty group. Replacing event_times.date_id AS event_date_id with event_dates.id AS event_date_id fixes the issue.

Related Solutions

Mysql – Index Strategy for MySQL Query that matches on one column and between two others

(Not an answer, but too clumsy for a comment.)

have an index on process_group_id, start_timestamp, end_timestamp. However the query does not appear to use anything but the process_group_id part of the index.

It is actually using start_timestamp although it does not say so. What was the key_len? That may give a clue. Also try EXPLAIN FORMAT=JSON SELECT ....

AND  1431388800 BETWEEN start_timestamp AND end_timestamp

Turn that into the following to see if it helps:

AND  start_timestamp <= 1431388800
AND  end_timestamp   >= 1431388800

I suspect it is identical, but I am not sure.

Caution. The difference between 0.5s and 0.06s could a warmed up cache. Run timings twice. Also, use SQL_NO_CACHE to avoid the Query cache.

How wide are the timestamp ranges typically? How precise is 1431388800? The values sound like they have a resolution of 1 second. What if we switched to 1 minute or 1 hour?

After you provide some answers, I will possibly suggest turning this into a Data Warehouse application and discuss Summary tables.

Edit

Consider this approach to storing the data. (I still don't have enough details to determine what variant of the following would be optimal.)

Since you have a "processing" phase that leads to the table in question, I suggest rewriting it to store into a different table (either in place of the existing one, or in addition)

CREATE TABLE ByMinute (
    process_group_id ...
    ts TIMESTAMP NOT NULL, -- rounded to the minute
    sum_1 FLOAT  -- see below
    sum_2 ...,
    PRIMARY KEY(process_group_id, ts)
);

The table contains one row per minute. That's 0.5M per year, not terribly big. If converting from the existing structure do sum_1 += measurement_1 for each row BETWEEN starttime AND endtime.

That is possibly more processing than you are currently doing, but it should not be excessive. And it makes the SELECT extremely efficient:

SELECT  sum_1, sum_2, sum_3, sum_4
    FROM  ByMinute
    WHERE  process_group_id = 5
      AND  ts = somehow round 1431388800 to a minute

You currently have a daily dump. The processing should be obvious. If you switch to "streaming" and use the ping-pong method I mentioned, then very similar code can be used for each transient table. And you would probably have nearly up-to-the-second data all the time.

MySQL Performance issue: ~360ms to fetch 120 rows with 10 columns

Why have LIMIT 10000?
Can status be turned into an ENUM (1 byte) instead of a potentially large VARCHAR(255)?
It would probably be better to combine date and time. Joining to the 'time' table seems to add to the complexity and hurt performance.
It is essentially impossible to deal with (that is, use an index for) a start-end date (or datetime) range. Consider having one row per day, instead of one row for a range of days. Sure, that increases the number of rows in the table, but it will probably greatly decrease the time taken for certain queries.
What does NULL mean for end_time? For end_date?
Perhaps published_at IS NOT NULL is equivalent to status = 'published'?
product_eds_events has a large value for Rows; please tell us what the table is about, and provide SHOW CREATE TABLE.
It may be better to change certain JOINs into subqueries; see below.
Some of the tables smell like "many-to-many mapping" tables. See this for several optimization tips.

below...

SELECT ...,
    GROUP_CONCAT(DISTINCT event_categories_events.event_category_id
                 ORDER BY event_category_id) AS event_category_ids,
    ...
  INNER JOIN event_categories_events
          ON event_categories_events.event_id = events.id 
  ...

The reason for this is... You have an "explode-implode" caused by JOIN plus GROUPing. First, the JOIN explodes the number of 'rows' and builds a temporary table for them. Then the GROUPing (GROUP_CONCAT, in this case), implodes. The better (I hope) approach would be:

SELECT ...,
    ( SELECT GROUP_CONCAT(
                 DISTINCT event_category_id
                 ORDER BY event_category_id)
          FROM event_categories_events
          WHERE event_id = events.id
    ) AS event_category_ids,
    ...
  -- no JOIN with event_categories_events
  ...

That particular subquery needs INDEX(event_id, event_category_id) in this order.

(Apply most of my suggestions, then start a fresh Question with fresh CREATE TABLEs, EXPLAINs, etc. Augmenting this Question could get messy and hard to follow.)

Best Answer

Related Solutions

Mysql – Index Strategy for MySQL Query that matches on one column and between two others

MySQL Performance issue: ~360ms to fetch 120 rows with 10 columns

Related Question