0

I have a huge table containing 14 million + rows. All varchar data. I am trying to run this query. I get output quickly. limit is set on 800K records only.

SELECT *
FROM (SELECT unique_user_id, url, count(url) as url_visit_count, categories, count(categories) as count 
FROM ph_usertrack_data GROUP BY unique_user_id LIMIT 800000) a 
ORDER BY a.url_visit_count DESC LIMIT 100;

when I put limit to 1 million records, I get this error

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

My temp folder is having a lot of space around 100 GB, so no space issue. Why this issue appears when I increase number of rows to process?

Shades88
  • 7,934
  • 22
  • 88
  • 130

1 Answers1

1

I think this should help-

You can use df -h to check whether \tmp is in its own partition, and how much space is allocated to it.

If it is in its own partition and short of space, you can either:

(a) modify /tmp so that its parition has more space (either by reallocating or moving it to the main partition - e.g. see here) (b) changing MySql config so that it uses a different temp folder on a different partition, e.g. /var/tmp

Refer here and this too.

Community
  • 1
  • 1
Jacob
  • 2,041
  • 14
  • 16