MySQL Count subquery

Question

I have a database with 3 tables:

• equities
• stocksplits
• dividends

There is a one to many relationship between equities and stocksplits, and between equities and dividends. For each equity I would like to show the number of stocksplits and dividends:

SELECT equities.Symbol, 
       (SELECT COUNT(*) 
          FROM stocksplits 
         WHERE stocksplits.EquityID = equities.InstrumentID) as `# Splits`,
       (SELECT COUNT(*) 
          FROM dividends 
         WHERE dividends.EquityID = equities.InstrumentID) as `# Dividends`
FROM equities

The query appears to run fine, though I suspect it is inefficient. How can it be refactored to be faster? No DBMS (SQL query via .net to MySQL server), assume indices exist on the primary ID of each table.

Solution 1

Counting the PKs instead of * might already help:

SELECT equities.Symbol, 
           (SELECT COUNT(stocksplitsID) 
              FROM stocksplits 
             WHERE stocksplits.EquityID =     equity.InstrumentID) as `# Splits`,
           (SELECT COUNT(dividendsid) 
              FROM dividends 
             WHERE dividends.EquityID = equities.InstrumentID) as `# Dividends`
FROM equities

Solution 2

Here is your original query

SELECT equities.Symbol, 
       (SELECT COUNT(*) 
          FROM stocksplits 
         WHERE stocksplits.EquityID = equities.InstrumentID) as `# Splits`
FROM equities

I was just thinking that an LEFT JOIN would be cleaner

SELECT equities.Symbol,
    SUM(IF(IFNULL(stocksplits.EquityID,0)=0,0,1)) StockSplits,
    SUM(IF(IFNULL(dividends.EquityID  ,0)=0,0,1)) Dividends
FROM
    equities
    LEFT JOIN stocksplits ON equities.InstrumentID = stocksplits.EquityID
    LEFT JOIN dividends   ON equities.InstrumentID = dividends.EquityID
GROUP BY equities.Symbol;

The IFNULL covers any stock that had no stock splits

Give it a Try and see it it runs faster

Let me explain the expression SUM(IF(IFNULL(stocksplits.EquityID,0)=0,0,1))

• IFNULL will turn a NULL into a 0 if the LEFT JOIN does not have a corresponding entry on the right-side table.
• if LEFT JOIN had a right-side entry, IF function returns 1
• if LEFT JOIN has no right-side entry, IF function return 0
• SUM will add up all the ones and zeros, simulating a COUNT