Create structure tables within a transaction in a single PHP process (optimization)

[nathanweeks]

Instead of calling the study_structures_script.php multiple times (once for each structure), it would be likely be faster for a single PHP process to create all structure tables in a transaction; e.g., consider the following (shell script) benchmark that creates 1000 structure tables within a transaction:

$ cat create.sh
#!/bin/bash
id=1
(
  echo 'START TRANSACTION;'
  id=1
  for structure_id in {1..1000}
  do
    echo "
    CREATE TABLE tblStudy${id}Structure${structure_id}SNPs (
        id int(11) unsigned NOT NULL AUTO_INCREMENT,
        position int(11) DEFAULT NULL,
        names json DEFAULT NULL,
        reference char(1) DEFAULT NULL,
        alternate json DEFAULT NULL,
        results json DEFAULT NULL,
        PRIMARY KEY (id),
        KEY k_position (position)
    ) ENGINE=InnoDB;"
  done
  echo 'COMMIT;'
) | mysql dbCuratorTools

$ docker-compose up -d mysql
Creating network "curatortools_default" with the default driver
Creating curatortools_mysql_1 ... done
$ docker cp create.sh curatortools_mysql_1:/tmp
$ docker-compose exec mysql bash -c 'time bash /tmp/create.sh'
real	0m7.827s
user	0m0.071s
sys	0m0.075s