phplist

NOTE:: Before reporting an issue, make sure you are running the latest version, currently 3.3.1


View Issue Details Jump to Notes ] Print ]
IDProjectCategoryView StatusDate SubmittedLast Update
0015417phplist applicationBounce Managementpublic25-02-10 13:5919-04-10 21:47
Reporterrolan 
PrioritynormalSeveritytweakReproducibilityN/A
StatusresolvedResolutionfixed 
PlatformOSOS Version
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.
Attached Files

- Relationships Relation Graph ] Dependency Graph ]
related to 0015318resolvedmichiel Index needed on phplist_user_message_bounce table 

-  Notes
(0050841)
rolan (reporter)
26-02-10 23:53

the above patch is for bounces.php


Copyright © 2000 - 2017 MantisBT Team
Powered by Mantis Bugtracker