4.2. Extra: Updating (Database)

Database updates

2022-05-22 suggested sql alters

alter table playertimes modify style TINYINT NOT NULL DEFAULT 0 after id;
alter table playertimes modify track TINYINT NOT NULL DEFAULT 0 after style;
alter table playertimes modify time FLOAT NOT NULL after track;
alter table playertimes modify auth INT NOT NULL after time;
alter table playertimes modify map varchar(255) not null after auth;
alter table playertimes modify points FLOAT NOT NULL DEFAULT 0 after map;
alter table playertimes modify points_calced_from FLOAT NOT NULL DEFAULT 0 after points;
alter table playertimes modify exact_time_int INT DEFAULT 0 after points_calced_from;
optimize table playertimes;
drop index map on playertimes;
alter table playertimes add index map (style, track, map, time);
alter table playertimes add index map2 (map);
alter table playertimes add index auth (auth, date, points);
alter table playertimes add index auth2 (auth, map);

alter table maptiers modify map varchar(255) not null;
optimize table maptiers;

alter table mapzones modify map varchar(255) not null;
alter table mapzones modify track tinyint not null after type;
optimize table mapzones;

Rankings (mostly outdated)

With the addition of the revamped rankings system (pull request #474, commit a1629d8), bhoptimer received an optimization update.

This update added indexes to the database, and reduced overall I/O for many things. The update only affects new bhoptimer databases and is problematic to apply retroactively. You may manually apply the update by executing the following queries in your database. You may choose to only apply specific parts of this update, but I recommend applying everything.

* Most of the update only applied to MySQL installations. You may manually run those queries on an SQLite database under your own risk. * The table names might be different than yours, if you happen to use your own prefix.

/* chat */
ALTER TABLE chat MODIFY COLUMN auth CHAR(32);
ALTER TABLE chat MODIFY COLUMN ccname CHAR(128);
ALTER TABLE chat MODIFY COLUMN ccmessage CHAR(16);

/* maptiers */
ALTER TABLE maptiers MODIFY COLUMN map CHAR(128);

/* mapzones */
ALTER TABLE mapzones MODIFY COLUMN map CHAR(128);
ALTER TABLE mapzones ADD INDEX `map` (`map`);

/* playertimes */
ALTER TABLE playertimes MODIFY COLUMN auth CHAR(32);
ALTER TABLE playertimes MODIFY COLUMN map CHAR(128);
ALTER TABLE playertimes MODIFY COLUMN style TINYINT;
ALTER TABLE playertimes MODIFY COLUMN track TINYINT;
ALTER TABLE playertimes ADD INDEX `map` (`map`, `style`, `track`);
ALTER TABLE playertimes ADD INDEX `auth` (`auth`, `date`, `points`);
ALTER TABLE playertimes ADD INDEX `time` (`time`);

/* users */
ALTER TABLE users MODIFY COLUMN auth CHAR(32);
ALTER TABLE users MODIFY COLUMN name CHAR(32);
ALTER TABLE users MODIFY COLUMN country CHAR(32);
ALTER TABLE users MODIFY COLUMN ip CHAR(64);
ALTER TABLE users ADD INDEX `points` (`points`);