Explain Query (mysql)
EXPLAIN SELECT
s.supplier_id,
if('fr' = 'fr',s.name, sLang.l_name) as name,
if('fr' = 'fr',s.description, sLang.l_description) as description,
re.*,
IFNULL(si.images, '80_supplier-placeholder-2018.png') AS image_url,
supplier_rating.rating AS suppl_rating
FROM
suppliers s
LEFT JOIN regions re ON (re.region_id = s.region_id)
LEFT JOIN suppliersLang sLang ON sLang.supplier_id = s.supplier_id
LEFT JOIN (SELECT supplier_id, GROUP_CONCAT(image_url) as images FROM supplier_images GROUP BY supplier_id) si ON (si.supplier_id = s.supplier_id)
LEFT JOIN (
SELECT
ROUND(AVG(rating), 0) rating,
p.supplier_id
FROM (SELECT pur.*, uc.status, uc.parent_comment_id FROM product_user_ratings pur INNER JOIN (SELECT owner_id, user_id, status, parent_comment_id FROM user_comments) uc ON (pur.user_id = uc.user_id AND pur.product_id = uc.owner_id)) inter
INNER JOIN products p ON inter.product_id = p.product_id
WHERE
p.status = 1 AND
inter.updated_at BETWEEN SUBDATE(NOW(), INTERVAL 180 DAY) AND NOW() AND
inter.status = 1 AND
IFNULL(inter.parent_comment_id, 0) = 0
GROUP BY
p.supplier_id
) AS supplier_rating ON (supplier_rating.supplier_id = s.supplier_id)
WHERE
s.supplier_id IN (376, 972, 597, 737, 326, 525, 113, 893, 956, 58, 594, 823, 689, 922, 177, 1, 587, 1018, 284, 277, 61, 40, 70, 625, 297, 659, 1033, 1032, 400, 445, 616, 875, 963, 555, 426, 274, 622, 76, 334, 859, 860, 920, 729, 181, 155, 695, 979, 75, 54, 116, 701, 904, 987, 1037, 1035, 977, 971, 974, 1014, 736, 790, 957, 504, 927, 905, 879, 682, 469, 700, 495, 999, 453, 458, 487, 499, 481, 611, 352, 347, 510, 500, 292, 969, 324, 207, 861, 772, 886, 603, 665, 257, 776, 789, 895, 873, 712, 883, 745, 602, 220, 409, 563, 621, 890, 423, 1015, 766, 764, 342, 1041, 670, 765, 143, 627, 147, 660, 569, 661, 1028, 502, 548, 222, 939, 536, 290, 959, 259, 961, 393, 223, 512, 967, 731, 947, 983, 986, 1025, 644, 609, 728, 243, 229, 1039, 450, 578, 788, 677, 511, 540, 244, 851, 330, 1038, 227, 554, 630, 1044, 325, 262, 596, 824, 279, 586, 1019, 626, 600, 725, 615, 218, 234, 1010, 524, 658, 945, 1023, 1034, 898, 888, 230, 918, 1017, 278, 247, 575, 673, 878, 521, 129, 553, 265, 339, 199, 925, 329, 432, 1045, 498, 133, 441, 579, 849, 657, 48, 1027, 149, 533, 1043, 858, 560, 165, 1048, 1022, 710, 576, 1026, 179, 248, 249, 338, 795, 938, 496, 507, 604, 868, 196, 566, 943, 976, 857, 368, 305, 866, 272, 693, 706, 72, 1016, 718, 537, 428, 709, 877, 585, 962, 286, 154, 194, 629, 847, 412, 740, 584, 639, 652, 643, 1011, 641, 773, 651, 1012, 1002, 1009, 1030, 988, 676, 647, 856, 708, 719, 842, 808, 648, 965, 680, 739, 529, 735, 948, 1020, 769, 928, 1029, 1021, 923, 505, 448, 942, 926, 996, 558, 144, 800, 852, 985, 478, 799, 802, 980, 567, 1013, 591, 1036, 490, 702, 699, 946, 688, 543, 599, 559, 492, 1008, 964, 990, 1007, 460, 876, 978, 515, 724, 534, 255, 863, 65, 771, 975, 887, 993, 590, 240, 703, 572, 698, 197, 574, 583, 997, 516, 825, 981, 891, 506, 960, 550, 989, 440, 913, 253, 750, 994, 96, 405, 541, 402, 1040, 417, 398, 394, 1047, 944, 475, 872)
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | PRIMARY | s | range | PRIMARY | PRIMARY | 4 | 363 | 100.00 | Using where | ||
| 1 | PRIMARY | re | eq_ref | PRIMARY | PRIMARY | 4 | securelufacom.s.region_id | 1 | 100.00 | ||
| 1 | PRIMARY | sLang | ref | supplier_id | supplier_id | 4 | securelufacom.s.supplier_id | 1 | 100.00 | ||
| 1 | PRIMARY | <derived2> | ref | <auto_key0> | <auto_key0> | 4 | securelufacom.s.supplier_id | 10 | 100.00 | ||
| 1 | PRIMARY | <derived3> | ref | <auto_key0> | <auto_key0> | 5 | securelufacom.s.supplier_id | 10 | 100.00 | ||
| 3 | DERIVED | <derived4> | ALL | 3360 | 100.00 | Using temporary | |||||
| 3 | DERIVED | p | eq_ref | PRIMARY, idx_status | PRIMARY | 4 | inter.product_id | 1 | 20.98 | Using where | |
| 4 | DERIVED | <derived5> | ALL | 30248 | 100.00 | ||||||
| 4 | DERIVED | pur | eq_ref | unique_rating, idx_product_id | unique_rating | 8 | uc.owner_id, uc.user_id | 1 | 11.11 | Using where | |
| 5 | DERIVED | user_comments | ref | idx_status | idx_status | 4 | const | 30248 | 100.00 | Using where | |
| 2 | DERIVED | supplier_images | ALL | 2565 | 100.00 | Using filesort |