Slow running query, is there a better way?

I've got a query that produces the right result, its just very slow. I feel like there must be a better way (perhaps without subqueries).

Table, result and query are below. I've anonymized the data and I have 8 subqueries rather than 2, but the format is the same.

Table "a":

开发者_开发问答id      userId       type     amount
------------------------------------
 1      1            a        400
 2      1            b        300
 3      1            c        230
 4      2            a        600
 5      2            b        500
 6      2            c        430

I've got an index on each column and one additional one that encompasses the userId and type columns. I can also guarantee you that userId and type are unique (i.e. there would't be two type 'a' for user 1).

Desired Result:

userId   typeAtotal  typeBtotal
--------------------------------
  1      400         300
  2      600         500

My Query:

SELECT userId, 
       (SELECT amount 
          FROM a AS a2 
         WHERE a2.userId = a1.userId 
           AND a2.type = 'a') AS aAmt,
       (SELECT amount 
          FROM a AS a3 
         WHERE a3.userId = a1.userId 
           AND a3.type = 'b') AS bAmt
    FROM a AS a1 
   WHERE type IN ('a','b') 
GROUP BY userId

Use:

  SELECT t.userid,
         MAX(CASE WHEN t.type = 'a' THEN amount ELSE NULL END) AS typeAtotal,
         MAX(CASE WHEN t.type = 'b' THEN amount ELSE NULL END) AS typeBtotal
    FROM YOUR_TABLE t
GROUP BY t.userid

If there can be more than one amount for either type - this will return the highest. If you want such situations added, use SUM:

  SELECT t.userid,
         SUM(CASE WHEN t.type = 'a' THEN amount ELSE NULL END) AS typeAtotal,
         SUM(CASE WHEN t.type = 'b' THEN amount ELSE NULL END) AS typeBtotal
    FROM YOUR_TABLE t
GROUP BY t.userid

Looks like cross-tabulation to me. You might try something like this:

SELECT userId, 
    SUM(IF(a.type = 'a'), a.amount, 0) AS aAmount, 
    SUM(IF(a.type = 'b'), a.amount, 0) AS bAmount
FROM a
WHERE type IN ('a', 'b')
GROUP BY a.userId

You might want to read this rather well-written tutorial: http://dev.mysql.com/tech-resources/articles/wizard/index.html

Edit: fixed the ELSE condition.