Group by in subquery

Question

So my problem is the following. Consider this query:

SELECT
    P.id,
    (SELECT barcode FROM product WHERE publisher_id = P.id LIMIT 1) AS barcode_sample
FROM
    publisher P
WHERE
    P.name LIKE '%tes%'

This is really about just returning the publisher ids and one barcode (any) as an example from the products. Now this query runs fast and gives what I need but I like to keep things at their place so I tried the following query:

SELECT
    P.id,
    B.barcode AS barcode_sample
FROM
    publisher P
    LEFT JOIN
    (
        SELECT
            publisher_id,
            MAX(barcode) AS barcode
        FROM
            product
        GROUP BY
            publisher_id
    ) B
        ON P.id = B.publisher_id
WHERE
    P.name LIKE '%tes%'

Which does almost the same but with a subquery join and group by. Now I'd expect MySQL to first eliminate the rows from the subquery (like a where) to only group by the publisher_id-s I need for the join. But apparently it's not that intelligent and just reads all the rows and does a resource consuming group by.

Here are the explains in order:

+----+--------------------+---------+-------+------------------+------------------+---------+--------------+-------+--------------------------+
| id | select_type        | table   | type  | possible_keys    | key              | key_len | ref          | rows  | Extra                    |
+----+--------------------+---------+-------+------------------+------------------+---------+--------------+-------+--------------------------+
|  1 | PRIMARY            | P       | index | NULL             | name             | 452     | NULL         | 94004 | Using where; Using index |
|  2 | DEPENDENT SUBQUERY | product | ref   | idx_publisher_id | idx_publisher_id | 5       | finance.P.id |   210 | Using where              |
+----+--------------------+---------+-------+------------------+------------------+---------+--------------+-------+--------------------------+

+----+-------------+------------+-------+---------------+------------------+---------+------+----------+--------------------------+
| id | select_type | table      | type  | possible_keys | key              | key_len | ref  | rows     | Extra                    |
+----+-------------+------------+-------+---------------+------------------+---------+------+----------+--------------------------+
|  1 | PRIMARY     | P          | index | NULL          | name             | 452     | NULL |    94004 | Using where; Using index |
|  1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL             | NULL    | NULL |    64829 |                          |
|  2 | DERIVED     | product    | index | NULL          | idx_publisher_id | 5       | NULL | 21713216 |                          |
+----+-------------+------------+-------+---------------+------------------+---------+------+----------+--------------------------+

My question is can I somehow achieve what I want in a semantically better (better looking) way or do I have to learn to live with this ugly but fast query?

Updated queries and examine results with a WHERE clause

Answer 1

What you seem to be looking for is a LATERAL join, which as far as I can tell is not supported by MySQL. Something like:

SELECT P.id, B.barcode AS barcode_sample
FROM publisher P
LEFT JOIN LATERAL (
    SELECT publisher_id, MAX(barcode) AS barcode
    FROM product x
    WHERE x.publisher_id = P.id
    GROUP BY publisher_id
) B
    ON P.id = B.publisher_id
WHERE P.name LIKE '%tes%'

can be used if the DBMS supports LATERAL. Some optimizers will push the predicates inside the sub-query even without LATERAL, but I don't think MySQL's optimizer does.

Anyhow, I'm not sure why you need to join against a sub-select, I believe the following should work:

SELECT P.id, MAX(B.barcode) AS barcode_sample
FROM publisher P
LEFT JOIN product B
    ON P.id = B.publisher_id
WHERE P.name LIKE '%tes%'
GROUP BY P.id 

Answer 2

Both need INDEX(publisher_id, barcode). The subquery will be only slightly faster with this index, but the JOIN version may be significantly faster with it.

Unfortunately, P.name LIKE '%tes%' requires scanning the entire table P.

As for "ugly", I see both as ugly. LIMIT 1 picks whatever happens to be first. BTW, adding my index may change the result. MAX implies that you want the 'last' in some sense. Neither quite conveys "just give me any barcode". To temper the ugliness, you say "barcode_sample".

Anyway, Lennart's second suggestion is also 'good'. I vote for it because it avoids subqueries.

But be cautious of GROUP BY if you actually have more columns in the SELECT.