2

I'm currently migrating about 330,000 customer records for a new client, and what I've found is that the "Manage Customers" Grid will slow to a crawl whenever you try to apply any sort of sorting on the grid.

I've dug through queries being made for this grid, and found that when a sort is applied, a temp table is created and sorted through, which causes the query to take a couple minutes to run, and create a ~3G temp table in the MySQL tmp directory.

An easy solution is to disable sorting for this grid, but I was wondering if anyone has been able to make the grid work with sorting for this many customer records?

EDIT: Here's the query being used by the "Manage Customers" page:

SELECT e., at_prefix.value AS prefix, at_firstname.value AS firstname, at_middlename.value AS middlename, at_lastname.value AS lastname, at_suffix.value AS suffix, CONCAT_WS(' ', IF(at_prefix.value IS NOT NULL AND at_prefix.value != '', LTRIM(RTRIM(at_prefix.value)), ''), LTRIM(RTRIM(at_firstname.value)), IF(at_middlename.value IS NOT NULL AND at_middlename.value != '', LTRIM(RTRIM(at_middlename.value)), ''), LTRIM(RTRIM(at_lastname.value)), IF(at_suffix.value IS NOT NULL AND at_suffix.value != '', LTRIM(RTRIM(at_suffix.value)), '')) AS name, at_default_billing.value AS default_billing, at_billing_postcode.value AS billing_postcode, at_billing_city.value AS billing_city, at_billing_telephone.value AS billing_telephone, at_billing_region.value AS billing_region, at_billing_country_id.value AS billing_country_id, credit_tbl., IFNULL(credit_tbl.value, 0) AS credit_value FROM customer_entity AS e LEFT JOIN customer_entity_varchar AS at_prefix ON (at_prefix.entity_id = e.entity_id) AND (at_prefix.attribute_id = '4') LEFT JOIN customer_entity_varchar AS at_firstname ON (at_firstname.entity_id = e.entity_id) AND (at_firstname.attribute_id = '5') LEFT JOIN customer_entity_varchar AS at_middlename ON (at_middlename.entity_id = e.entity_id) AND (at_middlename.attribute_id = '6') LEFT JOIN customer_entity_varchar AS at_lastname ON (at_lastname.entity_id = e.entity_id) AND (at_lastname.attribute_id = '7') LEFT JOIN customer_entity_varchar AS at_suffix ON (at_suffix.entity_id = e.entity_id) AND (at_suffix.attribute_id = '8') LEFT JOIN customer_entity_int AS at_default_billing ON (at_default_billing.entity_id = e.entity_id) AND (at_default_billing.attribute_id = '13') LEFT JOIN customer_address_entity_varchar AS at_billing_postcode ON (at_billing_postcode.entity_id = at_default_billing.value) AND (at_billing_postcode.attribute_id = '30') LEFT JOIN customer_address_entity_varchar AS at_billing_city ON (at_billing_city.entity_id = at_default_billing.value) AND (at_billing_city.attribute_id = '26') LEFT JOIN customer_address_entity_varchar AS at_billing_telephone ON (at_billing_telephone.entity_id = at_default_billing.value) AND (at_billing_telephone.attribute_id = '31') LEFT JOIN customer_address_entity_varchar AS at_billing_region ON (at_billing_region.entity_id = at_default_billing.value) AND (at_billing_region.attribute_id = '28') LEFT JOIN customer_address_entity_varchar AS at_billing_country_id ON (at_billing_country_id.entity_id = at_default_billing.value) AND (at_billing_country_id.attribute_id = '27') LEFT JOIN customercredit_credit AS credit_tbl ON credit_tbl.customer_id = e.entity_id WHERE (e.entity_type_id = '1') ORDER BY e.entity_id DESC LIMIT 20

