[SOLVED] Slow query takes up entire HDD space resulting in a "1030 Got error 28 from storage engine"

Slow query takes up entire HDD space resulting in a "1030 Got error 28 from storage engine"

Fairly new to MySQL.

Slow query takes up the entire HDD space ending up with 1030 error code.

INSERT INTO schema.Table C
SELECT a.`Date`, a.Store, a.SKU, 
       floor((a.QTY / ((b.CASEQTY * b.CASEPERLAYER) * b.LAYERPERPALLET))) AS Pallets,
       floor(((a.QTY / ((b.CASEPERLAYER * b.LAYERPERPALLET) * b.CASEQTY)) /.CASEQTY)) AS Cases,
       (a.QTY * b.CASEQTY) AS Pieces
FROM
    (schema.table1 AS a
    INNER JOIN schema.table2 AS b)
    WHERE a.Description = 'BLAH';

Problem:

When I run the above query I get the results I need in 0.01 sec with a limit of 100 rows. However, When I try to insert the query into a prepared table it fails.

The above query will basically run for hours until the HDD is full. Table A contains millions of records and table B only a few thousand. Storage engine is InnoDB. I've run a similar query for 3hrs and have had it succeed. Any help will be greatly appreciated.

Solution

Your inner join statement contains no join criteria. This will result in something (bad) called a "cartesian product". So, if table A has a million records and table b contains a thousand, then a cartesian product will match each row in table A to EVERY row in the other table. This should give you (at least) a billion records.

To fix this, you need to define/constrain the relationship between the two tables by using an "ON" clause for your join or it could go in the WHERE clause.