create database join_ex1;

use join_ex1;

create table site (
	id integer auto_increment not null,
	name varchar(100),
	primary key (id)
) type=innodb;

create table site_freq (
	site_id integer
) type=innodb;

create table hit (
	id integer auto_increment not null,
	site_id integer,
	primary key (id)
) type=innodb;

create index index1 on hit (site_id);

insert into site (name) values ('Slashdot');
insert into site (name) values ('Digg');
insert into site (name) values ('Engadget');
insert into site (name) values ('Gizmodo');

insert into site_freq (site_id) values (1),(1),(1),(1),(2),(2),(3),(4);

DROP PROCEDURE IF EXISTS insertRandomData;
DELIMITER //
CREATE PROCEDURE insertRandomData(toInsert INT)
BEGIN
	DECLARE i INT DEFAULT 0;
	DECLARE myId INT;
	WHILE (i < toInsert) DO
		SELECT site_id INTO myId FROM site_freq ORDER BY rand() LIMIT 1;
  		INSERT INTO hit (site_id) VALUES (myId);
	SET i=i+1;
	END WHILE;
END;
//
DELIMITER ;

CALL insertRandomData(500000);

SELECT name, count(*) FROM hit, site WHERE site_id = site.id GROUP BY name;

SELECT name, count(*) FROM hit join site on site_id = site.id GROUP BY name;

SELECT name, mycount FROM (
	SELECT site_id, count(*) mycount from hit group by site_id
) a, site
WHERE site.id = a.site_id
ORDER BY site_id;