CODEWITHSUNDEEP
mysqlquery-optimization
Steve
Steve

Reputation: 3095

MySQL - Why is this slow running query not using (what seems to be) the correct index?

This query works, and does what I want it to do, but is awfully slow running. We have a table of transactions that holds an amount, a user ID, a partner ID, and a description. When a user has 3 of a specific transaction they get a one-time bonus. 

SELECT t.str_spid, count(*) AS C
  FROM transactions t
  LEFT JOIN transactions AS x
    ON t.str_spid = x.str_spid
   AND x.str_prid = 148
   AND x.str_amount = 2500
 WHERE t.str_prid = 148
   AND (t.str_desc = "Annual Rewards" OR t.str_desc = "Annual Rewards (PRO)")
   AND t.str_amount = 2000
   AND x.str_spid IS NULL
 GROUP BY t.STR_SPID 
HAVING C = 3
 LIMIT 0,100;

I have an indexes on the individual fields, and then composite indexes as follows:

SPID_DE_PRID: STR_SPID, STR_DESC, STR_PRID
SPID_DE_PRID_AMT: STR_SPID, STR_AMOUNT, STR_DESC, STR_PRID

When I use explain I get possible keys (on t):

STR_SPID,STR_PRID,STRAMT,STRDESC,SPID_PRID,SPID_DE_PRID,SPID_DE_PRID_AMT

And key used: STR_PRID

On x I get possible:

STR_SPID,SPT_SOID,STRAMT,SPID_PRID,SPID_DE_PRID,SPID_DE_PRID_AMT

And key used: STR_SPID

Is there a better index or way to rewrite this so that it runs any faster?

Upvotes: 2

Views: 37

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562891

You search t for specific values in str_prid and str_desc, so those should be the first two columns of an index, but you don't have such an index.

The optimizer does its best with the single-column index on str_prid, to narrow down the search.

If you had an index on (str_prid, str_amount, str_desc, str_spid) that's what I would expect would have the best optimization for t.

Then for x, you'd want to look up matching rows, so you'd want and index on (str_spid, str_prid, str_amount).

Upvotes: 1

Related Questions