Modifying `sakila` database

This is not "real life" code. I'm trying to expand upon the well known Sakila sample database for MySQL to make it more complex. Step 7 (or 6) is running surprisingly slow.

PS: Note these are all separate queries executed against the same database in the order specified.

-
Add columns to sakila.customer table:

USE sakila;
ALTER TABLE customer
    ADD COLUMN multiplier DECIMAL(3,2) AFTER active;
ALTER TABLE customer
    ADD COLUMN cust_ranking VARCHAR(10) AFTER multiplier;

Duration: 0.289 sec

-
Create a proc to randomly distribute multiplier:

DROP PROCEDURE IF EXISTS sp_randCustMult;
DELIMITER //
CREATE PROCEDURE sp_randCustMult()

BEGIN
    -- declare a counter
    SET @start = (SELECT MIN(customer_id) FROM customer);
    SET @stop  = (SELECT MAX(customer_id) FROM customer);

    -- start while loop
    WHILE @start <= @stop 
        DO
        -- select a random float variable
        SET @RAND = RAND();
        -- update NULL field
        UPDATE customer
        SET multiplier = (SELECT 
            (CASE
                WHEN @RAND <= 0.65 THEN 1.00
                WHEN @RAND <= 0.90 THEN 0.85
                WHEN @RAND <= 1.00 THEN 1.05
                END))
        WHERE customer_id = @start;
            -- tick counter one up
            SET @start = @start + 1;
    END WHILE;
END//
DELIMITER ;

Duration: 0.001 sec

-
Call the proc to populate the rows:

CALL sp_randCustMult;

Duration: 0.761 sec

-
With Safe Update Mode OFF, add human-friendly values to cust_ranking based on multiplier.

UPDATE customer
    SET cust_ranking = 'Standard'
    WHERE multiplier = 1.00;
UPDATE customer
    SET cust_ranking = 'Premium'
    WHERE multiplier = 0.85;
UPDATE customer
    SET cust_ranking = 'Uplift'
    WHERE multiplier = 1.05;

Duration:

0.057 sec
0.037 sec
0.035 sec

-
Add a real_amount column to table payment:

ALTER TABLE payment
ADD COLUMN real_amount DECIMAL(5,2)
AFTER amount;

Duration: 0.749 sec

-

I've never played with mysql, so this may be completely wrong, but if I get it right the WHILE loop would be the equivalent of a T-SQL CURSOR, which is inherently slow.

You're essentially looping on payment_id, incrementing at each iteration - this assumes the ID's are contiguous, which isn't a safe assumption to make with data: if records were deleted, you have more iterations than records:

SET @start = (SELECT MIN(payment_id) FROM payment);
SET @stop  = (SELECT MAX(payment_id) FROM payment);

WHILE @start <= @stop
DO
    UPDATE payment AS p
        INNER JOIN customer AS c
            ON p.customer_id = c.customer_id
    SET real_payment = (p.amount * c.multiplier)
    WHERE p.payment_id = @start;
    SET @start = @start + 1;
END WHILE;

In pseudo-code, this can read as follows:

• For each payment_id in payment...

• ... update the real_payment column to p.amount*c.multiplier

I don't see why you need a loop to do this, I think this would be equivalent... and faster:

UPDATE payment AS p
    INNER JOIN customer AS c
        ON p.customer_id = c.customer_id
SET real_payment = (p.amount * c.multiplier)

StackExchange Code Review Q#51603, answer score: 7