ahahah jes vala :)
Evo informacija :)
Server: 5.7.26-29-log - Percona Server (GPL), Release 29, Revision 11ad961
Explain:
Code:
mysql> EXPLAIN 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;
+----+-------------+------------------+------------+--------+-----------------+---------------------------+---------+----------------------------------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------------+------------+--------+-----------------+---------------------------+---------+----------------------------------------------+------+----------+-------------+
| 1 | SIMPLE | trending_overall | NULL | index | product_id,date | overall_review_percentage | 5 | NULL | 1432 | 3.49 | Using where |
| 1 | SIMPLE | trending | NULL | eq_ref | PRIMARY | PRIMARY | 8 | admin_competitor.trending_overall.product_id | 1 | 100.00 | NULL |
+----+-------------+------------------+------------+--------+-----------------+---------------------------+---------+----------------------------------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
EXPLAIN FORMAT=JSON
Code:
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "2826400.61"
},
"ordering_operation": {
"using_filesort": false,
"nested_loop": [
{
"table": {
"table_name": "trending_overall",
"access_type": "index",
"possible_keys": [
"product_id",
"date"
],
"key": "overall_review_percentage",
"used_key_parts": [
"overall_review_percentage"
],
"key_length": "5",
"rows_examined_per_scan": 1432,
"rows_produced_per_join": 3338582,
"filtered": "3.49",
"cost_info": {
"read_cost": "1470528.00",
"eval_cost": "667716.40",
"prefix_cost": "2138244.40",
"data_read_per_join": "458M"
},
"used_columns": [
"id",
"product_id",
"likes",
"review",
"overall_likes_percentage",
"overall_review_percentage",
"date"
],
"attached_condition": "((`admin_competitor`.`trending_overall`.`date` <=> 1568662153))"
}
},
{
"table": {
"table_name": "trending",
"access_type": "eq_ref",
"possible_keys": [
"PRIMARY"
],
"key": "PRIMARY",
"used_key_parts": [
"id"
],
"key_length": "8",
"ref": [
"admin_competitor.trending_overall.product_id"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 3338582,
"filtered": "100.00",
"cost_info": {
"read_cost": "3338582.00",
"eval_cost": "667716.40",
"prefix_cost": "6144542.80",
"data_read_per_join": "10G"
},
"used_columns": [
"id",
"asin",
"url",
"title",
"brand",
"category",
"image",
"price",
"likes_overall",
"review_score",
"review_score_overall",
"date_found",
"date_srapped",
"saved",
"date_saved"
]
}
}
]
}
}
}
A nece za EXPLAIN FORMAT=TREE i EXPLAIN ANALYZE :(
Evo i tabela:
Code:
CREATE TABLE `trending` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`asin` varchar(30) NOT NULL DEFAULT '',
`url` text,
`title` varchar(255) NOT NULL DEFAULT '',
`brand` varchar(255) NOT NULL DEFAULT '',
`category` varchar(255) NOT NULL DEFAULT '',
`image` varchar(255) NOT NULL DEFAULT '',
`price` decimal(8,2) unsigned NOT NULL DEFAULT '0.00',
`likes` int(6) unsigned NOT NULL DEFAULT '0',
`likes_overall` decimal(10,2) unsigned NOT NULL DEFAULT '0.00',
`reviews` int(6) unsigned NOT NULL DEFAULT '0',
`review_score` decimal(10,2) NOT NULL DEFAULT '0.00',
`review_score_overall` decimal(10,2) unsigned NOT NULL DEFAULT '0.00',
`date_found` bigint(20) unsigned NOT NULL DEFAULT '0',
`date_srapped` bigint(20) unsigned NOT NULL DEFAULT '0',
`saved` tinyint(1) unsigned NOT NULL DEFAULT '0',
`date_saved` bigint(20) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `asin` (`asin`),
KEY `review_score_overall` (`review_score_overall`),
KEY `likes_overall` (`likes_overall`),
FULLTEXT KEY `title` (`title`,`category`)
) ENGINE=InnoDB AUTO_INCREMENT=1718407 DEFAULT CHARSET=utf8
Code:
CREATE TABLE `trending_overall` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`product_id` bigint(20) unsigned NOT NULL DEFAULT '0',
`asin` varchar(30) NOT NULL DEFAULT '',
`price` decimal(10,2) unsigned NOT NULL DEFAULT '0.00',
`likes` decimal(10,2) unsigned NOT NULL DEFAULT '0.00',
`review` decimal(10,2) unsigned NOT NULL DEFAULT '0.00',
`overall_likes_percentage` decimal(10,2) NOT NULL DEFAULT '0.00',
`overall_review_percentage` decimal(10,2) NOT NULL DEFAULT '0.00',
`date` bigint(20) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `product_id` (`product_id`),
KEY `date` (`date`),
KEY `review` (`review`),
KEY `overall_review_percentage` (`overall_review_percentage`),
KEY `likes` (`likes`)
) ENGINE=InnoDB AUTO_INCREMENT=97530591 DEFAULT CHARSET=utf8