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.
