Vai var ātrāku Select'u izveidot?

[Sasa]

No visiem 4739 ierakstiem ar query :

SELECT
hwe2_products.hansaCode AS HansaCode,
hwe2_products.decorName AS DecorName,
hwe2_products.`length` AS SheetL,      
hwe2_products.hansaGroupCode AS hgc,
hwe2_products.stock AS Stock,
hwe2_products.webDescr AS WebDescr,
GROUP_CONCAT(hwe2_classifications.classID ) AS Classification        
FROM
hwe2_product_classification
Left Join hwe2_classifications ON hwe2_product_classification.classificationClassID = hwe2_classifications.classID
Left Join hwe2_products ON hwe2_product_classification.productHansaCode = hwe2_products.hansaCode
WHERE
hwe2_products.publish = 1      
GROUP BY hwe2_products.hansaCode
HAVING
FIND_IN_SET('0162', Classification)
AND
FIND_IN_SET('3202', Classification)

 

tiek atlasīti tikai 2 ieraksti un tiek patērēts ~ 0.671 sec. vai tas ir normas robežās?

 

edit:

kā man šķiet uz doto mirkli bremze ir iekš FIND_IN_SET , bet kā bez tā varētu iztikt neesmu izdomājis šis ir mans pagaidām vienīgais variants kā dabūt tos datus kuri man ir vajadzīgi.

Edited December 2, 2011 by Sasa

[marrtins]

Var, bet šobrīd jau slinkums domāt. Index uz hwe2_classifications.classID ir? Iedosi pieeju phpmyadmin, uztaisīšu ātrāk.

[Sasa]

Jābūt jā Index šādi izveidoju tabulu:

