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

Question

For a dating application, I have several tables that I need to query for the same output with LIMIT 10 of both queries combined. This is difficult to do at the moment, although this is not a problem for their request separately, but LIMIT 10 will not work, since the numbers are not exact (for example, not LIMIT 5 and LIMIT 5, one request can return 0 rows, and the remaining 10, in depending on the scenario).

members table member_id | member_name ------------------------ 1 Herb 2 Karen 3 Megan dating_requests request_id | member1 | member2 | request_time ---------------------------------------------------- 1 1 2 2012-12-21 12:51:45 dating_alerts alert_id | alerter_id | alertee_id | type | alert_time ------------------------------------------------------- 5 3 2 platonic 2012-12-21 10:25:32 dating_alerts_status status_id | alert_id | alertee_id | viewed | viewed_time ----------------------------------------------------------- 4 5 2 0 0000-00-00 00:00:00 

Imagine that you are Karen and have just logged in, you should see these two elements:

 1. Herb requested a date with you. 2. Megan wants a platonic relationship with you. 

In one query with LIMIT 10. Instead, you need two queries that need to be combined:

 1. Herb requested a date with you. -> query = "SELECT dr.request_id, dr.member1, dr.member2, m.member_name FROM dating_requests dr JOIN members m ON dr.member1=m.member_id WHERE dr.member2=:loggedin_id ORDER BY dr.request_time LIMIT 5"; 2. Megan wants a platonic relationship with you. -> query = "SELECT da.alert_id, da.alerter_id, da.alertee_id, da.type, da.alert_time, m.member_name FROM dating_alerts da JOIN dating_alerts_status das ON da.alert_id=das.alert_id AND da.alertee_id=das.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 da.alert_time LIMIT 5"; 

Again, sometimes both tables may be empty, or 1 table may be empty or both filled (where LIMIT 10 is triggered) and ordered by time. Any ideas on how to get a request to efficiently complete this task? Thoughts, tips, chimes, optimization are welcome.

Answer 1

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 the data type, but also in their semantic meaning; however, MySQL does not care about semantics and will process different types of data, switching to something more general - therefore, if necessary, you can overload the columns to have different values ​​from each table, and then determine what meaning suits your higher level code (although I do not recommend doing so).

When the number of columns is different or when you want to achieve more / less overloaded data alignment from two queries, you can insert dummy literal columns in your SELECT . For example:

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

You may even have several reserved columns for the first table, and others for the second table, so they are NULL in another place (but remember that the column names come from the first query, so you can make sure 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 can try to reconcile your two queries this way, and then combine them with the UNION operator; applying LIMIT to UNION , you are close to achieving your goal:

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

The only problem that remains is that, as shown above, 10 or more records from the first table will “squeeze” any records from the second. However, to solve this issue, we can use ORDER BY in an external query.

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 decide what type of row you mean when reading each record in the result set (suppose you are testing request_id and / or alert_id for NULL values, alternatively you can add an extra column to the results that clearly indicate which table each entry has occurred, but it should be equivalent if these columns are id NOT NULL ).