Оптимизация select mysql
Есть 3 таблицы. data, modemsInfo, dataResponse.
data - вся дежурная ежеминутная информация с модемов +100строк/мин(~1.5млн всего пока),
modemsInfo - информация о модемах, кому принадлежит итд(сейчас ~10шт. более 3000шт не планируется),
dataResponse - ответы модемов. По сути тоже самое что data но мало строк(пока ~10)
Раз в 5 сек собираю последнюю информацию по всем модемам владельца (далее в коде idCompany). В определенный момент время запроса занимало 6с и чем больше становилось модемов, которые не передавали еще никакой информации data и dataResponse, тем страшней становилось время выборки. +1с за один свежий модем. Ковырял, оптимизировал используя различные статьи и ответы. Стало 1.4с. Чуть полегче, но мне кажется это очень долго. Подскажите, пожалуйста, кому не лень в этом ковыряться, что еще можно сделать?
Завязаны между собой через serialNum и typeReceipt тоже самое что и ** modeDialog**
Наполнение таблицы data:
INSERT INTO u2378356_ledMonitor.data (id, md5receipt, serialNum, mode, Ub, Ib, Up, IL, Ta, Tc, tL, tN, PL, Hand, Fire, Night, `Load`, Disch, OverIL, ShortL, ip, typeReceipt, timeReceipt) VALUES (1227124, '9faec1317eb285f9f20254b6d2e20670', 111111111111, 0, 83206, 76105, 10553, 25226, 80, 0, 411, 74, 22, 0, 0, 1, 0, 0, 0, 0, 1370562830, 0, '2023-12-17 14:00:10');
INSERT INTO u2378356_ledMonitor.data (id, md5receipt, serialNum, mode, Ub, Ib, Up, IL, Ta, Tc, tL, tN, PL, Hand, Fire, Night, `Load`, Disch, OverIL, ShortL, ip, typeReceipt, timeReceipt) VALUES (1227122, '931a1e61b06b2fbcbdea07f6d8fe59ed', 111111111111, 0, 12809, 99159, 42986, 1461, 75, 0, 575, 838, 42, 0, 0, 1, 0, 0, 0, 0, 1370562830, 0, '2023-12-17 14:00:10');
INSERT INTO u2378356_ledMonitor.data (id, md5receipt, serialNum, mode, Ub, Ib, Up, IL, Ta, Tc, tL, tN, PL, Hand, Fire, Night, `Load`, Disch, OverIL, ShortL, ip, typeReceipt, timeReceipt) VALUES (1227121, '393fd8d56d1338b6e5742a95bdd17a58', 822917000001, 0, 24478, 14257, 73864, 43259, 34, 31, 673, 936, 45, 0, 0, 1, 0, 0, 0, 0, 1370562830, 0, '2023-12-17 14:00:10');
INSERT INTO u2378356_ledMonitor.data (id, md5receipt, serialNum, mode, Ub, Ib, Up, IL, Ta, Tc, tL, tN, PL, Hand, Fire, Night, `Load`, Disch, OverIL, ShortL, ip, typeReceipt, timeReceipt) VALUES (1227120, '6592908f518c9b43c0dfc6bcc798bfbd', 111111111111, 0, 55502, 40960, 80864, 26944, 81, 63, 876, 152, 80, 0, 0, 1, 0, 0, 0, 0, 1370562830, 0, '2023-12-17 14:00:10');
INSERT INTO u2378356_ledMonitor.data (id, md5receipt, serialNum, mode, Ub, Ib, Up, IL, Ta, Tc, tL, tN, PL, Hand, Fire, Night, `Load`, Disch, OverIL, ShortL, ip, typeReceipt, timeReceipt) VALUES (1227119, 'b3f8f973c5f3ccabfc5f58be74249c9b', 822125000000, 0, 18944, 89632, 75158, 46181, 58, 87, 812, 368, 65, 0, 0, 1, 0, 0, 0, 0, 1370562830, 1, '2023-12-17 14:00:10');
INSERT INTO u2378356_ledMonitor.data (id, md5receipt, serialNum, mode, Ub, Ib, Up, IL, Ta, Tc, tL, tN, PL, Hand, Fire, Night, `Load`, Disch, OverIL, ShortL, ip, typeReceipt, timeReceipt) VALUES (1227118, '08d986357608dd939184cfbdc1041a81', 822125000000, 0, 13852, 61660, 99013, 51948, 77, 78, 848, 558, 35, 0, 0, 1, 0, 0, 0, 0, 1370562830, 1, '2023-12-17 14:00:10');
INSERT INTO u2378356_ledMonitor.data (id, md5receipt, serialNum, mode, Ub, Ib, Up, IL, Ta, Tc, tL, tN, PL, Hand, Fire, Night, `Load`, Disch, OverIL, ShortL, ip, typeReceipt, timeReceipt) VALUES (1227117, 'eaf4a664592b29aba94f5aefe4519c6e', 822917000001, 0, 19293, 14457, 81638, 5465, 26, 3, 818, 951, 95, 0, 0, 1, 0, 0, 0, 0, 1370562830, 1, '2023-12-17 14:00:10');
INSERT INTO u2378356_ledMonitor.data (id, md5receipt, serialNum, mode, Ub, Ib, Up, IL, Ta, Tc, tL, tN, PL, Hand, Fire, Night, `Load`, Disch, OverIL, ShortL, ip, typeReceipt, timeReceipt) VALUES (1227116, 'e54394b113c5d91c77eb068772c234e7', 822917000001, 0, 56479, 56457, 32901, 75286, 69, 75, 456, 205, 38, 0, 0, 1, 0, 0, 0, 0, 1370562830, 1, '2023-12-17 14:00:09');
INSERT INTO u2378356_ledMonitor.data (id, md5receipt, serialNum, mode, Ub, Ib, Up, IL, Ta, Tc, tL, tN, PL, Hand, Fire, Night, `Load`, Disch, OverIL, ShortL, ip, typeReceipt, timeReceipt) VALUES (1227115, 'a410df8e915d78d4e65020e16a9061e7', 111111111111, 0, 25941, 33667, 78140, 83016, 36, 83, 67, 314, 14, 0, 0, 1, 0, 0, 0, 0, 1370562830, 1, '2023-12-17 14:00:09');
INSERT INTO u2378356_ledMonitor.data (id, md5receipt, serialNum, mode, Ub, Ib, Up, IL, Ta, Tc, tL, tN, PL, Hand, Fire, Night, `Load`, Disch, OverIL, ShortL, ip, typeReceipt, timeReceipt) VALUES (1227111, '43a8f3d4dbcf7af684d7ec31183f7c2c', 111111111111, 0, 28033, 69911, 41437, 97644, 20, 42, 742, 384, 88, 0, 0, 1, 0, 0, 0, 0, 1370562830, 1, '2023-12-17 14:00:09');
INSERT INTO u2378356_ledMonitor.data (id, md5receipt, serialNum, mode, Ub, Ib, Up, IL, Ta, Tc, tL, tN, PL, Hand, Fire, Night, `Load`, Disch, OverIL, ShortL, ip, typeReceipt, timeReceipt) VALUES (1227110, '1e084b1a8028754ef6a6a6113f686638', 822917000001, 0, 58290, 28145, 8922, 54037, 12, 53, 135, 951, 29, 0, 0, 1, 0, 0, 0, 0, 1370562830, 1, '2023-12-17 14:00:09');
INSERT INTO u2378356_ledMonitor.data (id, md5receipt, serialNum, mode, Ub, Ib, Up, IL, Ta, Tc, tL, tN, PL, Hand, Fire, Night, `Load`, Disch, OverIL, ShortL, ip, typeReceipt, timeReceipt) VALUES (1227109, 'bd5ab82e1aee708f560805983724568e', 822125000000, 0, 70906, 3568, 53202, 41062, 28, 28, 914, 527, 53, 0, 0, 1, 0, 0, 0, 0, 1370562830, 1, '2023-12-17 14:00:09');
INSERT INTO u2378356_ledMonitor.data (id, md5receipt, serialNum, mode, Ub, Ib, Up, IL, Ta, Tc, tL, tN, PL, Hand, Fire, Night, `Load`, Disch, OverIL, ShortL, ip, typeReceipt, timeReceipt) VALUES (1227108, 'ea2247e979a8062840c650c0f5254036', 111111111111, 0, 81361, 46912, 87251, 11789, 9, 89, 452, 971, 75, 0, 0, 1, 0, 0, 0, 0, 1370562830, 1, '2023-12-17 14:00:09');
INSERT INTO u2378356_ledMonitor.data (id, md5receipt, serialNum, mode, Ub, Ib, Up, IL, Ta, Tc, tL, tN, PL, Hand, Fire, Night, `Load`, Disch, OverIL, ShortL, ip, typeReceipt, timeReceipt) VALUES (1227104, '9642c393186c731193c51828490d76d3', 111111111111, 0, 94653, 222, 37479, 78288, 78, 89, 134, 96, 79, 0, 0, 1, 0, 0, 0, 0, 1370562830, 1, '2023-12-17 14:00:08');
INSERT INTO u2378356_ledMonitor.data (id, md5receipt, serialNum, mode, Ub, Ib, Up, IL, Ta, Tc, tL, tN, PL, Hand, Fire, Night, `Load`, Disch, OverIL, ShortL, ip, typeReceipt, timeReceipt) VALUES (1227103, '7d5780e49716d92c33c2e6f500021026', 111111111111, 0, 76185, 85751, 46847, 66784, 97, 66, 725, 19, 59, 0, 0, 1, 0, 0, 0, 0, 1370562830, 1, '2023-12-17 14:00:08');
INSERT INTO u2378356_ledMonitor.data (id, md5receipt, serialNum, mode, Ub, Ib, Up, IL, Ta, Tc, tL, tN, PL, Hand, Fire, Night, `Load`, Disch, OverIL, ShortL, ip, typeReceipt, timeReceipt) VALUES (1227102, 'dac779c303bcf599174685a4d629b657', 822125000000, 0, 8978, 23016, 82799, 36104, 66, 64, 550, 817, 26, 0, 0, 1, 0, 0, 0, 0, 1370562830, 1, '2023-12-17 14:00:08');
INSERT INTO u2378356_ledMonitor.data (id, md5receipt, serialNum, mode, Ub, Ib, Up, IL, Ta, Tc, tL, tN, PL, Hand, Fire, Night, `Load`, Disch, OverIL, ShortL, ip, typeReceipt, timeReceipt) VALUES (1227101, 'f0db8b14b9357a5d0db62110939994e3', 822125000000, 0, 39960, 69244, 26330, 24935, 38, 43, 197, 554, 13, 0, 0, 1, 0, 0, 0, 0, 1370562830, 1, '2023-12-17 14:00:08');
INSERT INTO u2378356_ledMonitor.data (id, md5receipt, serialNum, mode, Ub, Ib, Up, IL, Ta, Tc, tL, tN, PL, Hand, Fire, Night, `Load`, Disch, OverIL, ShortL, ip, typeReceipt, timeReceipt) VALUES (1227100, '8c9712df7f264efeb934753d64cbe232', 822917000001, 0, 78323, 9031, 9834, 23217, 94, 45, 461, 856, 58, 0, 0, 1, 0, 0, 0, 0, 1370562830, 1, '2023-12-17 14:00:08');
INSERT INTO u2378356_ledMonitor.data (id, md5receipt, serialNum, mode, Ub, Ib, Up, IL, Ta, Tc, tL, tN, PL, Hand, Fire, Night, `Load`, Disch, OverIL, ShortL, ip, typeReceipt, timeReceipt) VALUES (1227099, '05c9d72b9f7dc9995c1c5d2981b1fb9d', 822125000000, 0, 25667, 74187, 25871, 42459, 59, 83, 858, 455, 45, 0, 0, 1, 0, 0, 0, 0, 1370562830, 1, '2023-12-17 14:00:08');
INSERT INTO u2378356_ledMonitor.data (id, md5receipt, serialNum, mode, Ub, Ib, Up, IL, Ta, Tc, tL, tN, PL, Hand, Fire, Night, `Load`, Disch, OverIL, ShortL, ip, typeReceipt, timeReceipt) VALUES (1227098, 'adb125ba523e789c43b280bf23572ac4', 822917000001, 0, 42359, 72061, 36836, 29225, 40, 68, 677, 695, 40, 0, 0, 1, 0, 0, 0, 0, 1370562830, 1, '2023-12-17 14:00:08');
INSERT INTO u2378356_ledMonitor.data (id, md5receipt, serialNum, mode, Ub, Ib, Up, IL, Ta, Tc, tL, tN, PL, Hand, Fire, Night, `Load`, Disch, OverIL, ShortL, ip, typeReceipt, timeReceipt) VALUES (1227097, 'f6c062d3b84362ec93a509a44f199f0f', 111111111111, 0, 40311, 80755, 36442, 33862, 6, 44, 452, 746, 52, 0, 0, 1, 0, 0, 0, 0, 1370562830, 1, '2023-12-17 14:00:08');
INSERT INTO u2378356_ledMonitor.data (id, md5receipt, serialNum, mode, Ub, Ib, Up, IL, Ta, Tc, tL, tN, PL, Hand, Fire, Night, `Load`, Disch, OverIL, ShortL, ip, typeReceipt, timeReceipt) VALUES (1227096, '8b4c70bb37860e457170c7d57eb64b99', 822125000000, 0, 6601, 98527, 1455, 11081, 30, 57, 986, 818, 89, 0, 0, 1, 0, 0, 0, 0, 1370562830, 1, '2023-12-17 14:00:08');
INSERT INTO u2378356_ledMonitor.data (id, md5receipt, serialNum, mode, Ub, Ib, Up, IL, Ta, Tc, tL, tN, PL, Hand, Fire, Night, `Load`, Disch, OverIL, ShortL, ip, typeReceipt, timeReceipt) VALUES (1227095, 'b6eef858ec15886c113510c970f64e23', 822917000001, 0, 99418, 31939, 52525, 90310, 80, 40, 970, 955, 20, 0, 0, 1, 0, 0, 0, 0, 1370562830, 1, '2023-12-17 14:00:08');
INSERT INTO u2378356_ledMonitor.data (id, md5receipt, serialNum, mode, Ub, Ib, Up, IL, Ta, Tc, tL, tN, PL, Hand, Fire, Night, `Load`, Disch, OverIL, ShortL, ip, typeReceipt, timeReceipt) VALUES (1227090, 'b8c3812632b8582d874a2e89546e07d7', 822917000001, 0, 64064, 40638, 17418, 24982, 77, 30, 541, 585, 38, 0, 0, 1, 0, 0, 0, 0, 1370562830, 1, '2023-12-17 14:00:07');
INSERT INTO u2378356_ledMonitor.data (id, md5receipt, serialNum, mode, Ub, Ib, Up, IL, Ta, Tc, tL, tN, PL, Hand, Fire, Night, `Load`, Disch, OverIL, ShortL, ip, typeReceipt, timeReceipt) VALUES (1227089, '26532d9cb67ded61bad390675ea4f552', 822917000001, 0, 22571, 41643, 52396, 62944, 54, 73, 448, 93, 22, 0, 0, 1, 0, 0, 0, 0, 1370562830, 1, '2023-12-17 14:00:07');
INSERT INTO u2378356_ledMonitor.data (id, md5receipt, serialNum, mode, Ub, Ib, Up, IL, Ta, Tc, tL, tN, PL, Hand, Fire, Night, `Load`, Disch, OverIL, ShortL, ip, typeReceipt, timeReceipt) VALUES (1227082, '268405297d3e3fcccb2a17c0d3173dd0', 111111111111, 0, 73953, 44671, 71077, 64229, 39, 23, 358, 474, 83, 0, 0, 1, 0, 0, 0, 0, 1370562830, 1, '2023-12-17 14:00:07');
INSERT INTO u2378356_ledMonitor.data (id, md5receipt, serialNum, mode, Ub, Ib, Up, IL, Ta, Tc, tL, tN, PL, Hand, Fire, Night, `Load`, Disch, OverIL, ShortL, ip, typeReceipt, timeReceipt) VALUES (1227081, '405d9b0bf00bd68fa256b094843dcee6', 111111111111, 0, 63800, 68218, 58017, 81928, 17, 79, 919, 195, 45, 0, 0, 1, 0, 0, 0, 0, 1370562830, 1, '2023-12-17 14:00:07');
INSERT INTO u2378356_ledMonitor.data (id, md5receipt, serialNum, mode, Ub, Ib, Up, IL, Ta, Tc, tL, tN, PL, Hand, Fire, Night, `Load`, Disch, OverIL, ShortL, ip, typeReceipt, timeReceipt) VALUES (1227080, '81786eb3a525e88f8251e760f2bdaa0b', 822125000000, 0, 39770, 10714, 39325, 61061, 62, 69, 656, 316, 91, 0, 0, 1, 0, 0, 0, 0, 1370562830, 1, '2023-12-17 14:00:07');
INSERT INTO u2378356_ledMonitor.data (id, md5receipt, serialNum, mode, Ub, Ib, Up, IL, Ta, Tc, tL, tN, PL, Hand, Fire, Night, `Load`, Disch, OverIL, ShortL, ip, typeReceipt, timeReceipt) VALUES (1227079, '8893945f6a00932f278011122e44558b', 822917000001, 0, 90452, 70936, 44922, 51558, 55, 95, 63, 50, 98, 0, 0, 1, 0, 0, 0, 0, 1370562830, 1, '2023-12-17 14:00:07');
INSERT INTO u2378356_ledMonitor.data (id, md5receipt, serialNum, mode, Ub, Ib, Up, IL, Ta, Tc, tL, tN, PL, Hand, Fire, Night, `Load`, Disch, OverIL, ShortL, ip, typeReceipt, timeReceipt) VALUES (1227078, 'b8f9b8c17c51ef0dca852868b144a67b', 111111111111, 0, 22306, 56306, 26453, 36391, 63, 32, 623, 93, 90, 0, 0, 1, 0, 0, 0, 0, 1370562830, 1, '2023-12-17 14:00:07');
Наполнение таблицы dataResponse:
INSERT INTO u2378356_ledMonitor.dataResponse (md5receipt, serialNum, text, ip, commandResponse, timeReceipt,
typeReceipt)
VALUES ('1', null, '822917000001', 11212, '1', null, null);
INSERT INTO u2378356_ledMonitor.dataResponse (md5receipt, serialNum, text, ip, commandResponse, timeReceipt,
typeReceipt)
VALUES ('2', null, '822917000000', 1212, '1', null, null);
INSERT INTO u2378356_ledMonitor.dataResponse (md5receipt, serialNum, text, ip, commandResponse, timeReceipt,
typeReceipt)
VALUES ('3', null, '822917000001', null, '1', null, null);
INSERT INTO u2378356_ledMonitor.dataResponse (md5receipt, serialNum, text, ip, commandResponse, timeReceipt,
typeReceipt)
VALUES ('4', null, '822125000000', 11313, '1', null, null);
INSERT INTO u2378356_ledMonitor.dataResponse (md5receipt, serialNum, text, ip, commandResponse, timeReceipt,
typeReceipt)
VALUES ('5', null, '822125000000', 4114, '1', null, null);
INSERT INTO u2378356_ledMonitor.dataResponse (md5receipt, serialNum, text, ip, commandResponse, timeReceipt,
typeReceipt)
VALUES ('6', null, '822917000001', 1212, '1', null, null);
INSERT INTO u2378356_ledMonitor.dataResponse (md5receipt, serialNum, text, ip, commandResponse, timeReceipt,
typeReceipt)
VALUES ('9', null, '822917000001', 1212, '1', null, null);
INSERT INTO u2378356_ledMonitor.dataResponse (md5receipt, serialNum, text, ip, commandResponse, timeReceipt,
typeReceipt)
VALUES ('10', null, '822917000001', null, '1', null, null);
INSERT INTO u2378356_ledMonitor.dataResponse (md5receipt, serialNum, text, ip, commandResponse, timeReceipt,
typeReceipt)
VALUES ('11', null, '822917000001', 1212, '1', null, null);
INSERT INTO u2378356_ledMonitor.dataResponse (md5receipt, serialNum, text, ip, commandResponse, timeReceipt,
typeReceipt)
VALUES ('12', null, '822917000001', 241515, '1', null, null);
INSERT INTO u2378356_ledMonitor.dataResponse (md5receipt, serialNum, text, ip, commandResponse, timeReceipt,
typeReceipt)
VALUES ('13', null, '822917000000', 1535132, '1', null, null);
INSERT INTO u2378356_ledMonitor.dataResponse (md5receipt, serialNum, text, ip, commandResponse, timeReceipt,
typeReceipt)
VALUES ('14', null, '822917000001', 4361, '2', null, null);
INSERT INTO u2378356_ledMonitor.dataResponse (md5receipt, serialNum, text, ip, commandResponse, timeReceipt,
typeReceipt)
VALUES ('15', null, '822125000000', 346, '1', null, null);
INSERT INTO u2378356_ledMonitor.dataResponse (md5receipt, serialNum, text, ip, commandResponse, timeReceipt,
typeReceipt)
VALUES ('16', null, '822125000000', 13464, '2', null, null);
Наполнение таблицы modemsInfo:
INSERT INTO u2378356_ledMonitor.modemsInfo (id, serialNum, idCompany, nameModem, phoneNumber, modeDialog,
commandStatusGPRS, region, city, street, structure, latitude, longitude,
timeActivation, timeRegister, idGroup, idIcon, model, keyDelete,
endTimeKeyDelete, commandStatusSMS, billingPeriodDay, billingPeriodTime,
identifySerialNum)
VALUES (21, 822917000001, 1, '1', '79803400001', 1, '?', null, null, null, null, null, null, '2023-12-17 16:02:58',
'2023-12-17 16:02:58', 0, 0, 0, null, null, '?', '01', '00:00:00', 000001);
INSERT INTO u2378356_ledMonitor.modemsInfo (id, serialNum, idCompany, nameModem, phoneNumber, modeDialog,
commandStatusGPRS, region, city, street, structure, latitude, longitude,
timeActivation, timeRegister, idGroup, idIcon, model, keyDelete,
endTimeKeyDelete, commandStatusSMS, billingPeriodDay, billingPeriodTime,
identifySerialNum)
VALUES (22, 822125000000, 1, '2', '79803400000', 1, '?', null, null, null, null, null, null, '2023-12-17 16:04:08',
'2023-12-17 16:04:08', 0, 0, 0, null, null, '?', '01', '00:00:00', 000000);
INSERT INTO u2378356_ledMonitor.modemsInfo (id, serialNum, idCompany, nameModem, phoneNumber, modeDialog,
commandStatusGPRS, region, city, street, structure, latitude, longitude,
timeActivation, timeRegister, idGroup, idIcon, model, keyDelete,
endTimeKeyDelete, commandStatusSMS, billingPeriodDay, billingPeriodTime,
identifySerialNum)
VALUES (44, 111111111111, 1, '3', '79802410000', 1, '?', null, null, null, null, null, null, '2023-12-17 21:21:57',
'2023-12-17 21:20:37', 0, 0, 0, null, null, '?', '01', '00:00:00', 111111);
INSERT INTO u2378356_ledMonitor.modemsInfo (id, serialNum, idCompany, nameModem, phoneNumber, modeDialog,
commandStatusGPRS, region, city, street, structure, latitude, longitude,
timeActivation, timeRegister, idGroup, idIcon, model, keyDelete,
endTimeKeyDelete, commandStatusSMS, billingPeriodDay, billingPeriodTime,
identifySerialNum)
VALUES (45, null, 1, '4', '71112111111', 1, '?', null, null, null, null, null, null, null, '2023-12-17 22:16:13', 0, 0,
0, null, null, '?', '01', '00:00:00', 112211);
INSERT INTO u2378356_ledMonitor.modemsInfo (id, serialNum, idCompany, nameModem, phoneNumber, modeDialog,
commandStatusGPRS, region, city, street, structure, latitude, longitude,
timeActivation, timeRegister, idGroup, idIcon, model, keyDelete,
endTimeKeyDelete, commandStatusSMS, billingPeriodDay, billingPeriodTime,
identifySerialNum)
VALUES (46, null, 1, '5', '73333333333', 0, '?', null, null, null, null, null, null, null, '2023-12-17 22:16:39', 0, 0,
0, null, null, '?', '01', '00:00:00', 333333);
INSERT INTO u2378356_ledMonitor.modemsInfo (id, serialNum, idCompany, nameModem, phoneNumber, modeDialog,
commandStatusGPRS, region, city, street, structure, latitude, longitude,
timeActivation, timeRegister, idGroup, idIcon, model, keyDelete,
endTimeKeyDelete, commandStatusSMS, billingPeriodDay, billingPeriodTime,
identifySerialNum)
VALUES (47, null, 3, '6', '71333333333', 1, '?', null, null, null, null, null, null, null, '2023-12-17 22:16:57', 0, 0,
0, null, null, '?', '01', '00:00:00', 121312);
INSERT INTO u2378356_ledMonitor.modemsInfo (id, serialNum, idCompany, nameModem, phoneNumber, modeDialog,
commandStatusGPRS, region, city, street, structure, latitude, longitude,
timeActivation, timeRegister, idGroup, idIcon, model, keyDelete,
endTimeKeyDelete, commandStatusSMS, billingPeriodDay, billingPeriodTime,
identifySerialNum)
VALUES (49, null, 1, '8', '73332223333', 1, '?', null, null, null, null, null, null, null, '2023-12-17 22:17:50', 16, 0,
0, null, null, '?', '01', '00:00:00', 212211);
INSERT INTO u2378356_ledMonitor.modemsInfo (id, serialNum, idCompany, nameModem, phoneNumber, modeDialog,
commandStatusGPRS, region, city, street, structure, latitude, longitude,
timeActivation, timeRegister, idGroup, idIcon, model, keyDelete,
endTimeKeyDelete, commandStatusSMS, billingPeriodDay, billingPeriodTime,
identifySerialNum)
VALUES (50, null, 4, '9', '79802412222', 1, '?', null, null, null, null, null, null, null, '2023-12-17 22:18:14', 17, 0,
0, null, null, '?', '01', '00:00:00', 112312);
INSERT INTO u2378356_ledMonitor.modemsInfo (id, serialNum, idCompany, nameModem, phoneNumber, modeDialog,
commandStatusGPRS, region, city, street, structure, latitude, longitude,
timeActivation, timeRegister, idGroup, idIcon, model, keyDelete,
endTimeKeyDelete, commandStatusSMS, billingPeriodDay, billingPeriodTime,
identifySerialNum)
VALUES (51, null, 1, '10', '71231231331', 1, '?', null, null, null, null, null, null, null, '2023-12-17 22:18:29', 24,
0, 0, null, null, '?', '01', '00:00:00', 312312);
INSERT INTO u2378356_ledMonitor.modemsInfo (id, serialNum, idCompany, nameModem, phoneNumber, modeDialog,
commandStatusGPRS, region, city, street, structure, latitude, longitude,
timeActivation, timeRegister, idGroup, idIcon, model, keyDelete,
endTimeKeyDelete, commandStatusSMS, billingPeriodDay, billingPeriodTime,
identifySerialNum)
VALUES (52, null, 1, '11', '71231333331', 1, '?', null, null, null, null, null, null, null, '2023-12-17 22:18:50', 0, 0,
0, null, null, '?', '01', '00:00:00', 123123);
Результат выборки:
Таблицы:
create table if not exists data
(
id bigint unsigned auto_increment
primary key,
md5receipt char(32) not null,
serialNum bigint unsigned not null,
mode tinyint unsigned not null,
Ub mediumint unsigned not null,
Ib mediumint unsigned not null,
Up mediumint unsigned not null,
IL mediumint unsigned not null,
Ta tinyint not null,
Tc tinyint not null,
tL smallint unsigned not null,
tN smallint unsigned not null,
PL tinyint unsigned not null,
Hand tinyint(1) not null,
Fire tinyint(1) not null,
Night tinyint(1) not null,
`Load` tinyint(1) not null,
Disch tinyint(1) not null,
OverIL tinyint(1) not null,
ShortL tinyint(1) not null,
ip int unsigned not null,
typeReceipt tinyint(1) not null ,
timeReceipt timestamp default CURRENT_TIMESTAMP not null,
constraint md5receipt
unique (md5receipt)
);
create index DsNtR
on data (serialNum, typeReceipt);
create table if not exists dataResponse
(
id bigint unsigned auto_increment
primary key,
md5receipt char(32) not null,
serialNum bigint unsigned not null,
text tinytext not null,
ip int unsigned not null,
commandResponse char not null,
timeReceipt timestamp default CURRENT_TIMESTAMP not null,
typeReceipt tinyint(1) not null,
constraint md5receipt
unique (md5receipt)
)
comment 'ответы устройств';
create index DRsNtR
on dataResponse (serialNum, typeReceipt);
create table if not exists modemsInfo
(
id bigint unsigned auto_increment
primary key,
serialNum bigint(12) unsigned zerofill null,
idCompany bigint unsigned not null,
nameModem varchar(20) not null,
phoneNumber char(11) not null,
modeDialog tinyint(1) default 1 not null,
commandStatusGPRS char default '?' not null,
region tinytext null,
city tinytext null,
street tinytext null,
structure tinytext null,
latitude char(11) null,
longitude char(11) null,
timeActivation timestamp null,
timeRegister timestamp default CURRENT_TIMESTAMP not null,
idGroup bigint unsigned default 0 not null,
idIcon tinyint unsigned default 0 not null,
model tinyint(1) not null,
keyDelete char(32) null,
endTimeKeyDelete timestamp null,
commandStatusSMS char default '?' not null,
billingPeriodDay char(2) default '01' not null,
billingPeriodTime time default '00:00:00' not null,
identifySerialNum mediumint(6) unsigned zerofill null,
constraint serialNum
unique (serialNum)
);
create index MIsNmD
on modemsInfo (serialNum, modeDialog);
create index idCompany_2
on modemsInfo (idCompany);
create index idGroup
on modemsInfo (idGroup);
EXPLAIN запроса. Не до конца понял всех тонкостей таблицы которой он дает. Много значений которые по словам авторов плохие(Using where; Using filesort, filtered 100% итд...):
И сам запрос.
SELECT mI.idGroup, mI.id as idModem , mI.commandStatusGPRS,
mI.nameModem, mI.commandStatusSMS,
mI.phoneNumber, mI.region, mI.city ,mI.street, mI.serialNum,
mI.timeActivation as timeActivation, mI.structure, mI.model,
mI.modeDialog,d.timeReceipt as timeReceiptData ,
dR.timeReceipt as timeReceiptResponse , d.Hand, d.PL,
d.`Load`, d.Mode, d.Disch, d.OverIL, d.ShortL, d.typeReceipt
FROM modemsInfo mI
left outer join data d on d.id = (SELECT MAX(da.id)
FROM data da
WHERE mI.serialNum = da.serialNum
AND mI.modeDialog=da.typeReceipt)
left outer join dataResponse dR on dR.id = (SELECT MAX(dR.id)
FROM dataResponse dR
WHERE mI.serialNum =
dR.serialNum
AND mI.modeDialog =
dR.typeReceipt)
WHERE mI.idCompany = :idCompany
order by mI.idGroup DESC
Структура таблиц, запрос текстом, текстовые запросы создания таблиц и любую другую инфу доброшу, если нужно.
Ответы (1 шт):
Автор решения: Дима Й
→ Ссылка
Составил такой запрос по шаблону из комментариев.
SELECT mI.idGroup, mI.id as idModem , mI.commandStatusGPRS, mI.nameModem, mI.commandStatusSMS,
mI.phoneNumber, mI.region, mI.city ,mI.street, mI.serialNum,
mI.timeActivation as timeActivation, mI.structure, mI.model, mI.modeDialog,
d.timeReceipt as timeReceiptData , dR.timeReceipt as timeReceiptResponse , d.Hand, d.PL, d.`Load`, d.Mode, d.Disch,
d.OverIL, d.ShortL, d.typeReceipt
FROM modemsInfo mI
LEFT JOIN (data d JOIN (SELECT MAX(id) as id, serialNum, typeReceipt FROM data d2 GROUP BY serialNum, typeReceipt) t21 ON d.id = t21.id)
ON mI.serialNum = d.serialNum AND mI.modeDialog = d.typeReceipt
LEFT JOIN (dataResponse dR JOIN (SELECT MAX(id) as id,serialNum, typeReceipt FROM dataResponse dR2 GROUP BY serialNum, typeReceipt) t31 ON dR.id = t31.id)
ON mI.serialNum = dR.serialNum AND mI.modeDialog = dR.typeReceipt
WHERE mI.idCompany = :idCompany
order by mI.idGroup DESC
Скорость выполнения уменьшилась с 1.4с до ~0.0016c
Шаблон:SELECT .. FROM t1 LEFT JOIN (t2 JOIN (SELECT MAX(id) FROM t2 GROUP BY .. ) t21) ON .. LEFT JOIN (t3 JOIN (SELECT MAX(id) FROM t3 GROUP BY .. ) t31) ON ..