SELECT simple_mtg_cards.*,
       Ifnull(d.c, 0)          AS decks_count,
       Ifnull(traded.COUNT, 0) AS traded_count,
       Ifnull(i.c, 0)          AS inventory_count,
       Ifnull(i.tc, 0)         AS tradelist_count,
       Ifnull(w.c, 0)          AS wishlist_count
FROM   `simple_mtg_cards`
       LEFT JOIN (SELECT card_id,
                         SUM(COUNT) c
                  FROM   mtg_cards_sets cs
                         JOIN sets s
                           ON ( s.id = cs.set_id
                                AND s.user_id = 1
                                AND s.TYPE = 'Deck' )
                  GROUP  BY card_id) AS d
              ON ( d.card_id = simple_mtg_cards.id )
       LEFT JOIN (SELECT card_id,
                         SUM(mtcs.COUNT) AS `count`
                  FROM   mtg_trade_cards_sets AS mtcs
                         JOIN mtg_detailed_cards mdc
                           ON ( mtcs.details_id = mdc.id )
                         JOIN trades t
                           ON mtcs.trade_id = t.id
                              AND ( t.source_user_id = 1
                                     OR t.target_user_id = 1 )
                              AND t.source_state != 4
                              AND t.source_state != 3
                              AND t.target_state != 3
                  GROUP  BY card_id) AS traded
              ON traded.card_id = simple_mtg_cards.id
       LEFT JOIN (SELECT card_id,
                         SUM(COUNT)       c,
                         SUM(trade_count) tc
                  FROM   mtg_detailed_cards_sets mdcs
                         JOIN mtg_detailed_cards mdc
                           ON mdcs.details_id = mdc.id
                  WHERE  set_id = 1
                  GROUP  BY card_id) AS i
              ON ( i.card_id = simple_mtg_cards.id )
       LEFT JOIN (SELECT card_id,
                         SUM(COUNT) c
                  FROM   detailed_mtg_cards_sets
                  WHERE  set_id = 3
                  GROUP  BY card_id) AS w
              ON ( w.card_id = simple_mtg_cards.id )
       JOIN (SELECT id,
                    @i := @i + 1 AS o
             FROM   (SELECT mtg_cards.id,
                            Ifnull(i.c, 0)  AS inventory_count,
                            Ifnull(i.tc, 0) AS tradelist_count
                     FROM   `mtg_cards`
                            INNER JOIN `mtg_cards_editions`
                                    ON `mtg_cards_editions`.`id` =
                                       `mtg_cards`.`main_card_edition_id`
                            INNER JOIN `mtg_card_prices`
                                    ON `mtg_card_prices`.`id` =
                                       `mtg_cards_editions`.`mtg_card_price_id`
                            LEFT JOIN (SELECT card_id,
                                              SUM(COUNT)       c,
                                              SUM(trade_count) tc
                                       FROM   mtg_detailed_cards_sets mdcs
                                              JOIN mtg_detailed_cards mdc
                                                ON mdcs.details_id = mdc.id
                                       WHERE  set_id = 1
                                       GROUP  BY card_id) AS i
                                   ON ( i.card_id = mtg_cards.id )
                     WHERE  ( (SELECT COUNT(*)
                               FROM   mtg_cards_editions
                               WHERE  mtg_cards_editions.edition_id IN
                                      ( 120, 178, 305 )
                                      AND mtg_cards_editions.card_id =
                                          mtg_cards.id) >
                              0 )
                            AND ( (SELECT COUNT(*)
                                   FROM   mtg_cards_formats
                                   WHERE  mtg_cards_formats.format_id IN ( 4 )
                                          AND mtg_cards_formats.card_id =
                                              mtg_cards.id)
                                  > 0
                                )
                            AND ( (SELECT COUNT(*)
                                   FROM   mtg_cards_colors
                                   WHERE  mtg_cards_colors.color_id IN ( 2, 3 )
                                          AND mtg_cards_colors.card_id =
                                              mtg_cards.id)
                                  > 0
                                  AND (SELECT COUNT(*)
                                       FROM   mtg_cards_colors
                                       WHERE  mtg_cards_colors.color_id NOT IN (
                                              2, 3 )
                                              AND mtg_cards_colors.card_id =
                                                  mtg_cards.id) =
                                      0 )
                     ORDER  BY inventory_count DESC,
                               name
                     LIMIT  30 offset 0) inner_fq,
                    (SELECT @i := 0) iter) AS fq
         ON ( fq.id = simple_mtg_cards.id )
ORDER  BY fq.o