cce1911 cce1911 - 4 years ago 167
MySQL Question

MySQL query performance improvment?

I've got a query that runs painfully slow and need some advice on how to improve its performance.

First table: results has approximately 10k rows.

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


Indexes

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


Second table: wp_usermeta has approximately 10k rows.

'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


Here is my query:

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


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.

Updated: EXPLAIN output and indexes added

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

Mit Mit
Answer Source

Since you already have a join to wp_usermeta, you need to join with linker_results to improve performance

    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;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download