I am currently debating whether my table, mapping_uGroups_uProducts, which is a view formed by the following table:

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` 
    SQL SECURITY DEFINER VIEW `db`.`mapping_uGroups_uProducts` 
    AS select distinct `X`.`upID` AS `upID`,`Z`.`ugID` AS `ugID` from 
    ((`db`.`mapping_uProducts_Products` `X` join `db`.`productsInfo` `Y` 
            on((`X`.`pID` = `Y`.`pID`))) join `db`.`mapping_uGroups_Groups` `Z` 
            on((`Y`.`gID` = `Z`.`gID`)));

My current query is:

    SELECT upID FROM uProductsInfo \
        JOIN fs_uProducts USING (upID) column \
        JOIN mapping_uGroups_uProducts USING (upID) -- could be faster if we use hard table and index \
        JOIN mapping_fs_key USING (fsKeyID)  \
    WHERE fsName="OVERALL"  \
        AND ugID=1          \
    ORDER BY score DESC     \
    LIMIT 0,30;

which is pretty slow. (for 30 results, it requires about 10 secondes). I think the reason for my query being so slow is definitely due to the fact that that particular query relies on a VIEW which has no index to speed things up.

+----+-------------+----------------+--------+----------------+---------+---------+---------------------------------------+-------+---------------------------------+
        | id | select_type | table          | type   | possible_keys  | key     | key_len | ref                                   | rows  | Extra                           |
        +----+-------------+----------------+--------+----------------+---------+---------+---------------------------------------+-------+---------------------------------+
        |  1 | PRIMARY     | mapping_fs_key | const  | PRIMARY,fsName | fsName  | 386     | const                                 |     1 | Using temporary; Using filesort | 
        |  1 | PRIMARY     | <derived2>     | ALL    | NULL           | NULL    | NULL    | NULL                                  | 19706 | Using where                     | 
        |  1 | PRIMARY     | uProductsInfo  | eq_ref | PRIMARY        | PRIMARY | 4       | mapping_uGroups_uProducts.upID        |     1 | Using index                     | 
        |  1 | PRIMARY     | fs_uProducts   | ref    | upID           | upID    | 4       | db.uProductsInfo.upID                 |   221 | Using where                     | 
        |  2 | DERIVED     | X              | ALL    | PRIMARY        | NULL    | NULL    | NULL                                  | 40772 | Using temporary                 | 
        |  2 | DERIVED     | Y              | eq_ref | PRIMARY        | PRIMARY | 4       | db.X.pID                              |     1 | Distinct                        | 
        |  2 | DERIVED     | Z              | ref    | PRIMARY        | PRIMARY | 4       | db.Y.gID                              |     2 | Using index; Distinct           | 
        +----+-------------+----------------+--------+----------------+---------+---------+---------------------------------------+-------+---------------------------------+
        7 rows in set (0.48 sec)

The explain here looks pretty cryptic, and I don't know whether I should drop view and write a script to just insert everything in the view to a hard table. ( obviously, it will lose the flexibility of the view since the mapping changes quite frequently).

Does anyone have any idea to how I can optimize my schema better?