MySQL查询性能提升？

Question

我有一个查询运行得非常慢，需要一些关于如何提高其性能的建议。

第一个表:results 大约有 10k 行。

'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'

索引

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', '', ''

第二个表:wp_usermeta 大约有 10k 行。

'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       

这是我的查询:

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)));

我想要获取的是每个 shooterID 按日期计算的游戏计数(结果记录)。任何建议将不胜感激。

更新:添加了 EXPLAIN 输出和索引

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

Answer 1

因为你已经加入了 wp_usermeta，所以你需要加入 linker_results 来提高性能

    SELECT pr.ShooterID AS ShooterID,
            pr.RangeID AS RangeID,
            CASE um.meta_key WHEN um.meta_key ='first_name' THEN um.meta_value ELSE 'NO_META_VALUE' END as meta_value,
            COUNT(0) AS GameCount
    FROM
        results pr
        JOIN wp_usermeta um ON ((pr.ShooterID = um.user_id))
        JOIN wp_plinker_results um ON ((results.ShooterID = pr.ShooterID)
                    AND (results.RangeID = pr.RangeID))
    GROUP BY pr.ShooterID,pr.RangeID,um.meta_value;