Ok, this one's bugging me... The average query on my little MySQL server box (for DKP) takes miliseconds to complete.

The following query takes 18 seconds!


SELECT e.event_id, r.raid_name, count(ra.raid_id)


AS raid_count

FROM eqdkp_events e, eqdkp_raid_attendees ra, eqdkp_raids r

WHERE (e.event_name = r.raid_name)
AND (r.raid_id = ra.raid_id)
AND (ra.member_name = 'MEMBER NAME')
AND (r.raid_date >= MEMBER FIRST RAIDED DATE)

GROUP BY ra.member_name, r.raid_name";

Anyone see an optimization strategy?

Disclaimer: I am not an SQL guru.

Possibly a non-on-crack idea:
Try swapping:
AND (r.raid_id = ra.raid_id)
AND (r.raid_date >= MEMBER FIRST RAIDED DATE)
these two lines. You have already selected your "ra" fully -- you should restrict yourself to valid raids that match the raid attendence before examining the inner parts of the raid structure.

(this is based off the assumption that your SQL evaluates boolean conditions in reverse order, which seems to be SOP for SQL evaluators.)

What follows is mostly rambling and me learning about SQL:

SELECT e.event_id, r.raid_name, count(ra.raid_id)
AS raid_count
FROM eqdkp_events e, eqdkp_raid_attendees ra, eqdkp_raids r
WHERE (e.event_name = r.raid_name)
AND (r.raid_id = ra.raid_id)
AND (ra.member_name = 'MEMBER NAME')
AND (r.raid_date >= MEMBER FIRST RAIDED DATE)


Are the string comparisons fast? I would naively guess a simple way to speed up such a system would be to have a table of strings referred to by index...

Then you'd have something like

SELECT e.event_id, r.raid_name, count(ra.raid_id)
AS raid_count
FROM eqdkp_member_table m, eqdkp_events e, eqdkp_raid_attendees ra, eqdkp_raids r
WHERE
AND (e.event_name_index = r.raid_name_index)
AND (r.raid_id = ra.raid_id)
AND (r.raid_date >= MEMBER FIRST RAIDED DATE)
AND (ra.member_name_index = m.member_name_index)
AND (m.character_name = 'MEMBER NAME')


Hmm. Indexes probably fix this problem, and/or fake it. What kind of indexes do you have?

Random: if you cut out the "raid date" optimization, does it run slower or faster?

They're indexed. The events table is indexed by ID in-table.

What the MEMBER FIRST RAIDED DATE does is only calculated attendance percentage for an individual raid since you joined. That is, if you attended all Epic 1.5 fights since you joined, it'd show 100% - instead of 20% of all fights.

Removing AND (r.raid_date >= MEMBER FIRST RAIDED DATE) still creates an long-running query.

Not a guru here either, but I would try adding e.event_id to the group by clause or removing it from the select altogether.

Is there a MySQL equivalent to "explain plan" or "display estimated execution plan", so we can see what MySQL is doing with the query?

It's the string comparison on name that's slowing it down. If you can use a userid instead, it would be much faster. Else, make sure there's an index on the name column in both tables.

I'm a programmer not a DBA but I'll take a shot anyway. I'm looking at you joining 3 tables to get your desired data but all of your criteria seems to be in the where clause. I'm wondering if you're taking 18 seconds to do a full inner join of all 3 tables and then look for matching joined rows out of that mega sized row set. Give this a try:

SELECT e.event_id, r.raid_name, count(ra.raid_id) AS raid_count
FROM eqdkp_events e
INNER JOIN eqdkp_raids r
ON e.event_name = r.raid_name
INNER JOIN eqdkp_raid_attendees ra
ON r.raid_id = ra.raid_id
WHERE (ra.member_name = 'MEMBER NAME')
AND (r.raid_date >= MEMBER FIRST RAIDED DATE)
GROUP BY ra.member_name, r.raid_name

I've seen examples where I work where doing it this way it a lot faster. A lot of it may depend on how the Query Optimizer works.

Still waiting for your next Poker update too. :)

I looked at it again and since I know nothing about what your query optimizer may or may not do I'll throw this out there as well:

SELECT e.event_id, r.raid_name, count(ra.raid_id) AS raid_count
FROM eqdkp_raids r
INNER JOIN eqdkp_raid_attendees ra
ON ra.raid_id = r.raid_id
AND (ra.member_name = 'MEMBER NAME')
INNER JOIN eqdkp_events e
ON e.event_name = r.raid_name
WHERE (r.raid_date >= MEMBER FIRST RAIDED DATE)
GROUP BY ra.member_name, r.raid_name