mysql query problem

Question

Hi,

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 

Do you know why?

Answer 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.