Problem of performance with mailwizz

Code: 
mysql> explain SELECT COUNT(DISTINCT t.subscriber_id) as counter FROM `mw_list_subscriber` `t`  LEFT JOIN `mw_list_field_value` `fieldValues434` ON (`fieldValues434`.`subscriber_id`=`t`.`subscriber_id`) WHERE ((t.list_id='15') AND (t.status='confirmed') AND ( (`fieldValues434`.`field_id` = 434 AND (CAST(`fieldValues434`.`value` AS UNSIGNED) = 0 OR CAST(`fieldValues434`.`value` AS UNSIGNED) = 3 OR CAST(`fieldValues434`.`value` AS UNSIGNED) = 4) ) ))\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
         type: ref
possible_keys: PRIMARY,fk_list_subscriber_list1_idx,list_email,status_last_updated,list_id_status
          key: list_id_status
      key_len: 49
          ref: const,const
         rows: 5331149
        Extra: Using where; Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: fieldValues434
         type: ref
possible_keys: fk_list_field_value_list_field1_idx,fk_list_field_value_list_subscriber1_idx,field_subscriber,field_id_value
          key: field_subscriber
      key_len: 8
          ref: const,mailwizz.t.subscriber_id
         rows: 1
        Extra: Using where
2 rows in set (0.00 sec)
 
Looks like the query uses the right indexes so i am not sure what else i could do here...
What is the time difference between running following queries, how much takes each:
Code: 
SELECT COUNT(DISTINCT t.subscriber_id) as counter FROM `mw_list_subscriber` `t`  LEFT JOIN `mw_list_field_value` `fieldValues434` ON (`fieldValues434`.`subscriber_id`=`t`.`subscriber_id`) WHERE ((t.list_id='15') AND (t.status='confirmed') AND ( (`fieldValues434`.`field_id` = 434 AND (CAST(`fieldValues434`.`value` AS UNSIGNED) = 0 OR CAST(`fieldValues434`.`value` AS UNSIGNED) = 3 OR CAST(`fieldValues434`.`value` AS UNSIGNED) = 4) ) ))

Code: 
SELECT COUNT(*) as counter FROM `mw_list_subscriber` `t`  LEFT JOIN `mw_list_field_value` `fieldValues434` ON (`fieldValues434`.`subscriber_id`=`t`.`subscriber_id`) WHERE ((t.list_id='15') AND (t.status='confirmed') AND ( (`fieldValues434`.`field_id` = 434 AND (CAST(`fieldValues434`.`value` AS UNSIGNED) = 0 OR CAST(`fieldValues434`.`value` AS UNSIGNED) = 3 OR CAST(`fieldValues434`.`value` AS UNSIGNED) = 4) ) ))
 
Code: 
mysql> SELECT COUNT(DISTINCT t.subscriber_id) as counter FROM `mw_list_subscriber` `t`  LEFT JOIN `mw_list_field_value` `fieldValues434` ON (`fieldValues434`.`subscriber_id`=`t`.`subscriber_id`) WHERE ((t.list_id='15') AND (t.status='confirmed') AND ( (`fieldValues434`.`field_id` = 434 AND (CAST(`fieldValues434`.`value` AS UNSIGNED) = 0 OR CAST(`fieldValues434`.`value` AS UNSIGNED) = 3 OR CAST(`fieldValues434`.`value` AS UNSIGNED) = 4) ) ));
+---------+
| counter |
+---------+
| 4716831 |
+---------+
1 row in set, 65535 warnings (43.98 sec)

mysql> SELECT COUNT(*) as counter FROM `mw_list_subscriber` `t`  LEFT JOIN `mw_list_field_value` `fieldValues434` ON (`fieldValues434`.`subscriber_id`=`t`.`subscriber_id`) WHERE ((t.list_id='15') AND (t.status='confirmed') AND ( (`fieldValues434`.`field_id` = 434 AND (CAST(`fieldValues434`.`value` AS UNSIGNED) = 0 OR CAST(`fieldValues434`.`value` AS UNSIGNED) = 3 OR CAST(`fieldValues434`.`value` AS UNSIGNED) = 4) ) ));
+---------+
| counter |
+---------+
| 4716831 |
+---------+
1 row in set, 65535 warnings (27.87 sec)
 
@Jean-Claude CHRISTOPHE - let's see how long this simple query takes:
Code: 
SELECT COUNT(*) from `mw_list_field_value` `fieldValues434` WHERE `fieldValues434`.`field_id` = 434 AND (CAST(`fieldValues434`.`value` AS UNSIGNED) = 0 OR CAST(`fieldValues434`.`value` AS UNSIGNED) = 3 OR CAST(`fieldValues434`.`value` AS UNSIGNED) = 4)
 
Code: 
mysql> SELECT COUNT(*) from `mw_list_field_value` `fieldValues434` WHERE `fieldValues434`.`field_id` = 434 AND (CAST(`fieldValues434`.`value` AS UNSIGNED) = 0 OR CAST(`fieldValues434`.`value` AS UNSIGNED) = 3 OR CAST(`fieldValues434`.`value` AS UNSIGNED) = 4);
+----------+
| COUNT(*) |
+----------+
|  4734102 |
+----------+
1 row in set, 65535 warnings (4.89 sec)
 
Ok, so it took almost 5 seconds for a count which on my machine really finishes in under half a second. There's def. something fishy here, but i am not sure what exactly.
What happens if you remove the cast:
Code: 
SELECT COUNT(*) from `mw_list_field_value` `fieldValues434` WHERE `fieldValues434`.`field_id` = 434 AND (`fieldValues434`.`value` = 0 OR `fieldValues434`.`value` = 3 OR `fieldValues434`.`value` = 4);
Code: 
SELECT COUNT(*) from `mw_list_field_value` `fieldValues434` WHERE `fieldValues434`.`field_id` = 434 AND `fieldValues434`.`value` IN(0, 3, 4);
 
Code: 
mysql> SELECT COUNT(*) from `mw_list_field_value` `fieldValues434` WHERE `fieldValues434`.`field_id` = 434 AND (`fieldValues434`.`value` = 0 OR `fieldValues434`.`value` = 3 OR `fieldValues434`.`value` = 4);
+----------+
| COUNT(*) |
+----------+
|  4734100 |
+----------+
1 row in set, 270 warnings (4.62 sec)

mysql> SELECT COUNT(*) from `mw_list_field_value` `fieldValues434` WHERE `fieldValues434`.`field_id` = 434 AND `fieldValues434`.`value` IN(0, 3, 4);
+----------+
| COUNT(*) |
+----------+
|  4734100 |
+----------+
1 row in set, 268 warnings (4.36 sec)
My mw_list_field_value table is enormous, could it be the problem ? normally with indexes it shouldn't ..
 
Code: 
mysql> select count(*) from mw_list_field_value;
+-----------+
| count(*)  |
+-----------+
| 164849341 |
+-----------+
1 row in set (1 min 15.85 sec)
 
I'm facing the same problem, my server has 16GB of RAM, I also used percona's page to design the my.conf.
Code: 
mysql> SELECT count(*) FROM mw_list_field_value;

+-----------+
| count(*)  |
+-----------+
| 128174741 |
+-----------+
1 row in set (2 min 13.76 sec)

Did you solve your problem? I'm thinking about if partitioning some tables would improve the perfomance.
 
You must log in or register to reply here.
Back
Top