CREATE TABLE IF NOT EXISTS `hwe2_product_classification` (
 `productHansaCode` varchar(20) collate utf8_unicode_ci NOT NULL,
 `classificationClassID` varchar(10) collate utf8_unicode_ci NOT NULL,
 PRIMARY KEY  (`productHansaCode`,`classificationClassID`),
 KEY `productHansaCode` (`productHansaCode`),
 KEY `classificationClassID` (`classificationClassID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Edited December 2, 2011 by Sasa

[NBS]

Es tavā vietā pildītu atsevišķi un neizmantotu FIND_IN_SET, jo tas strādā principā tāpat kā IN un NOT IN,

kas vispār ir ļoti slikti un ilgi visos griezumos.

[Sasa]

Kā tas ir pildītu atsevišķi?

 

edit:

Kāda tad var būt alternatīva FIND_IN_SET() ?

Edited December 5, 2011 by Sasa

[Kaklz]

Atsevišķa tabula, kurā ir konkrētam ierakstam visi piesaistītie kopas elementi. Bet vispār, ja gribi lai kāds tavā vietā DB kaut ko optimizē, tad dod create sintaksi visām tabulām (redzu, ka esi iedevis tikai vienai), dod piemēra datus un SQL kuru gribi optimizēt .

 

P.S pieprasījumam 0.6 sekundes uz nepilniem 5000 ierakstiem ir drausmīgi lēni.

[Aleksejs]

Būtu vēlams redzēt izpildes plānu:

EXPLAIN TAVS_SQLs

[NBS]

Atbilde uz jautājumu, ja pareizi izveido tabulas un struktūru nav jāizmanto FIND_IN_SET().

Kā jau lietotāji pieprasa, iedod vairāk informācijas.

Jo tas ir slimi, ka uz tik mazs ierakstiem tik ilgi pildās.

Tautas skaitīšanā max. pieļaujamais bija 2min.

Tā kā, vari salīdzināt ierakstu skaitu ~75 000 000(TS) vai ~ 5 000(tavas tabulu ieraksti).

[Sasa]

sagatavošu piemēra datus, jo vai manas zināšanas iekš SQL nav pietiekošas vai kas, bet FIND_IN_SET vienīgais deva man vēlamo rezultātu.

[Kavacky]

Izdari, ko teica Aleksejs.

[Sasa]

Explains querijam kas pirmajā postā,

d select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE hwe2_products ALL PRIMARY,hansaCode NULL NULL NULL 9032 "Using where; Using filesort"
1 SIMPLE hwe2_product_classification ref PRIMARY,productHansaCode productHansaCode 62 attelsr_web.hwe2_products.hansaCode 1
1 SIMPLE hwe2_classifications eq_ref PRIMARY,classID PRIMARY 32 attelsr_web.hwe2_product_classification.classificationClassID 1 "Using index"

 

kā iztulkot explainu?

Edited December 6, 2011 by Sasa

[Kaklz]

Beidzot saņēmos un sāku iedziļināties tajā info, ko tu te esi iedevis.

 

1) ja tu liec indeksus, tad lūdzu vismaz iepazīsties ar pašiem indeksu pamatiem. 3 indeksi uz tabulu, kurai ir 2 lauki ir stipri par daudz. Paskaties kaut vai uz to tabulu caur phpMyAdmin un palasi to, kas rādās ar sarkaniem burtiem uz dzeltena fona :). Ja tu tā indeksus esi salicis visur, tad ir traki :). Neliela piezīme - indeksi arī aizņem vietu uz diska un palēnina datu ievietošanu tabulās, kā rezultātā saliekot daudz nejēdzīgus indeksus tu vari jūtami nobremzēt DB pat pie nelielas slodzes.

 

2) Atlasīt datus no tabulas tikai pēc kritērija "where published=1" ir auzas jebkurā gadījumā, jo MySQL indeksi šādā gadījumā ir bezjēdzīgi (paskaties beigās pievienoto prezentāciju un cerams pamanīsi par kuru vietu es runāju). Tikpat bezjēdzīgs ir pieprasījums, ja indeksa nav - mysql ir jāskrien cauri visai tabulai un jāmeklē konkrētie ieraksti ar konkrēto vērtību.

 

3) tavs risinājums ar group by + having meklēšana ar group_concat uzbūvētājā laukā nozīmē, ka mysql katru reizi būs jāiet cauri pilnīgi visiem ierakstiem tabulā un jāmeklē tavi interesējošie kopas elementi katrā no ierakstiem. Ja tu teici, ka tev tie ir 5k, tad tās ir 5k grupēšanas operācijas + pēc tam katrā no grupām vēl 2 meklēšanas. Un tas ir lēni.

 

4) man nav skaidra tava loģika, kas slēpjas zem šiem pieprasījumiem. tu taisi LEFT JOIN, kas nozīmē, ka tev ir vienalga, ka kādam klasifikatoram varētu nebūt pretī produkts? Tad kāpēc praktiski vienīgais, ko tu mēģini dabūt ir ar produktu saistītā informācija?

 

5) Nemēģini vienmēr visu izdarīt vienā milzīgā SQL pieprasījumā. Sadali pa daļām. Tavā gadījumā man izskatās, ka varētu darīt šādi:

 

Dabūjam listi ar produktiem, kuriem klasifikatora klases id ir abi definētie:

SELECT productHansaCode, COUNT( * ) AS cnt
FROM  `hwe2_product_classification`
WHERE  `classificationClassID` in ("0161", "3202") GROUP BY productHansaCode HAVING cnt>1;

 

savācam rezultātu kaut vai uz PHP un pēc tam uzbūvējam vēlvienu SQL pieprasījumu, kurā izmantojam vairs tikai productHansaCode, kuram visticamāk ir primārā atslēga galvenajā tabulā:

 

SELECT
       hwe2_products.hansaCode AS HansaCode,
       hwe2_products.decorName AS DecorName,
       hwe2_products.`length` AS SheetL,      
       hwe2_products.hansaGroupCode AS hgc,
       hwe2_products.stock AS Stock,
       hwe2_products.webDescr AS WebDescr,    
FROM
       hwe2_products
WHERE
       hwe2_products.hansaCode IN ([iEPRIEKŠ IEGŪTĀ LISTE])
       AND published=1

Rezultātā optimizācijas (ja tās vispār ir vajadzīgas) tev ir jāveic praktiski tikai 1. SQL, lai to dabūtu maksimāli ātru, jo otrajā jau viss ir forši - viss notiek pēc primārās atslēgas.

 

Tā kā nav nekāda sīkāka info, tad laba daļa no tā visa ir minējums. Ja dikti gribas iespringt, tad abus manus SQL pieprasījumus var apvienot vienā izmantojot subselect.

 

6) vai tiešām klasifikatori visi ir tekstuālie mainīgie un arī tabulām primārās atslēgas tu liec kā mainīga garuma teksta laukus? 20 simbolus gari varchar lauki db aizņem 5x vairāk vietas, nekā integer tipa lauks. Atkarībā no tabulas tipa MyISAM / InnoDB tur vēl ir vairākas nianses, kāpēc tas ir slikti no ātrdarbības viedokļa.

 

Paskati kaut vai šo:

http://www.slideshar...sql-performance

[Sasa]

ieliku db eksportu http://failiem.lv/u/sdlzsng . Kaklz, paldies, izlasīšu apsmadzeņošu un noteikti pie kaut kāda secinājuma nonākšu.

 

edit:

1. vai man vajadzētu pārsturkturizēt tabulu `hwe2_product_classification` un visas produkta klasifikācijas salikt vienā rindā atdalītas ar komatiem?

 

2. LEFT JOIN'a jā laikam kaut kāda tiešām zapte sanākusi :(

 

3. par 6) punktu dati nāk no ārējas sistēmas un tur tā ir kā var gan burtus, gan skaitļus tajā laukā vadīt, tāpēc tā ir neko nepadarīsi. 20 simbolus gari varchari.

Edited December 8, 2011 by Sasa

[Kaklz]

http://webtech.lv/temp/hwe2/

 

Ir ok manuprāt :)

 

1) ja tu dari tādu mahināciju tad tikpat labi tu to lauku tad vari ielikt arī produktu tabulā - nebūs vairs nepieciešamas grupēšanas, kas ir viena no šaurajām vietām

 

3) tas tev neliedz pie importa ieviest savas primārās atslēgas, kas ir balstītas uz integeriem :)

[Sasa]

Tagad mēģinu iespringt un salikt vienā selectā ar subselectu (programmai vienai tā vajag, jo tur nav tādas iespējas pa daļām sadalīt, vai arī vēl neesmu izkodis), pagaidām bez panākumiem. Bet tā tiešām paldies :)

 

edit:

būs sanācis:

SELECT
hwe2_products.hansaCode,
hwe2_products.hansaName,
group_concat(hwe2_product_classification.classificationClassId)
FROM
hwe2_products join hwe2_product_classification on hwe2_products.hansaCode = hwe2_product_classification.productHansaCode
WHERE 
classificationClassID in ("0162", "3202")
AND publish=1
GROUP BY
hwe2_products.hansaCode HAVING COUNT( * ) > 1

 

tiesa gan nesanāk izvadīt visas klasifikācijas bet tas arī šobrīd nav vajadzīgs.

Edited December 9, 2011 by Sasa