MySQL:Old-Style JOIN Syntax is Required for Correlated Subqueries

Notice in the query below, the JOIN/ON syntax we’re all comfortable with does NOT work for correlating subqueries. You must use the old WHERE/= style of joining tables. For example:

SELECT    
    (        # The old-style join syntax used here is REQUIRED for the correlated subquery:
            SELECT COUNT(DISTINCT uus.id) as uus_count
            FROM user_unit_subscription uus, sims_organization so, user u, sims_user su         
            WHERE so.id = c.sims_organization_id
            AND uus.license_unit_id = lu.id
            AND u.id = uus.user_id
            AND su.id = u.sims_id
            AND su.enable = 1
            AND so.deleted = 0
            AND su.organization_id = so.id         
            GROUP BY uus.license_unit_id
     ) as correct_sub_count
FROM customer_order_subscription cos
JOIN customer_order co 
ON cos.customer_order_id = co.id
JOIN customer c 
ON co.customer_id = c.id
JOIN license_program lp 
ON co.license_program_id = lp.id
JOIN license_unit lu 
ON cos.license_unit_id = lu.id
JOIN customer_unit_subscription cus 
ON cos.license_unit_id = cus.license_unit_id 
AND cus.customer_id = c.id
WHERE cos.customer_order_id = customer_order_id;

MySQL MERGE and TEMPTABLE Algorithms for Views

I’ve learned a bit more about views in MySQL, and it’s worth mentioning that in some cases they can be far less efficient than their underlying query.

One thing to try to do with any view you build is add the following to the CREATE portion:

CREATE ALGORITHM = MERGE VIEW v_whatever

If you get a warning that MySQL can’t use the MERGE algorithm, be sure to test your view for performance. In this case, it uses the TEMPTABLE algorithm.

Some restrictions on using MERGE are when you have an aggregate in the SELECT, and also when using ORDER BY.

MySQL Transactions — The Right Way

DROP PROCEDURE IF EXISTS mytestproc;
 
DELIMITER $$
 
CREATE PROCEDURE mytestproc()
BEGIN
    START TRANSACTION;
    SAVEPOINT x;
    
    BEGIN
        DECLARE EXIT HANDLER FOR SQLEXCEPTION    
        BEGIN
            ROLLBACK TO SAVEPOINT x; -- Rollback on exception    
            RESIGNAL; -- Rethrow the error (if you want to raise it upstream, i.e., in .NET/java code)
        END;
        
        #SAVEPOINT x; #named savepoint      
        
        SELECT 1;
    #Other statements here
    END;
    
    COMMIT;
    # Clean Up
    RELEASE SAVEPOINT x;
END$$    
 
DELIMITER ;
 
CALL mytestproc();