View Issue Details

IDProjectCategoryView StatusLast Update
0015417phplist applicationBounce Managementpublic19-04-10 20:47
Reporterrolan 
PrioritynormalSeveritytweakReproducibilityN/A
Status resolvedResolutionfixed 
Product Version2.10.10 
Target Version2.10.12Fixed in Version2.10.12 
Summary0015417: speed up bounce management by optimizing mysql tables and queries to use indices
Descriptionbounce management will significantly improve in speed by adding an index to the status column of the bounce table.

create index statusindex on phplist_bounce (status(10));

Also, there are a number of queries throughout the phplist scripts which do not make use of the "date" index. Rewriting the query in the manner below makes use of them:

@@ -16,10 +16,10 @@
 if (isset($_GET['action']) && $_GET['action']) {
   switch($_GET['action']) {
     case "deleteunidentified":
- Sql_Query(sprintf('delete from %s where comment = "unidentified bounce" and date_add(date,interval 2 month) < now()',$tables["bounce"]));
+ Sql_Query(sprintf('delete from %s where status = "unidentified bounce" and `date` < date_sub(now(),interval 2 month)',$tables["bounce"]));
       break;
     case "deleteprocessed":
- Sql_Query(sprintf('delete from %s where comment != "not processed" and date_add(date,interval 2 month) < now()',$tables["bounce"]));
+ Sql_Query(sprintf('delete from %s where comment != "not processed" and `date` < date_sub(now(),interval 2 month)',$tables["bounce"]));
       break;
     case "deleteall":
       Sql_Query(sprintf('delete from %s',$tables["bounce"]));
Additional InformationHere is some supporting evidence:

describe select id,date,status,comment from phplist_bounce where date_add(date,interval 2 month) < now();
+----+-------------+----------------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------+------+---------------+------+---------+------+-------+-------------+
| 1 | SIMPLE | phplist_bounce | ALL | NULL | NULL | NULL | NULL | 27589 | Using where |
+----+-------------+----------------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.00 sec)



describe select id,date,status,comment from phplist_bounce where `date` < date_sub(now(),interval 2 month);
+----+-------------+----------------+-------+---------------+-----------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------+-------+---------------+-----------+---------+------+------+-------------+
| 1 | SIMPLE | phplist_bounce | range | dateindex | dateindex | 9 | NULL | 7 | Using where |
+----+-------------+----------------+-------+---------------+-----------+---------+------+------+-------------+
1 row in set (0.02 sec)
TagsNo tags attached.

Relationships

related to 0015318 resolvedmichiel Index needed on phplist_user_message_bounce table 

Activities

rolan

26-02-10 23:53

reporter   ~0050841

the above patch is for bounces.php