mysql [localhost:8017] {msandbox} (test) > EXPLAIN format=tree SELECT * FROM trending_overall WHERE date = 1568662153 ORDER BY overall_review_percentage DESC;
+--------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+--------------------------------------------------------------------------------------------------------------------------------+
| -> Sort: trending_overall.overall_review_percentage DESC
-> Index lookup on trending_overall using date (date=1568662153)
|
+--------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.09 sec)
mysql [localhost:8017] {msandbox} (test) > alter table trending_overall add key date_overallpc (`date`, `overall_review_percentage`);
Query OK, 0 rows affected (19 min 23.65 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql [localhost:8017] {msandbox} (test) > EXPLAIN format=tree SELECT * FROM trending_overall WHERE date = 1568662153 ORDER BY overall_review_percentage DESC;
+------------------------------------------------------------------------------------------------+
| EXPLAIN |
+------------------------------------------------------------------------------------------------+
| -> Index lookup on trending_overall using date_overallpc (date=1568662153; iterate backwards)
|
+------------------------------------------------------------------------------------------------+
1 row in set (0.09 sec)
upit je brz sada sa ovim kompozitom i radi lepo ..
e sad ovaj sto si reko:
mysql [localhost:8017] {msandbox} (test) > EXPLAIN format=tree SELECT trending.id, trending.asin, trending.url, trending.title, trending.brand, trending.category, trending.image, trending.price, trending.likes_overall, trending.review_score, trending.review_score_overall, trending.date_found, trending.date_srapped, trending.saved, trending.date_saved, trending_overall.likes, trending_overall.review, trending_overall.overall_review_percentage as review_score_daily, trending_overall.overall_likes_percentage as likes_daily
-> FROM trending
-> JOIN trending_overall ON trending_overall.product_id = trending.id
-> WHERE trending_overall.date = 1568662153
-> ORDER BY review_score_daily DESC
-> LIMIT 50;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Limit: 50 row(s)
-> Nested loop inner join
-> Index lookup on trending_overall using date_overallpc (date=1568662153; iterate backwards)
-> Single-row index lookup on trending using PRIMARY (id=trending_overall.product_id)
|
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
i on izgleda ok