CODEWITHSUNDEEP
mysqlsql
DDT
DDT

Reputation: 49

Optimizing GROUP BY on JOIN

SELECT 
 h1.Date AS Date,
 h1.Currency AS Currency1,
 h2.Currency AS Currency2,
 h1.Account AS Account,
 SUM(h1.Size) AS Size1,
 SUM(h2.Size) AS Size2,
 h1.MaturityDate AS MaturityDate
FROM
 htable h1 JOIN htable h2 ON 
 h1.AssetClass = 'FX' AND
 h2.AssetClass = 'FX' AND
 h1.Date = h2.Date AND
 (SUBSTR(h1.Ticker, 7, 3) <> h1.Currency) AND
 (SUBSTR(h2.Ticker, 7, 3) = h2.Currency) AND
 h1.SecID = h2.SecID
GROUP BY h1.Date, h1.Currency, h2.Currency, h1.Account, h1.MaturityDate
HAVING SUM(h1.Size) <> 0 AND SUM(h2.Size) <> 0)

The query above WITHOUT the GROUP BY clause is quick when queried for a specific Date.

The problem is when I add the GROUP BY, it becomes extremely slow, even if I add a specific Date to the HAVING clause.

I have already added indexes for htable for the columns Date, AssetClass, SecID, Currency, MaturityDate, and also a an index for the combination of (Date, Account, Currency, MaturityDate).

This is being done in MySQL. Do you have any tips on how to speed up the query (I plan on using the query for a view definition)? Thanks!

Upvotes: 0

Views: 50

Answers (1)

The Impaler
The Impaler

Reputation: 48769

The first index that come to mind I can see can improve the performance of your query is:

htable (AssetClass, Date, SecID) -- if Date is more selective than SecID

Or 

htable (AssetClass, SecID, Date) -- if SecId is more selective than Date

Now, if you want to go a step further, you can index a virtual column for SUBSTR(h2.Ticker, 7, 3), by:

alter table htable add ticker_currency varchar(3) 
  generated always as (SUBSTR(h2.Ticker, 7, 3)) virtual;

Then, add the index:

htable (AssetClass, SecID, Date, ticker_currency)

Upvotes: 1

Related Questions