Subquery vs Join and where - which one is faster? I have two sql's which gives me same results:

Query 1:

SELECT
u.*, COUNT(po.order_id) products_count
FROM (SELECT * FROM orders o WHERE o.date &gt;= (CURRENT_DATE() - INTERVAL 1 MONTH)) o
LEFT JOIN products_orders po ON po.order_id=o.id
JOIN users u ON u.id=o.user_id
GROUP BY po.order_id


and Query 2:

SELECT
u.*, o.id order_id, COUNT(po.order_id) products_count
FROM users u
JOIN orders o ON o.user_id=u.id
LEFT JOIN products_orders po ON po.order_id=o.id
WHERE o.date &gt;= (CURRENT_DATE() - INTERVAL 1 MONTH)
GROUP BY po.order_id


I wonder.. which one should be faster?
Query Explains shows them to be equals... What do you think guys?
You aggregation doesn't make much sense (and most databases, and even latest versions of MySQL will reject it - See ONLY_FULL_GROUP_BY)... you probably want to GROUP BY o.user_id, (and maybe, also orders_id, but then add it also on the SELECT clause) and you can have something a bit quicker if done this way:

SELECT
    u.*, c.order_count, c.products_count
FROM
    (SELECT
         o.user_id, 
         COUNT(DISTINCT o.id) AS order_count, 
         COUNT(po.order_id)   AS products_count
    FROM
        orders o
        LEFT JOIN products_orders po ON po.order_id = o.id
    WHERE 
        o.date &gt;= (now() - INTERVAL 1 MONTH)        
    GROUP BY
        o.user_id
    ) AS c
    JOIN users u ON u.id = c.user_id ;


The execution plan for this query is different, and uses a derived table (which MySQL or MariaDB aren't always very good at), but it will seek your users only once.

You can check out all the details at dbfiddle here