Tweaked to ...
- delete the table if it exists before trying to create it,
- just display the timediffs rather than save them, and
- also use fractional seconds
... your sproc executes in less than 4 secs running across a LAN to a MySQL 5.6 installation on a fairly slow machine.
drop procedure if exists sp_table_create;
delimiter go
create procedure sp_table_create(loopfrom int(5) unsigned, loopto int(5) unsigned)
begin
declare countloop int(5) unsigned;
declare start_time, end_time bigint;
if (loopfrom <= loopto) then
set countloop=loopfrom,
@tstart=now(6),
start_time = conv(concat(substring(uuid(),16,3),substring(uuid(),10,4),
substring(uuid(),1,8)),16,10) div 10 - (141427 * 24 * 60 * 60 * 1000000);
repeat
set @sqlstr = concat('drop table if exists table_',countloop);
prepare stmt from @sqlstr;
execute stmt;
deallocate prepare stmt;
set @sqlstr = concat('create table table_',countloop,' (`column1` int(5))');
prepare stmt from @sqlstr;
execute stmt;
deallocate prepare stmt;
set countloop = countloop + 1;
until countloop > loopto end repeat;
set @tfinish=now(6),
end_time = conv(concat(substring(uuid(),16,3),substring(uuid(),10,4),
substring(uuid(),1,8)),16,10) div 10 - (141427 * 24 * 60 * 60 * 1000000);
select concat("table ",loopfrom," to table ",loopto," created.");
select start_time, end_time, end_time-start_time, @tstart, @tfinish, timediff(@tfinish,@tstart);
end if;
end;
go
delimiter ;
call sp_table_create(1,100);
+-----------------------------------------------------------+
| concat("table ",loopfrom," to table ",loopto," created.") |
+-----------------------------------------------------------+
| table 1 to table 100 created. |
+-----------------------------------------------------------+
1 row in set (3.75 sec)
+------------------+------------------+---------------------+----------------------------+----------------------------+----------------------------+
| start_time | end_time | end_time-start_time | @tstart | @tfinish | timediff(@tfinish,@tstart) |
+------------------+------------------+---------------------+----------------------------+----------------------------+----------------------------+
| 1421081490553621 | 1421081494296830 | 3743209 | 2015-01-12 10:51:53.250000 | 2015-01-12 10:51:56.984375 | 00:00:03.734375 |
+------------------+------------------+---------------------+----------------------------+----------------------------+----------------------------+
1 row in set (3.80 sec)
Query OK, 0 rows affected (3.92 sec)
Not sure why your time is so slow. Profiling could have a little to do with it. Are reads or writes generally slow on that machine?
Edited 1 time(s). Last edit at 01/12/2015 03:27PM by Peter Brawley.