-1

I have a huge database (contains almost 200 000 rows in each table and many such tables). I have to perform querying on it. Data is stored in MySQL and I can't use any other database. In my query I have to perform UNION of tables. My query is something like this:

select * from table1 
UNION 
select * from table2
...
select * from tablen;

But when I am doing this, I am getting this error

Incorrect keyfile for the table '/tmp/#sql_d3d_0.MYI';Try to repair it'

I think it is happening because of joining too many tables with too much data. How can I optimize my query so that it won't create this problem and querying becomes faster?

James Z
  • 12,209
  • 10
  • 24
  • 44
sklearning
  • 223
  • 1
  • 4
  • 12
  • 2
    MySQL != Sql Server - please use care when choosing tags. – Mike Gardner Oct 30 '15 at 15:31
  • find relation between tables and do the joins – Subin Chalil Oct 30 '15 at 15:32
  • First advice, is try to avoid using `select *`. Using the column names (even if it's all of them) makes your code easier to understand and maintain, and will help prevent surprises should the table structure be altered. – Mike Gardner Oct 30 '15 at 15:36
  • Can you use `UNION ALL` instead of `UNION`? That way the server won't have to deduplicate your results, which is probably why it's trying to stuff all your data in a temporary table. If you're using MySQL server above v5.7.3, [`UNION ALL` definitely should be an improvement over `UNION` for temp table space](https://bugs.mysql.com/bug.php?id=50674). (Whether you can use `UNION ALL` depends on your actual data and how you're using these results, so only you can answer that...) – Matt Gibson Oct 30 '15 at 15:54
  • (Voting to re-open, as in this case I think there's something that *can* be done for this specific query pattern to avoid using temp space. All the answers to the duplicate seem to just involve making the temp space bigger, not making the query results less temp-hungry...) – Matt Gibson Oct 30 '15 at 15:57

1 Answers1

-1

When you make request with joins, MySQL makes a temporary table to answer your request. The error you are getting means your request is very hard on the server.

Perhaps enlarge your memory,

  • wait_timeout = 600
  • max_allowed_packet = 64M

or simplify your request.

Mogsdad
  • 44,709
  • 21
  • 151
  • 275
Thierry
  • 89
  • 1
  • 5