Всем привет!
Долго выполняется запрос!
1.
2.
3.
4.
5.
6.
SELECT DISTINCT e.* , IF(a_promo.alt_price, a_promo.alt_price, IF((a_promo.discount >0)AND(a_promo.departament_key =23769), a_departaments.DepRetail * a_promo.discount /100, IF(e.Key_Sip NOT IN (40823,40824,40838,40839,40840,40841,40842,40843,40844,40845,40846,40825,40847,40848,40849,40850,40851,40852,40853,40854,40826,40855,40856,40857,40858,40859,40860,40861,40827,40862,40863,40864,40865,40866,40867,40868,40869,40828,40870,40871,40872,40873,40874,40875,40876,40877,40829,40878,40879,40880,40830,40881,40882,40883,40884,40831,40885,40886,40887,40888,40889,40890,40891,40892,40832,40893,40894,40895,40896,40897,40833,40898,40899,40900,40901,40902,40835,40903,40904,40905,40906,40907,40908,40909,40910,40911,40836,40912,40913,40914,40915,42490,42491,42500,42501,42502,42503,42504,42505,42492,42506,42507,42508,42509,42510,42511,42493,42512,42513,42494,42514,42515,42516,42517,42495,42518,42496,42519,42520,42521,42522,42523,42524,42525,42526,42497,42527,42528,42529,42530,42531,42532,42533,42534,42498,42535,42536,42537,42538,42539,42499,42540,42541,42542,42543,23224,25302,25303,25304,25305,25307),a_departaments.DepRetail* 0.9,a_departaments.DepRetail))) AS retailPrice, a_departaments.DepRetail, el_act.name as actName, el_act.action_id
FROM a_elements e LEFT JOIN a_promo USING (Key_G) LEFT JOIN a_elements_have_images USING (Key_G) LEFT JOIN a_departaments USING (Key_G) LEFT JOIN a_elems_actions el_act USING (Key_G), a_elements_epson
WHERE a_elements_epson.Key_G = e.Key_G
AND e.Key_Sip IN (16944,16945,16946,16947,16948,16949,16950,16951,16952,16953,16954,25611,16955,16956,16957,16958,16959,16960,16961,16962,16963,16964,16965,16967,16968,16969,16970,16971,16966,16972,16973,16974,16975,16976,16977,16978,16979,16980,16981,16982,16983,16984,16986,16987,16988,16989,16990,16985,16991,16992,16993,16997)
ORDER BY exist DESC, have_image DESC, valRating ASC, e.Key_Sip DESC
LIMIT 12910,10
Пока не очень понимаю как его оптимизировать.
автор Профилирование:
Состояние Время
starting 0.000015
Waiting for query cache lock 0.000004
checking query cache for query 0.000217
checking permissions 0.000005
checking permissions 0.000003
checking permissions 0.000003
checking permissions 0.000003
checking permissions 0.000003
checking permissions 0.000005
Opening tables 0.000051
System lock 0.000016
Waiting for query cache lock 0.000039
init 0.000204
optimizing 0.000024
statistics 0.011863
preparing 0.012581
Creating tmp table 0.000093
executing 0.000004
Copying to tmp table 7.530905
converting HEAP to MyISAM 0.498932
Copying to tmp table on disk 4.881437
Sorting result 0.120527
Sending data 0.114850
end 0.000016
removing tmp table 0.840435
end 0.000020
query end 0.000006
closing tables 0.000033
freeing items 0.000046
Waiting for query cache lock 0.000004
freeing items 0.000025
Waiting for query cache lock 0.000003
freeing items 0.000003
storing result in query cache 0.000005
logging slow query 0.000004
logging slow query 0.000157
cleaning up 0.000010
Структура таблиц
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
CREATE TABLE IF NOT EXISTS `a_promo` (
`key_g` int(11) NOT NULL,
`alt_price` decimal(9,2) DEFAULT NULL,
`departament_key` int(11) DEFAULT NULL,
`discount` int(11) DEFAULT NULL,
`key_promo_disc` bigint(20) NOT NULL,
`internet_only` int(11) NOT NULL DEFAULT '0',
KEY `key_g` (`key_g`),
KEY `departament_key` (`departament_key`),
KEY `key_g_2` (`key_g`,`alt_price`,`departament_key`),
KEY `key_g_3` (`key_g`,`departament_key`,`discount`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `a_departaments` (
`Key_G` int(11) NOT NULL,
`KeyDep` int(5) NOT NULL,
`DepRetail` float NOT NULL,
`DepQty` float NOT NULL,
KEY `Key_G` (`Key_G`,`KeyDep`,`DepRetail`,`DepQty`),
KEY `KeyDep` (`KeyDep`,`DepQty`),
KEY `Key_G_2` (`Key_G`),
KEY `Key_G_3` (`Key_G`,`KeyDep`,`DepQty`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE IF NOT EXISTS `a_elements` (
`Key_G` int(11) NOT NULL,
`Name` varchar(950) COLLATE utf8_unicode_ci DEFAULT NULL,
`Retail` float DEFAULT NULL,
`AnnotFile` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`ImageFile` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`IncomeDate` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`IsNew` varchar(3) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0',
`isLeader` varchar(3) COLLATE utf8_unicode_ci NOT NULL,
`Qty` int(11) DEFAULT NULL,
`LocCode` varchar(250) COLLATE utf8_unicode_ci DEFAULT NULL,
`LocName` varchar(250) COLLATE utf8_unicode_ci DEFAULT NULL,
`Key_Sip` int(11) DEFAULT NULL,
`IsActive` varchar(3) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0',
`valAuthor` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`valPublishing` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`valSerial` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`valYear` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`valISBN` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`valTranslate` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`valPainter` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`valEdition` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`valWeight` varchar(11) COLLATE utf8_unicode_ci NOT NULL,
`valRating` int(11) NOT NULL,
`isHandled` int(2) NOT NULL,
`checkString` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
`last_change` datetime NOT NULL,
UNIQUE KEY `Key_G` (`Key_G`),
KEY `Key_Sip` (`Key_Sip`),
KEY `str2` (`checkString`),
KEY `strSIP` (`Key_G`,`checkString`),
KEY `Key_G_2` (`Key_G`,`Retail`,`Qty`,`Key_Sip`),
KEY `Key_G_3` (`Key_G`,`IsActive`),
KEY `Key_G_4` (`Key_G`,`Qty`,`IsActive`),
KEY `valPublishing` (`valPublishing`),
KEY `Key_G_5` (`Key_G`,`Key_Sip`,`IsActive`,`valSerial`),
KEY `Key_G_6` (`Key_G`,`Key_Sip`,`IsActive`,`valAuthor`),
KEY `Key_G_7` (`Key_G`,`Key_Sip`,`IsActive`),
KEY `Key_G_8` (`Key_G`,`Key_Sip`),
KEY `valRating` (`valRating`),
KEY `Key_Sip_2` (`Key_Sip`,`valRating`),
KEY `Key_G_9` (`Key_G`,`Key_Sip`,`valRating`),
FULLTEXT KEY `Name` (`Name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE IF NOT EXISTS `a_elements_have_images` (
`key_g` int(11) NOT NULL,
`have_image` tinyint(1) NOT NULL,
`income_date` date NOT NULL,
`date_have_image` date NOT NULL,
UNIQUE KEY `key_g_2` (`key_g`),
KEY `key_g` (`key_g`,`have_image`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE IF NOT EXISTS `a_elements_epson` (
`Key_G` int(11) NOT NULL,
`exist` tinyint(1) NOT NULL,
UNIQUE KEY `Key_G_3` (`Key_G`),
KEY `exist` (`exist`),
KEY `Key_G_2` (`Key_G`,`exist`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE IF NOT EXISTS `a_elems_actions` (
`key_g` int(11) NOT NULL,
`action_id` int(11) NOT NULL,
`name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
KEY `key_g` (`key_g`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
|
