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.
 
Back
Top