EDIT2: Here are the indexes currently being used on the customer_entity table:

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment customer_entity 0 PRIMARY 1 entity_id A 333393 NULL NULL BTREE
customer_entity 1 IDX_CUSTOMER_ENTITY_STORE_ID 1 store_id A 8 NULL NULL YES BTREE
customer_entity 1 IDX_CUSTOMER_ENTITY_ENTITY_TYPE_ID 1 entity_type_id A 6 NULL NULL BTREE
customer_entity 1 IDX_CUSTOMER_ENTITY_EMAIL_WEBSITE_ID 1 email A 333393 NULL NULL YES BTREE
customer_entity 1 IDX_CUSTOMER_ENTITY_EMAIL_WEBSITE_ID 2 website_id A 333393 NULL NULL YES BTREE
customer_entity 1 IDX_CUSTOMER_ENTITY_WEBSITE_ID 1 website_id A 8 NULL NULL YES BTREE
customer_entity 1 IDX_created_at 1 created_at A 333393 NULL NULL BTREE

EDIT3: It's a little messy, but I outputted the EXPLAIN to a file and copied it here:

id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE e ref IDX_CUSTOMER_ENTITY_ENTITY_TYPE_ID IDX_CUSTOMER_ENTITY_ENTITY_TYPE_ID 2 const 165978 Using temporary; Using filesort 1 SIMPLE at_prefix eq_ref UNQ_CUSTOMER_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID,IDX_CUSTOMER_ENTITY_VARCHAR_ATTRIBUTE_ID,IDX_CUSTOMER_ENTITY_VARCHAR_ENTITY_ID,IDX_CUSTOMER_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID_VALUE UNQ_CUSTOMER_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID 6 fredericks_store.e.entity_id,const 1
1 SIMPLE at_firstname eq_ref UNQ_CUSTOMER_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID,IDX_CUSTOMER_ENTITY_VARCHAR_ATTRIBUTE_ID,IDX_CUSTOMER_ENTITY_VARCHAR_ENTITY_ID,IDX_CUSTOMER_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID_VALUE UNQ_CUSTOMER_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID 6 fredericks_store.e.entity_id,const 1
1 SIMPLE at_middlename eq_ref UNQ_CUSTOMER_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID,IDX_CUSTOMER_ENTITY_VARCHAR_ATTRIBUTE_ID,IDX_CUSTOMER_ENTITY_VARCHAR_ENTITY_ID,IDX_CUSTOMER_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID_VALUE UNQ_CUSTOMER_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID 6 fredericks_store.e.entity_id,const 1
1 SIMPLE at_lastname eq_ref UNQ_CUSTOMER_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID,IDX_CUSTOMER_ENTITY_VARCHAR_ATTRIBUTE_ID,IDX_CUSTOMER_ENTITY_VARCHAR_ENTITY_ID,IDX_CUSTOMER_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID_VALUE UNQ_CUSTOMER_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID 6 fredericks_store.e.entity_id,const 1
1 SIMPLE at_suffix eq_ref UNQ_CUSTOMER_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID,IDX_CUSTOMER_ENTITY_VARCHAR_ATTRIBUTE_ID,IDX_CUSTOMER_ENTITY_VARCHAR_ENTITY_ID,IDX_CUSTOMER_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID_VALUE UNQ_CUSTOMER_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID 6 fredericks_store.e.entity_id,const 1
1 SIMPLE at_default_billing eq_ref UNQ_CUSTOMER_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID,IDX_CUSTOMER_ENTITY_INT_ATTRIBUTE_ID,IDX_CUSTOMER_ENTITY_INT_ENTITY_ID,IDX_CUSTOMER_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_VALUE UNQ_CUSTOMER_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID 6 fredericks_store.e.entity_id,const 1
1 SIMPLE at_billing_postcode eq_ref UNQ_CUSTOMER_ADDRESS_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID,IDX_CUSTOMER_ADDRESS_ENTITY_VARCHAR_ATTRIBUTE_ID,IDX_CUSTOMER_ADDRESS_ENTITY_VARCHAR_ENTITY_ID,IDX_CUSTOMER_ADDRESS_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID_VALUE UNQ_CUSTOMER_ADDRESS_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID 6 fredericks_store.at_default_billing.value,const 1
1 SIMPLE at_billing_city eq_ref UNQ_CUSTOMER_ADDRESS_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID,IDX_CUSTOMER_ADDRESS_ENTITY_VARCHAR_ATTRIBUTE_ID,IDX_CUSTOMER_ADDRESS_ENTITY_VARCHAR_ENTITY_ID,IDX_CUSTOMER_ADDRESS_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID_VALUE UNQ_CUSTOMER_ADDRESS_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID 6 fredericks_store.at_default_billing.value,const 1
1 SIMPLE at_billing_telephone eq_ref UNQ_CUSTOMER_ADDRESS_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID,IDX_CUSTOMER_ADDRESS_ENTITY_VARCHAR_ATTRIBUTE_ID,IDX_CUSTOMER_ADDRESS_ENTITY_VARCHAR_ENTITY_ID,IDX_CUSTOMER_ADDRESS_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID_VALUE UNQ_CUSTOMER_ADDRESS_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID 6 fredericks_store.at_default_billing.value,const 1
1 SIMPLE at_billing_region eq_ref UNQ_CUSTOMER_ADDRESS_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID,IDX_CUSTOMER_ADDRESS_ENTITY_VARCHAR_ATTRIBUTE_ID,IDX_CUSTOMER_ADDRESS_ENTITY_VARCHAR_ENTITY_ID,IDX_CUSTOMER_ADDRESS_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID_VALUE UNQ_CUSTOMER_ADDRESS_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID 6 fredericks_store.at_default_billing.value,const 1
1 SIMPLE at_billing_country_id eq_ref UNQ_CUSTOMER_ADDRESS_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID,IDX_CUSTOMER_ADDRESS_ENTITY_VARCHAR_ATTRIBUTE_ID,IDX_CUSTOMER_ADDRESS_ENTITY_VARCHAR_ENTITY_ID,IDX_CUSTOMER_ADDRESS_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID_VALUE UNQ_CUSTOMER_ADDRESS_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID 6 fredericks_store.at_default_billing.value,const 1
1 SIMPLE credit_tbl ALL NULL NULL NULL NULL 1

  • Sounds like an index would be helpful. Have you tried that? – Vernard Luz Jun 23 '15 at 2:34
  • Will you post the query used? Maybe we can optimize it. – Vernard Luz Jun 23 '15 at 2:55
  • Query looks good, you should take a look at MySQL's IFNULL method for the name column. Remove credit_tbl.* if not all columns are needed. – Vernard Luz Jun 24 '15 at 6:32
  • Well Vernard, the thing I'm trying to point out is that I'm not the one writing this query, it's generated by Magento by default for the "Manage Customers" grid. It's not performing slow because of the extra columns, it's performing slow because of the sorting being done on the temp table (see EXPLAIN above). – John Pangilinan Jun 24 '15 at 7:18
  • A printscreen would've been better :P I can barely read it – Vernard Luz Jun 24 '15 at 8:37
