Currently, when using View, should I use a rigid table instead?
I am currently discussing, 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 request:
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. (30 results take about 10 seconds). I think the reason my query was so slow is definitely due to the fact that this particular query is based on a VIEW that 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 explanation here looks rather cryptic and I don't know if I should give up the view and write a script to just push everything into the hard table view. (obviously, it will lose the flexibility of the presentation, since the display changes quite often).
Does anyone have any ideas how I can better optimize my circuit?
a source to share
The current plan uses the view as a slave table: it is scanned for each record in mapping_fs_key
withfsName = 'OVERALL'
You can replace the view with this function:
SELECT upID FROM uProductsInfo
JOIN fs_uProducts USING (upID)
JOIN mapping_fs_key USING (fsKeyID)
WHERE fsName='OVERALL'
AND upID IN
(
SELECT upID
FROM mapping_uGroups_Groups Z
JOIN productsInfo Y
ON y.gID = z.gID
JOIN mapping_uProducts_Products X
ON x.pID = y.pID
WHERE z.ugID = 1
)
ORDER BY
score DESC
LIMIT 0,30
a source to share