title: MySQL query with JOIN and GROUP BY optimization. Is it possible?
body: <p>I have two tables: gpnxuser and key_value</p>
  
  <pre><code>mysql&gt; describe gpnxuser;
  +--------------+--------------+------+-----+---------+----------------+
  | Field        | Type         | Null | Key | Default | Extra          |
  +--------------+--------------+------+-----+---------+----------------+
  | id           | bigint(20)   | NO   | PRI | NULL    | auto_increment |
  | version      | bigint(20)   | NO   |     | NULL    |                |
  | email        | varchar(255) | YES  |     | NULL    |                |
  | uuid         | varchar(255) | NO   | MUL | NULL    |                |
  | partner_id   | bigint(20)   | NO   | MUL | NULL    |                |
  | password     | varchar(255) | YES  |     | NULL    |                |
  | date_created | datetime     | YES  |     | NULL    |                |
  | last_updated | datetime     | YES  |     | NULL    |                |
  +--------------+--------------+------+-----+---------+----------------+
  </code></pre>
  
  <p>and</p>
  
  <pre><code>mysql&gt; describe key_value;
  +----------------+--------------+------+-----+---------+----------------+
  | Field          | Type         | Null | Key | Default | Extra          |
  +----------------+--------------+------+-----+---------+----------------+
  | id             | bigint(20)   | NO   | PRI | NULL    | auto_increment |
  | version        | bigint(20)   | NO   |     | NULL    |                |
  | date_created   | datetime     | YES  |     | NULL    |                |
  | last_updated   | datetime     | YES  |     | NULL    |                |
  | upkey          | varchar(255) | NO   | MUL | NULL    |                |
  | user_id        | bigint(20)   | YES  | MUL | NULL    |                |
  | security_level | int(11)      | NO   |     | NULL    |                |
  +----------------+--------------+------+-----+---------+----------------+
  </code></pre>
  
  <p>key_value.user_id is FK that references gpnxuser.id. I also have an index in gpnxuser.partner_id which is a FK that references a table called "partner" (which, I think, does not matter much to this question).</p>
  
  <p>For partner_id = 64, I have 500K rows in gpnxuser which have relationship with approximatelly 6M rows in key_value.</p>
  
  <p>I wanted to have a query that returned all distinct 'key_value.upkey' for user´s belonging to a given partner. I did something like this:</p>
  
  <pre><code>select upkey from gpnxuser join key_value on gpnxuser.id=key_value.user_id where partner_id=64 group by upkey;
  </code></pre>
  
  <p>which takes forever to run. The explain for the query looks like:</p>
  
  <pre><code>mysql&gt; explain select upkey from gpnxuser join key_value on gpnxuser.id=key_value.user_id where partner_id=64 group by upkey;
  
      +----+-------------+-----------+------+----------------------------+--------------------+---------+-----------------------------+--------+----------------------------------------------+
      | id | select_type | table     | type | possible_keys              | key                | key_len | ref                         | rows   | Extra                                        |
      +----+-------------+-----------+------+----------------------------+--------------------+---------+-----------------------------+--------+----------------------------------------------+
      |  1 | SIMPLE      | gpnxuser  | ref  | PRIMARY,FKB2D9FEBE725C505E | FKB2D9FEBE725C505E | 8       | const                       | 259640 | Using index; Using temporary; Using filesort |
      |  1 | SIMPLE      | key_value | ref  | FK9E0C0F912D11F5A9         | FK9E0C0F912D11F5A9 | 9       | gpnx_finance_db.gpnxuser.id |     14 | Using where                                  |
      +----+-------------+-----------+------+----------------------------+--------------------+---------+-----------------------------+--------+----------------------------------------------+
  </code></pre>
  
  <p>My question is: is there a query that can run fast and obtain the result that I want?</p>