[Solved] How can I unionize and find the median time in this raw SQL call?

EverSQL Database Performance Knowledge Base

How can I unionize and find the median time in this raw SQL call?

I am trying to

  1. collect two different type's objects (Published and InReviewing from the same model Events,
  2. find the medium time between the Published and Created,
  3. group those by date..

So that I can return an array with a date, and a medium time difference between any of the types InReviewing and Published that share a target_id from each day. I think SQL would return seconds?

Example :

[[Mon, 03 Dec 2012, 110000], [Thu, 13 Dec 2012, 2200000]]

My raw SQL syntax is lacking some conclusions. This is what I have so far :

    SELECT target_id, DATE(created_at), COUNT(*)
    FROM (   SELECT target_id, DATE(created_at)
          FROM events
          WHERE created_at > '#{@start_time}'
          AND events.type = 'InReviewing'
          UNION ALL
          SELECT target_id, DATE(created_at)
          FROM events
          WHERE created_at > '#{@start_time}'
          AND events.type = 'Published'   
      ) tmp
    GROUP BY DATE(created_at)

Don't mind the #{@start_time}`, that's using native code to implement a Time here, and it works properly..

Thanks!

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
  2. Explicitly ORDER BY After GROUP BY (modified query below): By default, the database sorts all 'GROUP BY col1, col2, ...' queries as if you specified 'ORDER BY col1, col2, ...' in the query as well. If a query includes a GROUP BY clause but you want to avoid the overhead of sorting the result, you can suppress sorting by specifying 'ORDER BY NULL'.
  3. Index Function Calls Using Generated Columns (modified query below): When a function is used directly on an indexed column, the database's optimizer won’t be able to use the index to optimize the search. Creating and indexing a generated column (supported in MySQL 5.7) will allow MySQL to optimize the search.
Optimal indexes for this query:
ALTER TABLE `events` ADD INDEX `events_idx_type_created_at` (`type`,`created_at`);
The optimized query:
SELECT
        tmp.target_id,
        DATE(tmp.created_at),
        COUNT(*) 
    FROM
        (SELECT
            events.target_id,
            DATE(events.created_at) 
        FROM
            events 
        WHERE
            events.created_at > '#{@start_time}' 
            AND events.type = 'InReviewing' 
        UNION
        ALL SELECT
            events.target_id,
            DATE(events.created_at) 
        FROM
            events 
        WHERE
            events.created_at > '#{@start_time}' 
            AND events.type = 'Published'
    ) tmp 
GROUP BY
    tmp.date_created_at 
ORDER BY
    NULL

Related Articles



* original question posted on StackOverflow here.