title: MySQL query performance improvment?
body: <p>I've got a query that runs painfully slow and need some advice on how to improve its performance. </p>
  
  <p>First table: results has approximately 10k rows.</p>
  
  <pre><code>'CREATE TABLE `results` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `matchID` int(11) NOT NULL,
  `matchShooterID` int(11) NOT NULL DEFAULT ''0'',
  `ResultDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  ... more columns ...
  PRIMARY KEY (`ID`)
  ) ENGINE=InnoDB AUTO_INCREMENT=9767 DEFAULT CHARSET=latin1'
  </code></pre>
  
  <p>Indexes</p>
  
  <pre><code>Table   Non_unique  Key_name    Seq_in_index    Column_name Collation   Cardinality Sub_part    Packed  Null    Index_type  Comment Index_comment   
  'results', '0', 'PRIMARY', '1', 'ID', 'A', '3462', NULL, NULL, '', 'BTREE', '', ''
  </code></pre>
  
  <p>Second table: wp_usermeta has approximately 10k rows.</p>
  
  <pre><code>'CREATE TABLE `wp_usermeta` (
    `umeta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `user_id` bigint(20) unsigned NOT NULL DEFAULT ''0'',
    `meta_key` varchar(255) DEFAULT NULL,
    `meta_value` longtext,
    PRIMARY KEY (`umeta_id`),
    KEY `user_id` (`user_id`),
    KEY `meta_key` (`meta_key`(191))
  ) ENGINE=InnoDB AUTO_INCREMENT=11170 DEFAULT CHARSET=utf8'; 
  
  Table   Non_unique  Key_name    Seq_in_index    Column_name Collation   Cardinality Sub_part    Packed  Null    Index_type  Comment Index_comment
  wp_usermeta 0   PRIMARY 1   umeta_id    A   6241                BTREE       
  wp_usermeta 1   user_id 1   user_id A   780             BTREE       
  wp_usermeta 1   meta_key    1   meta_key    A   104 191     YES BTREE       
  </code></pre>
  
  <p>Here is my query:</p>
  
  <pre><code>SELECT DISTINCT
          pr.ShooterID AS ShooterID,
          pr.RangeID AS RangeID,
          (SELECT um.meta_value
              FROM
                  wp_usermeta um
              WHERE
                  ((um.meta_key = 'first_name')
                      AND (um.user_id = pr.ShooterID))),
          (SELECT 
                  COUNT(0)
              FROM
                  wp_plinker_results
              WHERE
                  ((results.ShooterID = pr.ShooterID)
                      AND (results.RangeID = pr.RangeID))) AS GameCount
  FROM
      (results pr
      JOIN wp_usermeta um ON ((pr.ShooterID = um.user_id)));
  </code></pre>
  
  <p>What I'm trying to get is a count of the games (a result record) for each shooterID by date. Any suggestions would be greatly appreciated.</p>
  
  <p>Updated: EXPLAIN output and indexes added</p>
  
  <pre><code>1   PRIMARY pr  ALL                 3462    100.00  Using temporary
  1   PRIMARY um  ref user_id user_id 8   shootpli_plpress.pr.ShooterID   8   100.00  Using where; Using index; Distinct
  3   DEPENDENT SUBQUERY  wp_plinker_results  ALL                 3462    100.00  Using where
  2   DEPENDENT SUBQUERY  um  ref user_id,meta_key    user_id 8   shootpli_plpress.pr.ShooterID   8   100.00  Using index condition; Using where
  </code></pre>