Jump to content
php.lv forumi

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


Sasa

Recommended Posts

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

Link to comment
Share on other sites

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 by Sasa
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...