MySql pašizveidotās funkcijas iekš SELECT'a drausmīgi lēns query p

[Sasa]

Izveidoju savu mysql funkciju ko izsaucu iekš SELECT'a.

Funkcija: http://paste.php.lv/43455fe44f3f281cf82c331a9ef654b3?lang=mysql

Selects kurā izsaucu funkciju:

http://paste.php.lv/27c1056bce5fe55dc6f1b2e18e00e467?lang=mysql

 

izpildīšanās laiks ir ~ 0.5 sec.

 

izņemot funkciju izpildās ~ 0.06 sec.

[NBS]

`in` operātors tev diezgan daudz noslogo šo vaicājumu. Ja ir, iespējams, tik vaļā no tā.

 

Ar cieņu,

NBS.

Edited March 2, 2012 by NBS

[Sasa]

tiku vaļā no `in`.

delimiter $$

CREATE DEFINER=`attelsr_web_adm`@`192.168.0.164` FUNCTION `getSawOperHc`(operationClass VARCHAR(10), thick DECIMAL(8,3)) RETURNS varchar(50) CHARSET utf8
BEGIN
RETURN(
SELECT
hansaCode
FROM
   	(SELECT
       	hansaCode
   	FROM
       	hwe2_products
   	Join hwe2_product_classification ON hwe2_products.hansaCode = hwe2_product_classification.productHansaCode
   	JOIN hwe2_classifications ON hwe2_product_classification.classificationClassID = hwe2_classifications.classID
   	WHERE
       	classificationClassId = operationClass) AS t
       	Join
   	hwe2_product_classification ON t.hansaCode = hwe2_product_classification.productHansaCode
       	JOIN
   	hwe2_classifications ON hwe2_product_classification.classificationClassID = hwe2_classifications.classID
WHERE
   	className = thick
);
END$$

 

paskatījos ja es izpildu funkciju kur ir iselektēti ~ 300 ieraksti tad tas laiks ir ~ 0.5, bet tur kur izselektēti ieraksti ir tikai kādi ~ 20 tur tas izpildes laiks sarūk līdz 0.03

[Aleksejs]

Parādi execution plānu:

EXPLAIN SELECT ...

[Sasa]

EXPLAIN's Selectam kurā ir izmantota funkcija

id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
1    PRIMARY    <derived2>    ALL    NULL    NULL    NULL    NULL    227    "Using filesort"
1    PRIMARY    hwe2_product_classification    ref    PRIMARY,productHansaCode,classificationClassID    productHansaCode    62    lksp.hc    1    
1    PRIMARY    hwe2_classifications    eq_ref    PRIMARY,classID    PRIMARY    32    attelsr_web.hwe2_product_classification.classificationClassID    1    
2    DERIVED    hwe2_product_classification    ref    PRIMARY,productHansaCode,classificationClassID    classificationClassID    32   	 307    "Using where; Using temporary; Using filesort"
2    DERIVED    hwe2_products    eq_ref    PRIMARY,hansaCode    PRIMARY    62    attelsr_web.hwe2_product_classification.productHansaCode    1    "Using where"

 

 

Edited March 2, 2012 by Sasa

[Aleksejs]

hwe2_product_classification ref PRIMARY,productHansaCode,classificationClassID classificationClassID 32 307 "Using where; Using temporary; Using filesort"

 

 

hwe2_product_classification būtu jāpaskatās, kas ar indeksiem

[Sasa]

indeksi (hwe2_product_classification):

[NBS]

tiku vaļā no `in`.

delimiter $$

CREATE DEFINER=`attelsr_web_adm`@`192.168.0.164` FUNCTION `getSawOperHc`(operationClass VARCHAR(10), thick DECIMAL(8,3)) RETURNS varchar(50) CHARSET utf8
BEGIN
RETURN(
SELECT
hansaCode
FROM
   	(SELECT
       	hansaCode
   	FROM
       	hwe2_products
   	Join hwe2_product_classification ON hwe2_products.hansaCode = hwe2_product_classification.productHansaCode
   	JOIN hwe2_classifications ON hwe2_product_classification.classificationClassID = hwe2_classifications.classID
   	WHERE
       	classificationClassId = operationClass) AS t
       	Join
   	hwe2_product_classification ON t.hansaCode = hwe2_product_classification.productHansaCode
       	JOIN
   	hwe2_classifications ON hwe2_product_classification.classificationClassID = hwe2_classifications.classID
WHERE
   	className = thick
);
END$$

 

paskatījos ja es izpildu funkciju kur ir iselektēti ~ 300 ieraksti tad tas laiks ir ~ 0.5, bet tur kur izselektēti ieraksti ir tikai kādi ~ 20 tur tas izpildes laiks sarūk līdz 0.03

 

Nav iespēja iekšējā selektā ielikt to - `className = thick`

Domāju, ka varētu arī palīdzēt.