1

I switched on Devel logging on site (shop on Drupal Commerce), went to Shop->Orders and saw that

3818.231 views_plugin_query_default::execute P A E
SELECT commerce_order.order_id AS order_id, commerce_order.order_number AS commerce_order_order_number, commerce_order.uid AS commerce_order_uid, commerce_order.created AS commerce_order_created, users_commerce_order.name AS users_commerce_order_name, users_commerce_order.uid AS users_commerce_order_uid, commerce_order.status AS commerce_order_status, 'commerce_order' AS field_data_commerce_order_total_commerce_order_entity_type FROM commerce_order commerce_order LEFT JOIN users users_commerce_order ON commerce_order.uid = users_commerce_order.uid LEFT JOIN recently_read recently_read_commerce_order ON commerce_order.order_id = recently_read_commerce_order.entity_id LEFT JOIN commerce_payment_transaction commerce_payment_transaction_commerce_order ON commerce_order.order_id = commerce_payment_transaction_commerce_order.order_id WHERE (( (commerce_order.status NOT IN (:db_condition_placeholder_0, :db_condition_placeholder_1, :db_condition_placeholder_2, :db_condition_placeholder_3, :db_condition_placeholder_4, :db_condition_placeholder_5)) )) ORDER BY commerce_order_created DESC LIMIT 100 OFFSET 0
default

2422.621 views_plugin_pager::execute_count_query P A E
SELECT COUNT(*) AS expression FROM (SELECT 1 AS expression FROM commerce_order commerce_order LEFT JOIN users users_commerce_order ON commerce_order.uid = users_commerce_order.uid LEFT JOIN recently_read recently_read_commerce_order ON commerce_order.order_id = recently_read_commerce_order.entity_id LEFT JOIN commerce_payment_transaction commerce_payment_transaction_commerce_order ON commerce_order.order_id = commerce_payment_transaction_commerce_order.order_id WHERE (( (commerce_order.status NOT IN (:db_condition_placeholder_0, :db_condition_placeholder_1, :db_condition_placeholder_2, :db_condition_placeholder_3, :db_condition_placeholder_4, :db_condition_placeholder_5)) ))) subquery

Is it normal that on site with 3000 items and 7000 orders it takes so much time to form orders view in admin interface?

Site is hosted on server, Xeon E5-2609x2 (2.4 Ghz), 16 Gb DDR-3 memory

  • Can you also add some details of the server your hosted on? Like the memory and cpu available for you? – esafwan Dec 5 '13 at 7:10
  • Is it OK? Well, who are we to determine what's OK, what's normal et cetera? If you think it's a DB problem, you are asking in the wrong place. If you think it's a problem and want a drupal-side way to fix it, you aren't formulating your request quite right. – Mołot Dec 5 '13 at 7:51
0

6 second for a query, we can't say what is normal. However there are ways we can improve performance. with the server configuration you have, we can expect better performance.

There are several things you can do to solve this.

Optimize View:

In Drupal many of the performance issue are caused by views. These are mainly caused by use of Left Join in views. Use of pager, distinct feature etc.

These are things that can be solved easily.

  • You can alter views query to use Inner Join instead of Left Join by disabling the distinct in query either from views ui or code.
  • Avoid using last in pager or use views_litepager module instead.

Here is a sample views alter code:

/**
* Implements hook_views_query_alter().
*
* This disables distinct and turns LEFT joins into INNER joins. This increases
* performance.
*/
function my_module_views_query_alter(&$view, &$query) {
if ($view->name == "my_view") {
$query->table_queue['content_type_article']['join']->type = 'INNER';
$query->distinct = 0;
}
}

There is a good post on optimizing views that you can read to understand this better here.

Other than this you should check if your mysql configuration in my.cnf is optimized as per your system resources. For this you could use mysqltuner script or mysql-tuning-primer script.

Above that use of memcached with Drupal can also increase performance to great extend.

| improve this answer | |
1

Just looking at the query, the obvious culprit is the recently_read module. The join that is added to the query is fully unindexed:

LEFT JOIN recently_read recently_read_commerce_order ON commerce_order.order_id = recently_read_commerce_order.entity_id

The query doesn't really make sense, so I assume the Views integration for this module needs work.

| improve this answer | |

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