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 &lt;= @stop 
        DO
        -- select a random float variable
        SET @RAND = RAND();
        -- update NULL field
        UPDATE customer
        SET multiplier = (SELECT 
            (CASE
                WHEN @RAND &lt;= 0.65 THEN 1.00
                WHEN @RAND &lt;= 0.90 THEN 0.85
                WHEN @RAND &lt;= 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
Create another proc to populate payment with real amounts:

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

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

WHILE @start &lt;= @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;
END//
DELIMITER ;


Duration: 0.001 sec
CALL sp_RealAmtPayment;

Duration: 17.082 sec
SELECT * FROM payment;

1000 row(s) returned
0.002 sec / 0.002 sec


Step 7 seems extremely long considering the very small number of records.  What am I missing? All comments/critiques welcome!
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 &lt;= @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)