MySQL: create tables for joins with random rows

Beginners may want to first read MySQL: Create Random Data Text Strings

Create Tables For Joins

To create sample tables suitable for joins:
  CREATE TABLE `foos` (
    `id` int(11) NOT NULL,
    `name` char(20),
    `bar_id` int(11),
    PRIMARY KEY (`id`)
  );

  CREATE TABLE `bars` (
    `id` int(11) NOT NULL,
    `name` char(20),
    PRIMARY KEY (`id`)
  );

Insert Random Data

To fill each of the tables with a million rows of random data:
  delimiter $$  
  create procedure randomizer()
    begin
      declare i int Default 0 ;
      declare random char(20) ;
      myloop: loop
      set random=conv(floor(rand() * 99999999999999), 20, 36) ;
      insert into `foos` (`id`, `name`, `bar_id`) VALUES (i+1,random,i+1) ;
      insert into `bars` (`id`, `name`) VALUES (i+1,random) ;
      set i=i+1;
      if i=1000000 then
        leave myloop;
	end if;
    end loop myloop;
  end $$
  delimiter ;
  call randomizer();
Note that we ensure foos.id==bars.id and foos.name==bars.id because this makes it easier for us to see our joins are working correctly.

InnoDB Engine vs. MyISAM Engine

We compared creating the tables using the InnoDB engine vs. the MyISAM engine.

The results of calling randomizer() are quite different:

Command InnoDB MYSAM
call randomizer() 35.5 min 1.67 min

InnoDB is much slower. Do you know why?
Please let us know in the comments below.



What's Next?

blog comments powered by Disqus