I have this query into MYSQL, I have indexes everywhere, but answer from DB I have in 0.1 - 0.15 sec and it's too much because I need to have more queries there and to have 0.5 sec of total execution is too much. I know that trouble will be WHERE IN, but how to speed it up when I have only that datas? Table bases is about 30 000 lines.
SELECT
sklad_dodavatelu.Mnozstvi_Skladem as PocetSklademUdodavatele,
bases.Objednaci_nazev,
bases.Id_Pohoda,
bases.Kod,
bases.Dodani,
bases.StavSkladem,
bases.StavObjednano,
bases.Stav_obj_dod,
bases.StavSkladem - bases.StavObjednano as Count_Free,
bases.Nazev,
bases.Dodavatel,
vyrobci_web.UsualDeliveryTime,
bases.Predpoklad_Doruceni,
bases.NaseNaskladneni,
bases.CenaInternet,
bases.Akce,
bases.Text,
druhy_zbozi.Vip,
druhy_zbozi.Druh,
pictures.Url,
pictures.Popis,
pictures.Barva,
rel_zasoby.Kod_hlavicka,
sklad_dodavatelu.Naskladneni,
hlavicky.Nazev as NazevHlavicky,
sklad_dodavatelu.IdDodavatele,
(SELECT Datum
FROM ppl
order by Datum desc
limit 1) as PosledniPPL,
adb.MaxOdeslaniZbozi,
adb.OcekavaneZboziSkladem,
(SELECT Hodnota
from konstanty
where nazev = 'MaxPPL'
limit 1) as KonstantaPPL
from
bases
LEFT JOIN sklad_dodavatelu
on bases.Objednaci_nazev = sklad_dodavatelu.Id_Produktu
LEFT JOIN rel_zasoby
on bases.Id_Pohoda = rel_zasoby.Kod_Pohoda
LEFT JOIN hlavicky
on rel_zasoby.Kod_hlavicka = hlavicky.Id_Pohoda
LEFT JOIN vyrobci_web
on hlavicky.Vyrobce = vyrobci_web.Nazev
LEFT JOIN hlavicky_druhy
on rel_zasoby.Kod_hlavicka = hlavicky_druhy.Id_hlavicky
LEFT JOIN druhy_zbozi
on hlavicky_druhy.Id_druh = druhy_zbozi.Id
LEFT join pictures
on bases.Kod = pictures.Kod
LEFT join adb
on bases.Dodavatel = adb.adb_number_ids
where
bases.Id_Pohoda in
( '42735', '13979', '43069', '61283', '64303', '61271', '56462',
'41507', '31348', '60963', '62136', '69403', '43071', '13828',
'43073', '61281', '64310', '64306', '64307', '71453', '64514',
'79025', '79026', '44797', '77095', '77094', '55214', '76804',
'76806', '76805', '35490', '16432', '15072', '10416', '15101',
'7453', '13359', '16539', '16540', '34044', '44817', '61523',
'45969', '45963', '71065', '73430', '73425', '73427', '73426',
'73424', '73428', '73429', '73423', '30978', '32959', '33171',
'30918', '33233', '66308', '66309', '44795', '55251', '76074',
'76073', '67208', '67210', '67211', '49056', '49055', '49053',
'49048', '49054', '49047', '49061', '49062', '49063', '49064',
'49066', '49067', '49068', '49070', '55390', '55391', '55392',
'55393', '55394', '62580', '62581', '62257', '60674', '80803',
'56539', '79064', '79065', '79063', '79066', '81772', '81774',
'81773', '81777', '52901', '52902', '52900', '52898', '60345',
'54549', '76095', '76096', '76097', '67227', '67226', '67228',
'53419', '53418', '73433', '73438', '73435', '73434', '73431',
'73432', '73436', '73437', '72722', '72713', '56254', '56255',
'56256', '56257', '56258', '56259', '56260', '56261', '56246',
'56247', '56248', '56249', '56250', '56251', '56252', '56253',
'56292', '69042', '69044', '69046', '69048', '69049', '57227',
'57230', '57231', '57232', '57233', '57228', '57229', '57525',
'62260', '62261', '80481', '80482', '80483', '80478', '80479',
'80484', '80480', '57518', '57519', '57517', '57220', '57222',
'58632', '58630', '58631', '58628', '58637', '58634', '58636',
'58635', '58633', '58647', '58644', '58646', '58645', '58643',
'58642', '58639', '58641', '58640', '58638', '60459', '60938',
'60942', '60944', '60945', '77608', '60787', '60708', '60709',
'62550', '76619', '76620', '76618', '60405', '60404', '66974',
'80598', '80596', '80594', '80595', '80597', '60978', '60979',
'60980', '60981', '60982', '60983', '60985', '60988', '60989',
'60966', '60967', '60968', '60969', '60990', '60970', '60971',
'60972', '60973', '60974', '60984', '61588', '61503', '61504',
'55006', '55008', '55009', '55010', '55011', '55012', '55013',
'55014', '55015', '55016', '55017', '55018', '61630', '61631',
'61629', '61632', '61628', '61633', '61627', '61634', '61626',
'61635', '61625', '61636', '61624', '61637', '61623', '61638',
'61622', '61639', '61620', '61640', '61593', '61608', '61606',
'61609', '61605', '61610', '61604', '61611', '61603', '61612',
'61602', '61613', '61601', '61614', '61600', '61615', '61599',
'61616', '61598', '61617', '61597', '61596', '61619', '61618',
'61546', '61545', '61544', '61543', '61542', '61541', '61540',
'61539', '61538', '61534', '61679', '61677', '61675', '67070',
'67069', '61681', '62041', '62037', '62036', '62040', '62038',
'62042', '69272', '69274', '69275', '81633', '81632', '81631',
'76570', '76571', '76569', '76572', '76573', '62340', '62342',
'62338', '62341', '62343', '62339', '70652', '70653', '70651',
'64120', '64121', '64122', '64123', '64124', '64221', '64220',
'64224', '64225', '64226', '64761', '64756', '76411', '76412',
'76410', '76409', '79009', '79008', '79007', '79010', '79014',
'79015', '79013', '79018', '79016', '79017', '79011', '79012',
'71449', '66480', '66481', '66482', '66483', '66484', '66486',
'73209', '79253', '79254', '66542', '66538', '66539', '66536',
'66540', '74840', '79255', '79256', '66599', '72718', '72719',
'73459', '73457', '77404', '66709', '66710', '66820', '66819',
'66770', '66578', '67216', '67111', '67112', '67113', '67114',
'67120', '67075', '67559', '73003', '68661', '81115', '81114',
'68662', '81113', '81112', '67396', '67397', '67399', '67400',
'67401', '72870', '72871', '67381', '67382', '67384', '67385',
'67386', '72934', '72935', '67375', '67377', '67376', '67378',
'67379', '67380', '72933', '72932', '67394', '67393', '67391',
'67390', '67395', '72869', '72868', '68986', '68987', '68988',
'76623', '68982', '68983', '76622', '76621', '69434', '74890',
'64108', '70697', '70699', '70706', '70760', '70759', '70758',
'70757', '70756', '70753', '70754', '70755', '80647', '80649',
'80650', '80651', '80648', '70587', '81085', '81086', '71494',
'81088', '81087', '72658', '72659', '72666', '72660', '72661',
'72664', '72667', '72665', '76537', '77610', '77612', '77611',
'77613', '77615', '77614', '81634', '81891', '81892', '81893',
'81895', '81894', '81896', '67115', '72957', '67072', '67562',
'73112', '73113', '73255', '73254', '73252', '73250', '73251',
'73253', '73261', '73260', '73258', '73256', '73257', '73259',
'73224', '77555', '77552', '77556', '77553', '77554', '74760',
'74759', '74761', '74762', '80952', '74822', '74821', '74823',
'74825', '74826', '76485', '74903', '76076', '76075', '76105',
'76106', '76103', '76104', '76089', '76090', '76091', '76092',
'76381', '76379', '76380', '76624', '76625', '76626', '80870',
'80869', '80868', '80867', '76941', '76940', '76944', '76943',
'76942', '76463', '76464', '76465', '76466', '76467', '76468',
'76950', '76951', '76952', '76948', '76949', '80766', '80767',
'80768', '80764', '80765', '76997', '76947', '76945', '76946',
'81201', '81202', '79019', '79023', '79021', '79024', '79020',
'79022', '76715', '76716', '76717', '76719', '76718', '81816',
'81817', '81818', '81755', '81756', '77093', '78952', '78950',
'78947', '78949', '78953', '78951', '78948', '79068', '79067',
'79069', '77002', '79143', '79153', '79154', '79140', '79141',
'79142', '79155', '79080', '79139', '79148', '79157', '79144',
'79159', '79158', '79145', '79146', '79147', '79135', '79152',
'79160', '79162', '79161', '79149', '79151', '79150', '79136',
'79137', '79004', '79005', '79006', '77887', '77884', '77885',
'77886', '80774', '74713', '74714', '80459', '80455', '80458',
'76486', '81007', '81008', '81523', '81525', '80580', '76237',
'77104', '77004', '80563', '80561', '80562', '80568', '80569',
'80567', '80698', '80704', '80699', '80703', '80697', '80700',
'80706', '80705', '81277', '79176', '79177', '79178', '79183',
'79184', '79185', '80749', '80739', '80751', '80736', '80737',
'80735', '81321', '81834', '80831', '79277', '79275', '79287',
'79288', '79276', '79274', '79250', '79289', '79286', '79263',
'79258', '79257', '79264', '79260', '79262', '79259', '79261',
'79234', '79269', '79273', '79272', '79270', '79265', '79268',
'74842', '79267', '79271', '79295', '79296', '80858', '79293',
'79290', '79292', '79297', '79291', '79294', '80928', '80930',
'80929', '80931', '81016', '81076', '81074', '81567', '81565',
'81566', '81570', '81569', '81568', '81238', '81240', '81239',
'67407', '81249', '81315', '81319', '81320', '81314', '81316',
'81247', '81248', '81551', '81550', '81555', '81554', '81552',
'80755', '80756', '80757', '80758', '80759', '80760', '81776',
'81775', '81806', '81808', '81823', '81822', '81821', '81820',
'81786', '81792', '81789', '81795', '81790', '81791', '81793',
'81794', '81787', '81785', '81784', '81788', '82018', '82019',
'82020', '81761', '81764', '77595', '81766', '81767', '81760',
'82213', '82214', '82206', '82207', '82243', '82247', '82245',
'82248', '82246')
order by
bases.Nazev,
bases.Id_Pohoda
CodePudding user response：
After cleaning up your query for readability and what others may not have seen because of it, there are many things to consider.
First, your IN clause has almost 800 IDs. How did you come up with all these IDs. IF it was from a query of some condition AND you built the string query out, that is a problem. You would be best to change to something like
SELECT
all, your, columns
from
( select theId
from YourTable
where YourCondition ) PreQuery
JOIN Bases b
ON PreQuery.theId = bases.IdColumn
Also, if your "ID" values are actually numeric values (integer), dont wrap 'quotes' around them. They are numbers, no need to interpret numbers from a string, leave them as numbers WITHOUT quotes.
Next, you have two column-based queries which are not correlated to any specific base or other child table. This is doing the query every time against ALL records. I would add this to the JOIN list of tables since it is always returning a single row anyhow and just grab it. However, if it IS supposed to be associated WITH a specific ID of some other context, that needs to be indicated/adjusted.
SELECT
all, your, columns,
Posledni.Datum as PosledniPPL,
Konstanta.Hodnota as KonstantaPPL
from
bases
JOIN (all your other joins)
JOIN (SELECT Datum
FROM ppl
order by Datum desc
limit 1) as Posledni
JOIN (SELECT Hodnota
from konstanty
where nazev = 'MaxPPL'
limit 1) as Konstanta
Then on to indexes. Having indexes on just a single column key does not help as much as a multi-column / covering helping, especially when joining nested levels. Following suggested indexes
table index
bases ( Id_Pohoda, Nazev, Objednaci_nazev, Kod, Dodavatel )
sklad_dodavatelu ( Id_Produktu )
rel_zasoby ( Kod_Pohoda, Kod_hlavicka )
hlavicky ( Id_Pohoda, Vyrobce )
vyrobci_web ( Nazev )
hlavicky_druhy ( Id_hlavicky, Id_druh )
druhy_zbozi ( Id )
pictures ( Kod )
adb ( adb_number_ids )
Finally, as you write queries, and especially when getting into using the same table in the same query multiple times for alternate lookup purposes, using aliases vs long table names can help simplify vs overtyping. So, without retyping your ENTIRE query, consider the following using aliases.
SELECT
s.Mnozstvi_Skladem as PocetSklademUdodavatele,
b.Objednaci_nazev,
b.Id_Pohoda,
b.Kod,
b.StavSkladem - b.StavObjednano as Count_Free,
vw.UsualDeliveryTime,
b.Predpoklad_Doruceni,
dz.Vip,
dz.Druh,
p.Url,
p.Popis,
r.Kod_hlavicka,
s.Naskladneni,
h.Nazev as NazevHlavicky
from
bases b
LEFT JOIN sklad_dodavatelu s
on b.Objednaci_nazev = s.Id_Produktu
LEFT JOIN rel_zasoby r
on b.Id_Pohoda = r.Kod_Pohoda
LEFT JOIN hlavicky h
on r.Kod_hlavicka = h.Id_Pohoda
LEFT JOIN vyrobci_web vw
on h.Vyrobce = vw.Nazev
LEFT JOIN hlavicky_druhy hd
on r.Kod_hlavicka = hd.Id_hlavicky
LEFT JOIN druhy_zbozi dz
on hd.Id_druh = dz.Id
LEFT join pictures p
on b.Kod = p.Kod
LEFT join adb
on b.Dodavatel = adb.adb_number_ids
where
b.Id_Pohoda in ( your list of 800 IDs)
order by
b.Nazev,
b.Id_Pohoda
CodePudding user response：
What I would suggest is adding the following composite indexes on the tables then execute the query and run explain again :
ALTER TABLE bases ADD INDEX `idx_1` (`Objednaci_nazev`,`Id_Pohoda`,`Kod`,`Dodavatel`);
ALTER TABLE rel_zasoby ADD INDEX `idx_2` (`Kod_Pohoda`,`Kod_hlavicka`);
ALTER TABLE hlavicky ADD INDEX `idx_3` (`Id_Pohoda`,`Vyrobce`);
ALTER TABLE hlavicky_druhy ADD INDEX `idx_4` (`Id_hlavicky`,`Id_druh`);