Er, don't know why this is. Just tested earlier script on my database and it worked fine albeit took a long time to run. Possible you interrupted the processing?
Script below is tested and works for me. Changes from earlier version are:
- added "smartform" database specifier to all tabe references just in case your MySQL instance was defaulting to a different database.
- added the clause "and race_id > 700000" to insert select query. This is an arbitrary cutoff for testing purposes that limits the insert to 10000 rows or so and thereby shortens the run-time a lot. You'll need to remove this when running a 'for real' query.
- added a select all statement for the competitiveness table to return results at the end.
DROP TABLE IF EXISTS smartform.competitiveness;
CREATE TABLE smartform.competitiveness (
`runner_id` INT NOT NULL,
`name` VARCHAR(255) NOT NULL,
`number_of_races` INT NOT NULL,
PRIMARY KEY (`runner_id`));
INSERT INTO smartform.competitiveness
select runner_id, name, count(runner_id) from smartform.historic_runners
where ((unfinished not like _latin1'Non%') or isnull(unfinished))
and race_id > 700000
group by runner_id, name;
select * from smartform.competitiveness;
This ran and returned results in under 2 seconds on my desktop MySQL server. Hope it works for you.