0

Best case would be an index is used for the sorting. Try MySQL's EXPLAIN command and optimize away.

I can't help without your query and the information from the EXPLAIN from your database.

You might be interested in changing the following system variables which may affect ORDER BY speed.

  • Increase the sort_buffer_size variable value.
  • Increase the read_rnd_buffer_size variable value.

Source: https://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html

EDIT:

Remove the entity_type_id from the condition, since you're already querying at customer_entity. I know it's magento code, but try to find out how this condition was added and remove it if you can.

| improve this answer | |
  • Thanks Vernard, yes indexing was my first instinct. I edited my post to include the query and the indexes for the customer_entity table. I would post the EXPLAIN but the formatting is verbose and awful, so if you know a better way to post that then I'll add that as well. – John Pangilinan Jun 23 '15 at 19:00
  • My intuition is that indexing may not be useful here, as what I'm getting from doing an EXPLAIN is that temporary table is being created, and then a sort is being applied after. I deliberately sorted on the entity_id in the above query to illustrate that, as the entity_id is already indexed within its table. – John Pangilinan Jun 23 '15 at 19:02
  • I'm assuming that Magento is capable of handling thousands of customers within its EAV Database structure, right? – John Pangilinan Jun 23 '15 at 19:02
  • So if you apply sorting on any column except entity_id, it becomes slow? – Vernard Luz Jun 24 '15 at 6:28
  • You can increase the command line's screen buffer size so that EXPLAIN output becomes readable. Open cli -> right click on menu -> properties to change screen buffer size – Vernard Luz Jun 24 '15 at 6:32

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.