Incorrect key file for table mysql errors

[NeverBored]

Suddenly I'm getting a bunch of these errors every day for the past week. It's not constant, but seemingly random I'll get 1+ error emails at a time. From the looks of it I'm not sure that it's a VB problem directly, but I'm really just hoping someone can help me out. I don't understand where the table it quotes is or how to repair it... The query listed is a plugin that grabs a list of 5 threads, is there something wrong with it? It works, and I've been using it for like a year.

Invalid SQL:
SELECT * FROM thread WHERE ytvideo AND forumid IN (13) AND open=1 ORDER BY RAND() DESC LIMIT 5;

Incorrect key file for table '/var/mysqltmp/#sql_45a7_6.MYI'; try to repair it

[kh99]

I'm guessing that's some sort of temporary table created by that query. Do you have a lot of threads in your database? I think ORDER BY RAND isn't very efficient for large tables, and if there's no caching of the random threads then it's going to have to be done every time someone visits the page.

[NeverBored]

Thanks for the reply. There is only about 9000 threads currently (don't know if that would be considered a lot). It has good purpose to get random results every page load, but I understand it may not be efficient. From a performance stand point, would it make any difference if I limited it to threads within a small date range rather than picking from all the threads?

Interestingly the error emails seemed to have now changed over the past 24 hours to mostly "Got error -1 from storage engine"

[kh99]

Originally Posted by NeverBored

Thanks for the reply. There is only about 9000 threads currently (don't know if that would be considered a lot). It has good purpose to get random results every page load, but I understand it may not be efficient. From a performance stand point, would it make any difference if I limited it to threads within a small date range rather than picking from all the threads?

I don't know if that's "a lot" either. You could try adding a date range and see if the errors go away. To be honest my reply was just a guess so I'm not sure the error is related to the ORDER BY.