Count group errcode 28 - No space left on device
Helo,
I am using this code to count the repetitions of blocks in a column called <call_code>. Succeeded.
mysql> set @x=0;
mysql> set @data="";
mysql> SELECT call_code,
-> countnum,
-> COUNT(1) iterations
-> FROM (SELECT call_code,
-> COUNT(num) countnum
-> FROM (SELECT call_code,
-> IF(@data = call_code, @x, @x := @x + 1) AS num,
-> IF(@data = call_code, "", @data := call_code) AS info
-> FROM genotypes
-> ORDER BY call_code, array_experiment_id LIMIT 100) x
-> GROUP BY call_code,
-> num) AS y
-> GROUP BY call_code,
-> countnum;
I added a few more columns (array_experiment_id, Chromosome)
mysql> set @x=0;
mysql> set @data="";
mysql> SELECT array_experiment_id, chromossome, call_code,
-> countnum,
-> COUNT(1) iterations
-> FROM (SELECT array_experiment_id, chromossome, call_code,
-> COUNT(num) countnum
-> FROM (SELECT array_experiment_id, chromossome, call_code,
-> IF(@data = call_code, @x, @x := @x + 1) AS num,
-> IF(@data = call_code, "", @data := call_code) AS info
-> FROM genotypes
-> ORDER BY call_code, array_experiment_id) x
-> GROUP BY call_code,
-> num) AS y
-> GROUP BY call_code,
-> countnum;
The code works, but not with the 38 million records.
Making all records ... it shows an error (errcode 28).
It works well only when I add a LIMIT 1000.
-> ORDER BY call_code, array_experiment_id LIMIT 1000) x
What should I take initiative?
thanks.