0

First of all, here are my tables' CREATE TABLE:

users - has 10,192 rows

CREATE TABLE `users` (
    `auth` CHAR(32) NOT NULL,
    `name` CHAR(32) NULL DEFAULT NULL,
    `country` CHAR(32) NULL DEFAULT NULL,
    `ip` CHAR(64) NULL DEFAULT NULL,
    `lastlogin` INT(11) NOT NULL DEFAULT '-1',
    `points` FLOAT NOT NULL DEFAULT '0',
    PRIMARY KEY (`auth`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB;

playertimes - has 25,847 rows

CREATE TABLE `playertimes` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `auth` CHAR(32) NULL DEFAULT NULL,
    `map` CHAR(128) NULL DEFAULT NULL,
    `time` FLOAT NULL DEFAULT NULL,
    `jumps` INT(11) NULL DEFAULT NULL,
    `style` INT(11) NULL DEFAULT NULL,
    `date` CHAR(16) NULL DEFAULT NULL,
    `strafes` INT(11) NULL DEFAULT NULL,
    `sync` FLOAT NULL DEFAULT NULL,
    `points` FLOAT NOT NULL DEFAULT '0',
    `track` INT(11) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`),
    INDEX `time` (`time`),
    INDEX `auth` (`auth`),
    INDEX `points` (`points`),
    FULLTEXT INDEX `map` (`map`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=28399;

I have a procedure that iterates over auth in playertimes where points is over 0, and then update the points column in the users table.
The users table gets the points column update in the following way: sort (descending) all playertimes entries with the same auth, then use a sum of all the points, each row is worth 0.975x than the one before.

Example: The iteration sees 5 rows where points is (respectively) 100, 90, 80, 70, 60 for each row. So it's 100 (100%) + 87.75 (97.5%) + 76.05 (95.0625%) + 64.88 (92.6859375%) + 54.3 (90.3687890625%) resulting in a sum of around 382.98 << that should be the value of points when the per-user iteration is done!

This is the procedure:

DROP PROCEDURE IF EXISTS UpdateAllPoints;
DELIMITER ;;
CREATE PROCEDURE UpdateAllPoints()
BEGIN
    DECLARE authid VARCHAR(32);
    DECLARE done INT DEFAULT 0;
    DECLARE cur CURSOR FOR (SELECT auth FROM playertimes
                               WHERE points > 0.0 GROUP BY auth);
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    OPEN cur;
    ranks: LOOP
        FETCH cur INTO authid;
        IF done THEN
            LEAVE ranks;
        END IF;
        UPDATE users u
          JOIN ( SELECT SUM((points * (@f := 0.975 * @f) / 0.975)) total
                     FROM playertimes t
                     CROSS JOIN ( SELECT @f := 1.0 ) params
                     WHERE points > 0.0
                       AND auth = authid
                     ORDER BY points DESC
               ) temp
            SET u.points = temp.total WHERE auth = authid;
    END LOOP;
    CLOSE cur;
END;;
DELIMITER ;

For my current database, this procedure takes 49.7 seconds to run on average. I feel like it's unacceptable.

Are there any system setting or a changes could be made so it can run faster and achieve the same results? If changing the procedure into a single query can work, I absolutely prefer it.

This is another approach I've tried - it doesn't seem to work at all, even if a.auth was exposed to the subquery. But I imagine that this is how it could work, if it did.

UPDATE users u
    JOIN (SELECT auth FROM playertimes WHERE points > 0.0 GROUP BY auth) a
    JOIN ((SELECT SUM((points * (@f := 0.975 * @f) / 0.975))) total, auth
    CROSS JOIN (SELECT @f := 1.0) params
                   WHERE points > 0.0 AND auth = a.auth
                   ORDER BY points DESC) temp
        ON a.auth = temp.auth
        SET u.points = temp.total;

Edit: This is the query that works individually for an auth I specify, what I'm trying to figure out is how to make it do the same for every entry in users as long as it has an associated playertimes row with over 0.0 points; without having me to specify a value of auth in the query.

UPDATE users
    SET points = (SELECT SUM((points * (@f := 0.975 * @f) / 0.975)) FROM playertimes
    JOIN (SELECT @f := 1.0) params WHERE points > 0.0 AND auth = '<authid>' ORDER BY points DESC)
    WHERE auth = '<authid>';

Edit 2: I optimized the procedure further by applying the above query and I also optimized the database with indexes (FULLTEXT and regular indexes where I could) - the execution time cut itself from 58 seconds to 5.5! Is there any other kind of optimization that I can do to shorten it even more?

DROP PROCEDURE IF EXISTS UpdateAllPoints;
DELIMITER ;;
CREATE PROCEDURE UpdateAllPoints()
BEGIN
    DECLARE authid VARCHAR(32);
    DECLARE done INT DEFAULT 0;
    DECLARE cur CURSOR FOR (SELECT auth FROM playertimes
                                  WHERE points > 0.0 GROUP BY auth);
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    OPEN cur;
    ranks: LOOP
        FETCH cur INTO authid;
        IF done THEN
            LEAVE ranks;
        END IF;
        UPDATE users
            SET points = (
                  SELECT SUM((points * (@f := 0.975 * @f) / 0.975))
                      FROM playertimes
                      JOIN (SELECT @f := 1.0) params
                      WHERE points > 0.0
                        AND auth = authid
                      ORDER BY points DESC )
            WHERE auth = authid;
    END LOOP;
    CLOSE cur;
END;;
DELIMITER ;
  • Are there any indexes on the tables, other than the primary keys? – Philᵀᴹ Oct 24 '17 at 7:43
  • @Philᵀᴹ no, there aren't any others. – shavit Oct 24 '17 at 8:38
0

(After seeing new version...)

Instead of

INDEX `auth` (`auth`),

have

INDEX(auth, points)

There may be a way to use a "multi-table Update" to eliminate the inefficient Cursor.

-1

Instead of JOINing to playertimes, use

WHERE ...
  AND EXISTS  ( SELECT * 
                   FROM playertimes
                   WHERE points > 0.0  AND auth = x.auth )

and have

INDEX(points, auth)

The SELECT SUM... may as well be in the SET clause instead of JOINed to.

I don't understand the "commajoin" to auth, but it sounds wrong.

  • I updated the question with my current SHOW CREATE TABLE for both tables! – shavit Oct 30 '17 at 4:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.