Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2014
    Posts
    2

    Unanswered: DERIVED index is causing really slow queries on 9 mil records

    My query is close to 20 min long and the h@ngup appears to be at the DERIVED index.

    I tried to create a temporary table, but when applying it, the query just locks up in a manner that the cli will not except anymore commands and I have to cntl+c to get out of it. http://i.imgur.com/QHkpo59.png

    explain
    SELECT cia.cia_orderamount ,
    cia.cia_notes ,
    cia.cia_mincount ,
    cia.cia_customerid ,
    cia.cia_id ,
    lc.id ,
    lc.locationcode ,
    T1.instock ,
    c.id AS customerid ,
    c.customer
    FROM cardinventoryalerts cia
    INNER JOIN customers c ON cia.cia_customerid = c.id
    AND c.useautocardorder = 1
    INNER JOIN locationcodes lc ON cia.cia_locationid = lc.id
    LEFT JOIN
    (SELECT cb.customer ,
    cb.locationcode ,
    count(cb.id) AS instock
    FROM cardbatch cb
    WHERE cb.issued = 'no'
    AND cb.locationcode <> ''
    AND cb.locationcode IS NOT NULL
    GROUP BY cb.customer,
    cb.locationcode) AS T1 ON lc.locationcode = T1.locationcode
    AND T1.customer = c.customer
    WHERE ((cia_mincount > T1.instock)
    OR (T1.instock IS NULL
    AND cia_mincount>0))
    UNION
    SELECT cia.cia_orderamount ,
    cia.cia_notes ,
    cia.cia_mincount ,
    cia.cia_customerid ,
    cia.cia_id ,
    -1 ,
    NULL ,
    T1.instock ,
    c.id AS customerid ,
    c.customer
    FROM cardinventoryalerts cia
    INNER JOIN customers c ON cia.cia_customerid = c.id
    AND c.useautocardorder = 1
    LEFT JOIN
    (SELECT cb.customer ,
    count(cb.id) AS instock
    FROM cardbatch cb
    WHERE cb.issued = 'no'
    AND (cb.locationcode = ''
    OR cb.locationcode IS NULL)
    GROUP BY cb.customer) AS T1 ON c.customer = T1.customer
    WHERE ((cia_mincount > T1.instock)
    OR (T1.instock IS NULL
    AND cia_mincount>0))
    AND cia.cia_locationid IS NULL
    ORDER BY customer,
    locationcode;



    *************************** temp table attempt ********

    explain
    CREATE TEMPORARY TABLE IF NOT EXISTS
    temp_table ( INDEX(locationcode) )
    AS (
    SELECT customerid, locationcode
    FROM cardbatch
    );
    CREATE TEMPORARY TABLE IF NOT EXISTS
    temp_table2 ( INDEX(cia_locationid) )
    AS (
    SELECT cia_customerid, cia_locationid
    FROM cardinventoryalerts
    )
    SELECT cia.cia_orderamount ,
    cia.cia_notes ,
    cia.cia_mincount ,
    cia.cia_customerid ,
    cia.cia_id ,
    lc.id ,
    lc.locationcode ,
    T1.instock ,
    c.id AS customerid ,
    c.customer
    FROM temp_table2 cia
    INNER JOIN customers c ON cia.cia_customerid = c.id
    AND c.useautocardorder = 1
    INNER JOIN locationcodes lc ON cia.cia_locationid = lc.id
    LEFT JOIN
    (SELECT cb.customer ,
    cb.locationcode ,
    count(cb.id) AS instock
    FROM temp_table cb
    WHERE cb.issued = 'no'
    AND cb.locationcode <> ''
    AND cb.locationcode IS NOT NULL
    GROUP BY cb.customer,
    cb.locationcode) AS T1 ON lc.locationcode = T1.locationcode
    AND T1.customer = c.customer
    WHERE ((cia_mincount > T1.instock)
    OR (T1.instock IS NULL
    AND cia_mincount>0))
    UNION
    SELECT cia.cia_orderamount ,
    cia.cia_notes ,
    cia.cia_mincount ,
    cia.cia_customerid ,
    cia.cia_id ,
    -1 ,
    NULL ,
    T1.instock ,
    c.id AS customerid ,
    c.customer
    FROM cardinventoryalerts cia
    INNER JOIN customers c ON cia.cia_customerid = c.id
    AND c.useautocardorder = 1
    LEFT JOIN
    (SELECT cb.customer ,
    count(cb.id) AS instock
    FROM cardbatch cb
    WHERE cb.issued = 'no'
    AND (cb.locationcode = ''
    OR cb.locationcode IS NULL)
    GROUP BY cb.customer) AS T1 ON c.customer = T1.customer
    WHERE ((cia_mincount > T1.instock)
    OR (T1.instock IS NULL
    AND cia_mincount>0))
    AND cia.cia_locationid IS NULL
    ORDER BY customer,
    locationcode;



    ************************************************** ****

    http://i.imgur.com/VNpEUVx.png

    id select_type table type possible_keys key key_len ref rows Extra
    1 PRIMARY cia ALL fk_ciacustid,fk_cialocationid,customer_loc 11911
    1 PRIMARY c eq_ref PRIMARY,id PRIMARY 4 gcc.cia.cia_customerid 1 Using where
    1 PRIMARY lc eq_ref PRIMARY,id PRIMARY 4 gcc.cia.cia_locationid 1
    1 PRIMARY <derived2> ALL 20843 Using where
    2 DERIVED cb index idx_loc customer_loc 156 9293472 Using where
    3 UNION cia ref fk_ciacustid,fk_cialocationid,customer_loc fk_cialocationid 5 const 21 Using where
    3 UNION c eq_ref PRIMARY,id PRIMARY 4 gcc.cia.cia_customerid 1 Using where
    3 UNION <derived4> ALL 1831 Using where
    4 DERIVED cb ref_or_null idx_loc idx_loc 53 6676342 Using where; Using temporary; Using filesort
    UNION RESULT <union1,3> ALL Using filesort





    ************************************************** **************



    mysql> show tables;
    +---------------------+
    | Tables_in_temp |
    +---------------------+
    | cardbatch |
    | cardinventoryalerts |
    | customers |
    | locationcodes |
    +---------------------+
    4 rows in set (0.00 sec)

    mysql> desc locationcodes;
    +--------------+------------------+------+-----+---------+----------------+
    | Field | Type | Null | Key | Default | Extra |
    +--------------+------------------+------+-----+---------+----------------+
    | id | int(11) unsigned | NO | PRI | NULL | auto_increment |
    | customer | varchar(100) | YES | MUL | NULL | |
    | locationcode | varchar(50) | YES | MUL | NULL | |
    | parentid | int(11) | YES | MUL | NULL | |
    | alias | varchar(100) | YES | | NULL | |
    +--------------+------------------+------+-----+---------+----------------+
    5 rows in set (0.00 sec)

    mysql> desc customers;
    +------------------+------------------+------+-----+---------+----------------+
    | Field | Type | Null | Key | Default | Extra |
    +------------------+------------------+------+-----+---------+----------------+
    | id | int(10) unsigned | NO | PRI | NULL | auto_increment |
    | customer | varchar(100) | YES | UNI | NULL | |
    | address | varchar(100) | NO | | yes | |
    | balance | varchar(20) | YES | | 0 | |
    | useautocardorder | int(11) | YES | | NULL | |
    +------------------+------------------+------+-----+---------+----------------+
    5 rows in set (0.00 sec)

    mysql> desc cardinventoryalerts;
    +-----------------+------------------+------+-----+-------------------+----------------+
    | Field | Type | Null | Key | Default | Extra |
    +-----------------+------------------+------+-----+-------------------+----------------+
    | cia_id | int(11) unsigned | NO | PRI | NULL | auto_increment |
    | cia_customerid | int(11) unsigned | NO | MUL | NULL | |
    | cia_locationid | int(11) unsigned | YES | MUL | NULL | |
    | cia_orderamount | int(11) | YES | | NULL | |
    | cia_mincount | int(11) | YES | | NULL | |
    | cia_created | timestamp | NO | | CURRENT_TIMESTAMP | |
    | cia_createdby | varchar(50) | YES | | NULL | |
    | cia_notes | varchar(500) | YES | | NULL | |
    +-----------------+------------------+------+-----+-------------------+----------------+
    8 rows in set (0.00 sec)

    mysql> desc cardbatch;
    +--------------+------------------+------+-----+---------+----------------+
    | Field | Type | Null | Key | Default | Extra |
    +--------------+------------------+------+-----+---------+----------------+
    | id | int(10) unsigned | NO | PRI | NULL | auto_increment |
    | cardnumber | varchar(100) | YES | UNI | NULL | |
    | issued | varchar(10) | YES | | no | |
    | firstname | varchar(100) | YES | | NULL | |
    | lastname | varchar(100) | YES | | NULL | |
    | customer | varchar(100) | YES | MUL | NULL | |
    | locationcode | varchar(50) | YES | | NULL | |
    +--------------+------------------+------+-----+---------+----------------+
    7 rows in set (0.00 sec)

  2. #2
    Join Date
    Apr 2014
    Posts
    2

    DERIVED index is causing really slow queries on 9 mil records

    repost edit
    Last edited by fulcan; 04-23-14 at 23:50.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •