How to get the most recent result sets when grouping key and date are in separate tables

Question

Good evening! I'm having some difficulty. I'm trying to obtain the most recent row from a few tables using SQL. I can do this using a loop in code, but it requires a lot of round trips to the database and results in slow performance.

Would someone be able to help me out?

There are a number of questions on stackoverflow that utilize the MAX method, but in those cases the id column and date column are in the same table. I'm not sure how to get that to work in my case.

Here is the create code for my tables. Note: I've renamed the columns and tables to be generic.

Table Creation

CREATE TABLE IF NOT EXISTS `table_2` (
    `table1_id` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
    `table2_id` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
    `needed_value_1` varchar(254) COLLATE utf8mb4_unicode_ci NOT NULL,
    `needed_value_2` varchar(254) COLLATE utf8mb4_unicode_ci NOT NULL,
    `comparison_int` smallint(5) unsigned NOT NULL,
    `needed_limiting_date` datetime DEFAULT NULL,
    PRIMARY KEY (`table2_id`,`table1_id`),
    UNIQUE KEY `table1_id` (`table1_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS `table_1` (
    `owned_by_id` int(10) unsigned NOT NULL,
    `table1_uuid` char(36) COLLATE utf8mb4_unicode_ci NOT NULL,
    `table1_id` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    `needed_value_3` varchar(254) COLLATE utf8mb4_unicode_ci NOT NULL,
    `comparison_bool` tinyint(1) NOT NULL,
    `comparison_bool2` tinyint(1) unsigned NOT NULL,
    `external_id` int(10) unsigned NOT NULL,
    `needed_date` datetime NOT NULL,
    PRIMARY KEY (`owned_by_id`,`table1_uuid`),
    UNIQUE KEY `table1_uuid` (`table1_uuid`),
    UNIQUE KEY `table1_id` (`table1_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `external_table` (
    `owned_by_id` int(10) unsigned NOT NULL,
    `external_id` int(10) unsigned NOT NULL,
    PRIMARY KEY (`owned_by_id`,`external_id`) USING BTREE,
    KEY `owned_by_id` (`owned_by_id`),
    KEY `external_id` (`external_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Code That Works but is not performant

So here is what I would do in pseudocode.

// create a results array
results = []

// get a list of external ids
external_ids = get_data('SELECT external_id FROM external_table WHERE owned_by_id = {id_provided_via_param}');

// loop through ids and get the most recent information per each, add to results
foreach(external_ids as external_id){
    sql_to_run = 'SELECT t1.needed_value_3, t1.external_id, t1.needed_date, t2.needed_value_1, t2.needed_value_2, t2.needed_limiting_date
                    FROM table_1 AS t1
                             INNER JOIN table_2 AS t2 ON t2.table1_id = t1.table1_id
                    WHERE t1.comparison_bool = 1
                      AND t1.comparison_bool_2 = 0
                      AND t1.external_id = {external_id_param}
                      AND t2.`comparison_int` = 0
                      AND t2.needed_limiting_date <= {given_date_param}
                    ORDER BY needed_limiting_date DESC
                    LIMIT 1'
                    
    results.push(get_data(sql_to_run))
}

return results

That works to get what I need, the most recent information from the database for each external_id.

Primary Question

How would I do this same thing in SQL, so that a resultset is returned consisting of the most recent information for each external id?

Limitations

• The user has read access, the user cannot create Temporary Tables.
• The user cannot modify any schema.

Thank you for any help you can provide.

Answer 1

These indexes should help performance:

t1:  INDEX(comparison_bool, comparison_bool_2, external_id, table1_id)
t2:  INDEX(comparison_int, needed_limiting_date, table1_id)

To make it more performant, you really need to JOIN to external_table too. See "groupwise-max" for more tips on such.