cross self join to get more rows - better solution?

Question

I have a table employees:

+----------------+------------------+
| Field          | Type             |
+----------------+------------------+
| id             | int(10) unsigned |
| persons_id     | int(10) unsigned |
| restaurants_id | int(10) unsigned | 
+----------------+------------------+

and a table bills:

+----------------+------------------+
| Field          | Type             |
+----------------+------------------+
| id             | int(10) unsigned |
| customers_id   | int(10) unsigned |
| employees_id   | int(10) unsigned |
| restaurants_id | int(10) unsigned |
| date           | date             |
+----------------+------------------+

I want to insert 10,000 rows with an employees_id, restaurant_id and a date into the table bills by using cross joins. The table employees is filled with 15 rows (employees).

Currently I do following:

insert into bills (employees_id, restaurants_id, date) select e.id, e.restaurants_id, '2012-01-01' from employees e cross join employees e2 cross join employees e3 cross join employees e4 order by RAND() limit 10000;

With this query I get 50,625 rows (without to limit to 10,000) (15rows*15rows*15rows*15rows=50,625rows). Is there an more elegant solution to get 10,000 rows? So I don't have to write X times cross join employees eX.

I'm using MySQL. Thanks.

Answer 1

No, their is no better way then cross joining on yourself in MySQL. You can make the SQL slightly more elegant using, say, a CTE in other RDBMS, but what you have is the best way IMO.

By "best", I include "efficient" too: set based/no loops, and no temp tables

Answer 2

CREATE TABLE emp1 SELECT id,restaurants_id FROM employees WHERE 1=2;
INSERT INTO emp1 SELECT id,restaurants_id FROM employees;
INSERT INTO emp1 SELECT * FROM emp1;
INSERT INTO emp1 SELECT * FROM emp1;
INSERT INTO emp1 SELECT * FROM emp1;
INSERT INTO emp1 SELECT * FROM emp1;
INSERT INTO emp1 SELECT * FROM emp1;
INSERT INTO emp1 SELECT * FROM emp1;
INSERT INTO emp1 SELECT * FROM emp1;
INSERT INTO emp1 SELECT * FROM emp1;
INSERT INTO emp1 SELECT * FROM emp1;
INSERT INTO emp1 SELECT * FROM emp1;
CREATE TABLE emp2 SELECT id,restaurants_id FROM employees WHERE 1=2;
ALTER TABLE emp2 ADD rnd INT NULL;
INSERT INTO emp2 (id,restaurants_id,rnd)
SELECT id,restaurants_id,RAND()*10000 FROM emp1 LIMIT 10000;
ALTER TABLE emp2 ADD INDEX (rnd);
DROP TABLE emp1;
INSERT INTO bills (employees_id, restaurants_id, date)
SELECT id,restaurants_id FROM emp2 ORDER BY rnd;

Answer 3

You can use WHILE loop to do this.

SET num = 0

WHILE num < 10000 do

    INSERT INTO bills (employees_id, restaurants_id, date)
    SELECT id, restaurants_id, '2012-01-01' FROM employees WHERE id = 1 + RAND() * 16;

    SET num = num + 1

END WHILE;

In the above example, it selects a random employee where his id is between 1 and 15 (you said you have only 15 of them). This assumes that your employee IDs are sequential.

Answer 4

I know you said mysql, but in postgresql you've got common table expressions (CTE) and you can do stuff like this:

WITH RECURSIVE source (counter) AS ( SELECT 1 UNION ALL SELECT counter + 1 FROM source where counter < 10000)

(--Your awesome query goes here)

FROM source;

So the query gets repeated 10,000 times

Does mysql have something alike?