MySQL - Combining two select statements into one result with LIMIT efficiently

database, join, mysql, select

Solution

You can combine multiple queries with `UNION`, but only if the queries have the same number of columns. Ideally the columns are the same, not only in data type, but also in their semantic meaning; however, MySQL doesn't care about the semantics and will handle differing datatypes by casting up to something more generic - so if necessary you could overload the columns to have different meanings from each table, then determine what meaning is appropriate in your higher level code (although I don't recommend doing it this way).

When the number of columns differs, or when you want to achieve a better/less overloaded alignment of data from two queries, you can insert dummy literal columns into your `SELECT` statements. For example:

SELECT t.cola, t.colb, NULL, t.colc, NULL FROM t;

You could even have some columns reserved for the first table and others for the second table, such that they are `NULL` elsewhere (but remember that the column names come from the first query, so you may wish to ensure they're all named there):

  SELECT a, b, c, d, NULL AS e, NULL AS f, NULL AS g FROM t1
UNION ALL -- specify ALL because default is DISTINCT, which is wasted here
  SELECT NULL, NULL, NULL, NULL, a, b, c FROM t2;

You could try aligning your two queries in this fashion, then combining them with a `UNION` operator; by applying `LIMIT` to the `UNION`, you're close to achieving your goal:

  (SELECT ...)
UNION
  (SELECT ...)
LIMIT 10;

The only issue that remains is that, as presented above, 10 or more records from the first table will "push out" any records from the second. However, we can utilise an `ORDER BY` in the outer query to solve this.

Putting it all together:

(
  SELECT
    dr.request_time AS event_time, m.member_name,      -- shared columns
    dr.request_id, dr.member1, dr.member2,             -- request-only columns
    NULL AS alert_id, NULL AS alerter_id,              -- alert-only columns
      NULL AS alertee_id, NULL AS type
  FROM dating_requests dr JOIN members m ON dr.member1=m.member_id 
  WHERE dr.member2=:loggedin_id
  ORDER BY event_time LIMIT 10 -- save ourselves performing excessive UNION
) UNION ALL (
  SELECT
    da.alert_time AS event_time, m.member_name,        -- shared columns
    NULL, NULL, NULL,                                  -- request-only columns
    da.alert_id, da.alerter_id, da.alertee_id, da.type -- alert-only columns
  FROM
    dating_alerts da
    JOIN dating_alerts_status das USING (alert_id, alertee_id)
    JOIN members m ON da.alerter_id=m.member_id
  WHERE
    da.alertee_id=:loggedin_id
    AND da.type='platonic'
    AND das.viewed='0'
    AND das.viewed_time<da.alert_time
  ORDER BY event_time LIMIT 10 -- save ourselves performing excessive UNION
)
ORDER BY event_time
LIMIT 10;

Of course, now it's up to you to determine what type of row you're dealing with as you read each record in the resultset (suggest you test `request_id` and/or `alert_id` for `NULL` values; alternatively one could add an additional column to the results that explicitly states from which table each record originated, but it should be equivalent provided those `id` columns are `NOT NULL`).

Problem

Original source