Working on a simple time sheet webapp, I have created the following query (simplified - I actually have several mapped columns of a similar type to project_id) to generate test data:
INSERT INTO `entries` (`entry_id`, `user_id`, `project_id`, `date`, `comment`, `hours`)
VALUES
( null,
0,
(SELECT `project_id` FROM projects ORDER BY RAND() LIMIT 1),
CURRENT_DATE(),
'# TEST DATA #',
(SELECT ROUND((0.25 + RAND() * (24 - 0.24)), 2))
);
Currently, I'm running this query inside a PHP loop.
This code is currently adequate for my needs now, as I can iterate 50 loops in 0.3037 seconds.
However, I fear that when it comes time to test large data sets (searching and report generation for rows > 1 000 000), I may run into problems creating them.
How can I optimize this algorithm? Should I consider using a stored procedure?
Or should I just not worry about it, and run the loop to longer iterations, more times?
My stored procedure attempt
Following loki2302's suggestion, I wrote this stored procedure:
DROP PROCEDURE IF EXISTS `create_test_entries`;
DELIMITER //
CREATE PROCEDURE `create_test_entries` (IN number INT)
LANGUAGE SQL
DETERMINISTIC
SQL SECURITY INVOKER
COMMENT 'Creates `number` of random test entries in the timesheet'
BEGIN
DECLARE i INT;
SET i = 0;
WHILE i < number DO
INSERT INTO `entries` (`entry_id`, `user_id`, `project_id`, `department_id`, `task_id`, `date`, `comment`, `hours`)
VALUES
( null,
0,
(SELECT `project_id` FROM projects ORDER BY RAND() LIMIT 1),
CURRENT_DATE(),
'# TEST DATA #',
(SELECT ROUND((0.25 + RAND() * (24 - 0.24)), 2))
);
SET i = i + 1;
END WHILE;
END //
Comments?
