# Mysql "Copying to tmp table"

**URL:** <https://forum.kirupa.com/t/mysql-copying-to-tmp-table/196254>\
**Category:** Uncategorized\
**Created:** [August 7, 2006, 5:55am UTC](https://forum.kirupa.com/t/mysql-copying-to-tmp-table/196254 "2006-08-07T05:55:02Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![StarXploser](https://yyz1.discourse-cdn.com/flex011/user_avatar/forum.kirupa.com/starxploser/32/875_2.png) [@StarXploser](https://forum.kirupa.com/u/StarXploser)\
**Post date:** [August 7, 2006, 5:55am UTC](https://forum.kirupa.com/t/mysql-copying-to-tmp-table/196254/1 "2006-08-07T05:55:02Z")

</div>

Show MySQL Processes \>\>

## Copying to tmp table | SELECT users.id FROM users LEFT JOIN friends ON users.id = friends.id2 AND friends.id1=95292481 WHE…

## –

more queries that are unable to run until “copying to tmp” is done.\

here is the query

SELECT users.id FROM users LEFT JOIN friends ON users.id = friends.id2 AND friends.id1=$id WHERE friends.id2 IS NULL AND users.id != $id ORDER BY users.packageclass DESC, users.dailyPoints DESC LIMIT $n;

and its “EXPAIN”

mysql\> EXPLAIN SELECT users.id FROM users LEFT JOIN friends ON users.id = friends.id2 AND friends.id1=777 WHERE friends.id2 IS NULL AND users.id != 777 OR  
DER BY users.packageclass DESC, users.dailyPoints DESC LIMIT 500 \g  
±—±------------±--------±-----±--------------±-----±--------±------±------±----------------------------+  
| id | select\_type | table | type | possible\_keys | key | key\_len | ref | rows | Extra |  
±—±------------±--------±-----±--------------±-----±--------±------±------±----------------------------+  
| 1 | SIMPLE | users | ALL | id | NULL | NULL | NULL | 15134 | Using where; Using filesort |  
| 1 | SIMPLE | friends | ref | id1,id2 | id1 | 4 | const | 1 | Using where; Not exists |  
±—±------------±--------±-----±--------------±-----±--------±------±------±----------------------------+  
2 rows in set (0.00 sec)

here is the INDEX stuff:

mysql\> show index from users;  
±------±-----------±-------------±-------------±-------------±----------±------------±---------±-------±-----±-----------±--------+  
| Table | Non\_unique | Key\_name | Seq\_in\_index | Column\_name | Collation | Cardinality | Sub\_part | Packed | Null | Index\_type | Comment |  
±------±-----------±-------------±-------------±-------------±----------±------------±---------±-------±-----±-----------±--------+  
| users | 1 | id | 1 | id | A | 15134 | NULL | NULL | | BTREE | |  
| users | 1 | dailypoints | 1 | dailypoints | A | 35 | NULL | NULL | | BTREE | |  
| users | 1 | packageclass | 1 | packageclass | A | 3 | NULL | NULL | | BTREE | |  
±------±-----------±-------------±-------------±-------------±----------±------------±---------±-------±-----±-----------±--------+

mysql\> show index from friends;  
±--------±-----------±---------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------+  
| Table | Non\_unique | Key\_name | Seq\_in\_index | Column\_name | Collation | Cardinality | Sub\_part | Packed | Null | Index\_type | Comment |  
±--------±-----------±---------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------+  
| friends | 1 | id1 | 1 | id1 | A | 3626 | NULL | NULL | | BTREE | |  
| friends | 1 | id2 | 1 | id2 | A | 4058 | NULL | NULL | | BTREE | |  
±--------±-----------±---------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------+

I dont want any NULL Value in my EXPLAIN output.  
I’m guessing I need to tweak the query or the INDEX

I’ve been GOOGLE-ing all day long on how to do proper JOIN queries and set up the INDEX stuff but I don’t think I got it.  
Can someone help me out or direct me somewhere.

Thanks, ANY help would be appreciated, I’m out of ideas.