Why doesn't mysql use my index?

I have two tables:

mysql> desc myps3t_gameusertrophyinfo;

+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| id        | int(11)      | NO   | PRI | NULL    | auto_increment |
| user_id   | int(11)      | NO   | MUL | NULL    |                |
| trophy_id | int(11)      | NO   | MUL | NULL    |                |
| date      | datetime     | NO   | MUL | NULL    |                |
| date_read | varchar(100) | NO   |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+
5 rows in set (0.19 sec)


mysql> show index from myps3t_gameusertrophyinfo;

+---------------------------+------------+------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table                     | Non_unique | Key_name                           | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------开发者_StackOverflow-------------------+------------+------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| myps3t_gameusertrophyinfo |          0 | PRIMARY                            |            1 | id          | A         |     4004589 |     NULL | NULL   |      | BTREE      |         |
| myps3t_gameusertrophyinfo |          0 | user_id                            |            1 | user_id     | A         |        7686 |     NULL | NULL   |      | BTREE      |         |
| myps3t_gameusertrophyinfo |          0 | user_id                            |            2 | trophy_id   | A         |     4004589 |     NULL | NULL   |      | BTREE      |         |
| myps3t_gameusertrophyinfo |          1 | myps3t_gameusertrophyinfo_403f60f  |            1 | user_id     | A         |        7686 |     NULL | NULL   |      | BTREE      |         |
| myps3t_gameusertrophyinfo |          1 | myps3t_gameusertrophyinfo_61a683d8 |            1 | trophy_id   | A         |       22624 |     NULL | NULL   |      | BTREE      |         |
| myps3t_gameusertrophyinfo |          1 | idx_gameusertrophyinfo_date        |            1 | date        | A         |     4004589 |     NULL | NULL   |      | BTREE      |         |
+---------------------------+------------+------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
7 rows in set (0.06 sec)

the other table:

mysql> desc myps3t_gametrophyinfo ;
+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| id        | int(11)      | NO   | PRI | NULL    | auto_increment |
| game_id   | int(11)      | NO   | MUL | NULL    |                |
| name      | varchar(500) | NO   |     | NULL    |                |
| desc      | varchar(500) | NO   |     | NULL    |                |
| type      | varchar(20)  | NO   |     | NULL    |                |
| pic_url   | varchar(200) | NO   |     | NULL    |                |
| desc_pt   | varchar(500) | NO   |     | NULL    |                |
| name_pt   | varchar(500) | NO   |     | NULL    |                |
| hidden_id | int(11)      | NO   |     | NULL    |                |
| total_id  | int(11)      | NO   |     | NULL    |                |
| trophy_id | int(11)      | NO   |     | NULL    |                |
| addon_id  | int(11)      | YES  |     | NULL    |                |
| points    | double       | NO   |     | 0       |                |
| sony_id   | int(11)      | YES  |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+
14 rows in set (0.00 sec)

mysql> show index from myps3t_gametrophyinfo;
+-----------------------+------------+-------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table                 | Non_unique | Key_name                      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------------------+------------+-------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| myps3t_gametrophyinfo |          0 | PRIMARY                       |            1 | id          | A         |       25976 |     NULL | NULL   |      | BTREE      |         |
| myps3t_gametrophyinfo |          1 | myps3t_gametrophyinfo_game_id |            1 | game_id     | A         |         764 |     NULL | NULL   |      | BTREE      |         |
+-----------------------+------------+-------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

when i do this query:

mysql> explain select * from myps3t_gameusertrophyinfo a, myps3t_gametrophyinfo b  where a.trophy_id = b.id and b.addon_id = 58;                                        +----+-------------+-------+--------+--------------------------------------------------------------+---------+---------+-----------------------------+---------+-------------+
| id | select_type | table | type   | possible_keys                                                | key     | key_len | ref                         | rows    | Extra       |
+----+-------------+-------+--------+--------------------------------------------------------------+---------+---------+-----------------------------+---------+-------------+
|  1 | SIMPLE      | a     | ALL    | myps3t_gameusertrophyinfo_61a683d8,myps3t_gameusertrophyinfo | NULL    | NULL    | NULL                        | 4004592 |             |
|  1 | SIMPLE      | b     | eq_ref | PRIMARY                                                      | PRIMARY | 4       | fabriciols_ps3t.a.trophy_id |       1 | Using where |
+----+-------------+-------+--------+--------------------------------------------------------------+---------+---------+-----------------------------+---------+-------------+
2 rows in set (0.00 sec)

why mysql do not use my keys ? this query take more than 30 seconds, the first table has 4milion records ...

-- edit --

for quasnoi

mysql> SELECT COUNT(*), COUNT(DISTINCT addon_id), SUM(addon_id = 58) FROM myps3t_gametrophyinfo;
+----------+--------------------------+--------------------+
| COUNT(*) | COUNT(DISTINCT addon_id) | SUM(addon_id = 58) |
+----------+--------------------------+--------------------+
|    25976 |                      160 |                  6 |
+----------+--------------------------+--------------------+
1 row in set (0.00 sec)

---

MySQL chooses a as a leading table and b as a driven table. It does use a PRIMARY KEY on b for the joins.

Create an index on myps3t_gametrophyinfo (addon_id), this way b will be more probably chosen as a leading table.

---

You can try

select * from 
myps3t_gametrophyinfo b 
STRAIGHT_JOIN myps3t_gameusertrophyinfo a ON (a.trophy_id = b.id) 
WHERE b.addon_id = 58;

---

I would probably rewrite the query to try to get a more sane execution path. I think something like below is more likely to get you the performance you want and is more clear in what you are doing to a human reader

SELECT * FROM myps3t_gametrophyinfo a LEFT JOIN myps3t_gameusertrophyinfo b ON a.id = b.trophy_id WHERE a.addon_id=58;