0

I have a fairly large SELECT query and MySQL stalls while giving me a status of "Sending Data". I don't know what could be the problem, or how to fix any of this. Here is the query:

SELECT DISTINCT
    `global`.`id` AS `global_id`,
    `core_store`.`store_id`,
    `parent`.`id` AS `filter_id`,
    `global`.`option_id`,
    COALESCE(store_option_value.value_id, global_option_value.value_id) AS `value_id`,
    COALESCE(store_option_value.value, global_option_value.value, '') AS `name`,
    `global`.`position`,
    `global`.`color`,
    `global`.`normal_image`,
    `global`.`selected_image`,
    `global`.`normal_hovered_image`,
    `global`.`selected_hovered_image`,
    `global`.`state_image` 
FROM
    `m_filter2_value` AS `global` 
    INNER JOIN
        `core_store` 
    INNER JOIN
        `eav_attribute_option` 
        ON global.option_id = eav_attribute_option.option_id 
    INNER JOIN
        `eav_attribute` 
        ON eav_attribute.attribute_id = eav_attribute_option.attribute_id 
    INNER JOIN
        `m_filter2` AS `parent_global` 
        ON eav_attribute.attribute_code = parent_global.code 
    INNER JOIN
        `m_filter2_store` AS `parent` 
        ON parent_global.id = parent.global_id 
        AND core_store.store_id = parent.store_id 
    LEFT JOIN
        `eav_attribute_option_value` AS `global_option_value` 
        ON global_option_value.option_id = eav_attribute_option.option_id 
        AND global_option_value.store_id = 0 
    LEFT JOIN
        `eav_attribute_option_value` AS `store_option_value` 
        ON store_option_value.option_id = eav_attribute_option.option_id 
        AND store_option_value.store_id = core_store.store_id 
    LEFT JOIN
        `m_filter2_value_store` AS `target` 
        ON target.global_id = global.id 
        AND target.store_id = core_store.store_id 
WHERE
    (
        core_store.store_id <> 0
    )
    AND 
    (
        global.edit_status = 0
    )
    AND 
    (
        target.id IS NULL
    )
    LIMIT 10000;

And here is the EXPLAIN of it:

+----+-------------+----------------------+--------+----------------------------------------------------------------------------------+------------------------------------------+---------+-------------------------------------------------------+---------+--------------------------------------------------------------------------+
| id | select_type | table                | type   | possible_keys                                                                    | key                                      | key_len | ref                                                   | rows    | Extra                                                                    |
+----+-------------+----------------------+--------+----------------------------------------------------------------------------------+------------------------------------------+---------+-------------------------------------------------------+---------+--------------------------------------------------------------------------+
|  1 | SIMPLE      | global               | ref    | option_id,edit_status                                                            | edit_status                              | 8       | const                                                 |     734 | Using temporary                                                          |
|  1 | SIMPLE      | eav_attribute_option | eq_ref | PRIMARY,IDX_EAV_ATTRIBUTE_OPTION_ATTRIBUTE_ID                                    | PRIMARY                                  | 4       | finish_library_emea.global.option_id                  |       1 | NULL                                                                     |
|  1 | SIMPLE      | eav_attribute        | eq_ref | PRIMARY                                                                          | PRIMARY                                  | 2       | finish_library_emea.eav_attribute_option.attribute_id |       1 | Using where                                                              |
|  1 | SIMPLE      | parent_global        | eq_ref | PRIMARY,code                                                                     | code                                     | 767     | finish_library_emea.eav_attribute.attribute_code      |       1 | Using index                                                              |
|  1 | SIMPLE      | global_option_value  | ref    | IDX_EAV_ATTRIBUTE_OPTION_VALUE_OPTION_ID,IDX_EAV_ATTRIBUTE_OPTION_VALUE_STORE_ID | IDX_EAV_ATTRIBUTE_OPTION_VALUE_OPTION_ID | 4       | finish_library_emea.global.option_id                  |       1 | Using where                                                              |
|  1 | SIMPLE      | parent               | ref    | global_id,store_id                                                               | global_id                                | 8       | finish_library_emea.parent_global.id                  |       2 | Using where                                                              |
|  1 | SIMPLE      | core_store           | eq_ref | PRIMARY                                                                          | PRIMARY                                  | 2       | finish_library_emea.parent.store_id                   |       1 | Using index                                                              |
|  1 | SIMPLE      | store_option_value   | ref    | IDX_EAV_ATTRIBUTE_OPTION_VALUE_OPTION_ID,IDX_EAV_ATTRIBUTE_OPTION_VALUE_STORE_ID | IDX_EAV_ATTRIBUTE_OPTION_VALUE_OPTION_ID | 4       | finish_library_emea.global.option_id                  |       1 | Using where                                                              |
|  1 | SIMPLE      | target               | ALL    | NULL                                                                             | NULL                                     | NULL    | NULL                                                  | 1012396 | Using where; Not exists; Distinct; Using join buffer (Block Nested Loop) |
+----+-------------+----------------------+--------+----------------------------------------------------------------------------------+------------------------------------------+---------+-------------------------------------------------------+---------+--------------------------------------------------------------------------+

I'm not a very good db admin, so I don't know what is going on here. When this is run from the application (Magento 1 application), this stalls out and will eventually cause a timeout in several hours. So if anybody knows what I would need to optimize this, or get past the "Sending Data" status, it would be greatly appreciated.

  • From the explain plan it looks like there is a missing index on the table m_filter2_value_store. If you could provide the output of SHOW CREATE TABLE m_filter2_value_store; then it might be easier to give more exact advice. – dbdemon Dec 5 '17 at 21:15
0

According to the EXPLAIN target is the worst part. So... See if it helps to change

    LEFT JOIN  `m_filter2_value_store` AS `target`  ON target.global_id = global.id
      AND  target.store_id = core_store.store_id
 WHERE ...
      AND  ( target.id IS NULL )

to

 WHERE ...
   AND NOT EXISTS( SELECT 1 FROM m_filter2_value_store
             WHERE global_id = global.id
               AND store_id = core_store.store_id )

EAV sucks. See if you can improve some indexes with the tips here.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.