CODEWITHSUNDEEP
mysqlphpmyadmin
Rim
Rim

Reputation: 13

Query stuck copying in tmp table for a long time

Thanks in advance for taking the time to read my question. I'm using MySQL and Python together and I'm executing first this SQL command

  SELECT T1.col1 AS t, T1.col4 AS d, T2.col3 AS e, T2.col4 AS d, T2.col7 AS p,
  T2.col13 AS de, 
  T2.col5 AS carrier, T2.col12 AS f, T2.col10 AS c
  FROM table AS T1
  JOIN table_c AS T2 ON T1.col1 = T2.col1
  ORDER BY RAND( )
  LIMIT 100000 ;

The databases I'm working on are quite big (T1 is of 300 000 000 lines and T2 is of 300 000 lines) and I created them thanks to phpMyAdmin . I created indexes also on the col1 of T1 and col1 of T2. But when I run the SQL command it gets stuck on the copying to tmp table phase message ( I left it for the whole weekend and it's still at the same point)

I tried modifying the parameter tmp_table_size but it didn't change anything

I have no idea what could be the solution for me to execute this command in a reasonable time

All suggestions are welcome

Thank you so much for your help

Upvotes: 0

Views: 3377

Answers (2)

biziclop
biziclop

Reputation: 14616

The ORDER BY RAND() trick should never be used with such large table sizes. ORDER BY RAND() works by assigning a random value for each row in your JOINed result set (in your case probably hundreds of millions of rows), actually creating all those rows, sorting them on disk, and if it's ready, only then can it choose and return the top 100000 rows for you.

Solutions are not obvious, but you can find some techniques to fight with your problem, like How can i optimize MySQL's ORDER BY RAND() function?

Upvotes: 1

poncha
poncha

Reputation: 7866

  1. Use EXPLAIN EXTENDED SELECT ... to see what the query involves (how many estimated rows to work on, which indexes are in use, etc).

  2. EXPLAIN query will also show "1 warning(s)", see SHOW WARNINGS - that will show you how exactly your query looks like after query optimizer.

  3. Post your tables structure (SHOW CREATE TABLE ...), table status for both tables, and relevant configuration (all configuration entries matching max*, *buffer*, *size*).

  4. Post your hardware configuration (cpu - speed?, cores?, ram - total? free?)

Upvotes: 0

Related Questions