hoho Posted February 6, 2015 Report Share Posted February 6, 2015 Datubāzē ir 50000 ieraksti, man vajag izvilkt visus datus un vairākus count: SELECT t1.*, (SELECT COUNT(*) FROM t2 WHERE t2.t1_id = t1.id аs а1), (SELECT COUNT(*) FROM t3 WHERE t3.t3_id = t1.id as a2), (SELECT COUNT(*) FROM t4 WHERE t4.t4_id = t1.id as a3) FROM t1 GROUP BY t1.id Bez count strādā ātri, ar count pat nevaru sagaidīt rezultātu, viss uzkārās. Indeksi ir salikti. Kādas iz Count alternatīvas lai izvilkt rezultātu? Quote Link to comment Share on other sites More sharing options...
e-remit Posted February 6, 2015 Report Share Posted February 6, 2015 Tiešām tas SQL pieprasījums ir tāds, kā ieliki postā? Pirmkārt, tas GROUP BY ir lieks, ja pieņem, ka ID ir primārā atslēga. Quote Link to comment Share on other sites More sharing options...
briedis Posted February 6, 2015 Report Share Posted February 6, 2015 "Indeksi ir salikti." Ha! Kāpēc tu domā, ka viņi vispār strādā šajā gadījumā? Domā, ka nospiežot "Index" kolonnai, visi kvēriji maģiski sāks strādāt? Uzraksti EXPLAIN .. pieprasījumu šim kvērijam un tad skaties, kur tiek izmantots, kur nē. Quote Link to comment Share on other sites More sharing options...
marrtins Posted February 6, 2015 Report Share Posted February 6, 2015 Pārliec lai a1,a2,a3 updeitojas tikai pie izmaiņām trigeros. Quote Link to comment Share on other sites More sharing options...
Pieduriens Posted February 6, 2015 Report Share Posted February 6, 2015 Intuitīvi jūtu ka COUNT() nebūtu jāselekto vissa rinda, pietiktu tikai ar ID... SELECT t1.*, - iespējams ka visu * Tev nemaz nevajag.. (SELECT COUNT(*) - iespējams ka visu * Tev nemaz nevajag.. Quote Link to comment Share on other sites More sharing options...
spainis Posted February 6, 2015 Report Share Posted February 6, 2015 Intuitīvi jūtu ka COUNT() nebūtu jāselekto vissa rinda, pietiktu tikai ar ID... SELECT t1.*, - iespējams ka visu * Tev nemaz nevajag.. (SELECT COUNT(*) - iespējams ka visu * Tev nemaz nevajag.. http://www.percona.com/blog/2007/04/10/count-vs-countcol/ Quote Link to comment Share on other sites More sharing options...
Pieduriens Posted February 6, 2015 Report Share Posted February 6, 2015 (edited) Vai tad runa nav par rindām kā tādām, nevis kolonām katrā rindā.. nekur neredzu arī koda piemērā countcol(), bet nu kā jau teicu..tikai minējums. Kā arī, vai tad SELECT * (select all from row) ir nepieciešams pie ierakstu skaitīšanas? Edited February 6, 2015 by Pieduriens Quote Link to comment Share on other sites More sharing options...
spameris Posted March 3, 2015 Report Share Posted March 3, 2015 (edited) Ar MySQL man ir maza pieredze, bet pamatā ja jācīnas par perfomance DB tad jālieto JOIN nevis subquery. uff.. šķet nepareizi uzrakstiju :) ar pirmo reizi, kautkā bez reāliem datiem grūti rakstās, bet nu ideja ir tāda ka SQL optimizatorimem lielākoties patīk JOIN nevis subquery SELECT t1, COUNT(DISTINCT t2.id), COUNT(DISTINCT t3.id), COUNT(DISTINCT t4.id) FROM t1 LEFT JOIN t2 ON t2.t1_id = t1.id LEFT JOIN t2 ON t3.t3_id = t1.id LEFT JOIN t2 ON t4.t4_id = t1.id GROUP BY t1.id ja pieņem ka t2.id etc.. ir primarais kejs. Edited March 3, 2015 by spameris Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.