fn build_temporary_table(&self, tx: &mut Transaction) -> Result<(), Error> {
const CREATE_INUSE_UUIDS_SQL: &str = r"CREATE TEMPORARY TABLE uuids_in_use (
region_loc_x INT NOT NULL,
region_loc_y INT NOT NULL,
region_size_x INT NOT NULL,
region_size_y INT NOT NULL,
asset_type VARCHAR(20) NOT NULL,
asset_uuid CHAR(36) DEFAULT NULL,
asset_hash CHAR(8) NOT NULL)";
const INSERT_INUSE_UUIDS_SQL: &str = r"INSERT INTO uuids_in_use
(region_loc_x, region_loc_y, region_size_x, region_size_y, asset_type, asset_uuid, asset_hash)
VALUES
(:region_loc_x, :region_loc_y, :region_size_x, :region_size_y, :asset_type, :asset_uuid, :asset_hash)";
// Get all the active UUIDs. In memory all at once, but under 1MB
let active_uuids = self.get_uuids_in_use(tx)?;
log::info!("{} active UUIDs: {:?}", active_uuids.len(), &active_uuids[0..5.min(active_uuids.len())]);
// Create the temporary table
tx.query_drop(CREATE_INUSE_UUIDS_SQL)?;
log::info!("Temporary table created.");
// Put all the records in the temporary table.
tx.exec_batch(
INSERT_INUSE_UUIDS_SQL,
active_uuids.iter().map(|p| params! {
"region_loc_x" => p.region_loc[0],
"region_loc_y" => p.region_loc[1],
"region_size_x" => p.region_size[0],
"region_size_y" => p.region_size[1],
"asset_uuid" => p.asset_uuid.to_string(),
"asset_hash" => p.asset_hash.clone(),
"asset_type" => p.asset_type.to_str().to_string(),
})
)?;
log::info!("Temporary table filled.");
Ok(())
}
03:09:00 [INFO] 406 active UUIDs: ...
03:09:00 [INFO] Temporary table created.
03:09:26 [INFO] Temporary table filled.
For some reason, MySQL called from Rust took 26 seconds to insert 406 records into an empty temporary table. SELECT is fast, but INSERT is insanely slow. 15 records per second.
I tried ENGINE MEMORY (the data doesn't even go out to disk) and got
03:15:19 [INFO] Temporary table created.
03:15:45 [INFO] Temporary table filled.
so it still took 26 seconds. Same as with the default InnoDB engine. This suggests it's not the database engine that's the problem.
This is Dreamhost shared hosting, being tested using remote access to the MySQL database from the "mysql" crate.
Is there something really slow about how Rust does this? Extreme throttling of external INSERT operations on Dreamhost? Something else?