mysql query: Incorrect key file for table

Question

I run this query:

SELECT v.autor, v.titlu,  'http://85.25.176.18/resursecrestine-download' + v.`link` 
FROM  `video_resurse` v, predicimp3 p
WHERE v.titlu = p.titlu
ORDER BY v.autor

all it's ok.

But when I replace "=" with "!=" it takes very long...and instead of results it gives me:

#126 - Incorrect key file for table '/tmp/#sql_42c5_0.MYI'; try to repair it

Why?

DrewM · Accepted Answer · 2010-01-15 22:28:09Z

1

You equal is a join, which typically returns a fairly small set of things. If you replace the = with !=, your are forcing the DB to do a CROSS JOIN and then filter through that to exclude any records that are equal.

To do the CROSS JOIN the DB probably needs to make a temp table with a total number of rows equal to (number of rows in video_resurse) * (number of rows in predicimp3). If either of those tables has a large number of rows in it, then the temp table is likely to be very large and take a long time to generate. Hence the slow performance.

The error you are seeing is likely indicating that you ran our of space in the /tmp directory (which is where mysql puts its temp tables by default) which can cause that error.

answered Jan 15, 2010 at 22:28

DrewM

1,90614 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Cristian Boariu Over a year ago

You were absolutely right. There was not enough space for the sql query. thanks a lot.

Collectives™ on Stack Overflow

mysql query: Incorrect key file for table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related