MySQL Query Optimisation

[MrCue]

SELECT DISTINCT pk_ID
FROM v9_eve_pk_mails, v9_eve_pk_mails_involved
WHERE pki_mail_ID = pk_ID
AND (
pk_victim = 'TuRtLe HeAd'
OR pki_name = 'TuRtLe HeAd'
)
ORDER BY pk_date DESC
LIMIT 10

Is there a way to make this query faster? it currently takes anywhere from 20 - 500 seconds depending on the number of results

[kegobeer]

Generally speaking, you should get an increase in speed if your join columns are both indexed.  If you clarify what tables your fields are coming from (pk_ID, pki_mail_ID, pk_victim and pki_name), there is probably a way to get rid of the distinct clause by rewriting the query, which should give you a speed increase.

[MrCue]

pk_ID, pk_victim come from v9_eve_pk_mails
pki_mail_ID, pki_name come from v9_eve_pk_mails_involved

pk_ID is a primary key, all the others have indexes

An explain shows
v9_eve_pk_mails     ALL     PRIMARY,pk_victim     NULL     NULL     NULL     260319     Using temporary; Using filesort
v9_eve_pk_mails_involved    ref    pki_name,pki_mail_ID    pki_mail_ID    8    v9_eve_pk_mails.pk_ID    2    Using where; Distinct

[[Unknown]]

First off, ALL is bad.  It isn't using any keys...

You can try this:

SELECT DISTINCT mails.pk_ID
FROM v9_eve_pk_mails AS mails USE INDEX (PRIMARY), v9_eve_pk_mails_involved AS inv
WHERE inv.pki_mail_ID = mails.pk_ID
   AND (mails.pk_victim = 'TuRtLe HeAd' OR inv.pki_name = 'TuRtLe HeAd')
ORDER BY mails.pk_date DESC
LIMIT 10;

How fast is that and what is the explain?

-[Unknown]

[MrCue]

mails     ALL     PRIMARY     NULL     NULL     NULL     261375     Using temporary; Using filesort
inv    ref    pki_name,pki_mail_ID    pki_mail_ID    8    mails.pk_ID    2    Using where; Distinct

46.4207 seconds

[MrCue]

I got it down to ~ 0.6 seconds now, by limiting it to only look at the rows where the ID was in an array.

Thanks for looking.