MySql - Abysmal Performance

I am trying to run a relatively simple query on a table that has half a million rows. It's just a small fragment I'm using to test the values I get back are correct. The problem is this query takes over 20 minutes to complete, which seems unusually slow even for 500,000 records.

DROP VIEW IF EXISTS view_temp_sortie_stats;
CREATE VIEW view_temp_sortie_stats AS
SELECT server_id, session_id, ucid, role, sortie_id,
(
SELECT COUNT(sortie_id)
FROM raw_gameevents_log
WHERE sortie_id = l.sortie_id AND server_id = l.server_id AND session_id = l.session_id AND target_player_ucid = l.ucid AND event = "HIT"
) AS HitsReceived
FROM raw_gameevents_log l
WHERE ucid IS NOT NULL AND sortie_id IS NOT NULL
GROUP BY server_id, session_id, ucid, role, sortie_id;

SELECT * FROM view_temp_sortie_stats;


Here is my table:

Next I tried to add indexes for server_id, session_id, sortie_id to see if it would improve - this took more than 10 minutes to apply and timed out. So I could not add them.

This seems abnormally slow, it shouldn't take this much time to add indexes, or perform this query.

My innodb_buffer_pool_size is 5GB, yet the mysqld process only consumes 300mb of memory when these queries are run.

I am running on Windows Server 2012 R2 Standard with 12 GB Ram, 2x Intel Haswell CPU, so I should be seeing much better performance than this from mysql.

There is no one else connected to this instance of MySql and no other operations are occurring.

EDIT - Here is the query explained

Does someone know what might be wrong?

EDIT2 - Partial Fix

After some googling I found out why the add index was taking forever - the original query was still running in the background for over 2hrs. Once I Killed the query the add index took about 30 seconds.

Now when I run the above query it takes 27 seconds - which is a drastic improvement for sure, but that still seems pretty slow for 500,000 records. Here is the new query explain plan:

EXPLAIN SELECT server_id, session_id, ucid, role, sortie_id (...etc...) GROUP BY server_id, session_id, ucid, role, sortie_id;

GROUP BY

1 Answer
1

Your subquery is:

SELECT COUNT(sortie_id)
FROM raw_gameevents_log
WHERE sortie_id = l.sortie_id AND server_id = l.server_id
AND session_id = l.session_id AND target_player_ucid = l.ucid
AND event = "HIT"


and the main query is:

SELECT server_id, session_id, ucid, role, sortie_id, [...]
FROM raw_gameevents_log l
WHERE ucid IS NOT NULL AND sortie_id IS NOT NULL
GROUP BY server_id, session_id, ucid, role, sortie_id;


Let's start from the subquery. The COUNT can count on whatever, so we don't bother with the select fields. The WHERE fields:

WHERE sortie_id = l.sortie_id AND server_id = l.server_id
AND session_id = l.session_id AND target_player_ucid = l.ucid
AND event = "HIT"


You create an index beginning with the constant fields, then the others:

CREATE INDEX subqindex ON raw_gameevents_log(
event,
sortie_id, server_id, session_id, target_player_ucid
)


Then the main query:

WHERE ucid IS NOT NULL AND sortie_id IS NOT NULL
GROUP BY server_id, session_id, ucid, role, sortie_id;


Here you need an index on

ucid, sortie_id, server_id, session_id, role


Finally, you might try getting rid of the subquery (even if the optimizer probably already does a good work with that):

SELECT server_id, session_id, ucid, role, sortie_id,
COALESCE(hits, 0) AS hits
FROM raw_gameevents_log l
LEFT JOIN
(
SELECT COUNT(1) AS hits FROM raw_gameevents_log
WHERE event = 'HIT'
) AS h
ON (h.sortie_id = l.sortie_id, h.server_id = l.server_id, h.session_id = l.session_id, h.target_player_ucid = l.ucid)
WHERE l.ucid IS NOT NULL AND l.sortie_id IS NOT NULL
GROUP BY l.server_id, l.session_id, l.ucid, l.role, l.sortie_id;


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.