NOTE:: Before reporting an issue, make sure you are running the latest version, currently 3.3.1
|
NOTE:: Before reporting an issue, make sure you are running the latest version, currently 3.3.1 |
| Anonymous | Login | Signup for a new account | 28-07-17 13:49 BST |  |
| My View | View Issues | Change Log | Roadmap |
| View Issue Details [ Jump to Notes ] | [ Print ] | ||||||||
| ID | Project | Category | View Status | Date Submitted | Last Update | ||||
| 0015417 | phplist application | Bounce Management | public | 25-02-10 13:59 | 19-04-10 21:47 | ||||
| Reporter | rolan | ||||||||
| Priority | normal | Severity | tweak | Reproducibility | N/A | ||||
| Status | resolved | Resolution | fixed | ||||||
| Platform | OS | OS Version | |||||||
| Product Version | 2.10.10 | ||||||||
| Target Version | 2.10.12 | Fixed in Version | 2.10.12 | ||||||
| Summary | 0015417: speed up bounce management by optimizing mysql tables and queries to use indices | ||||||||
| Description | bounce 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 Information | Here 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) | ||||||||
| Tags | No tags attached. | ||||||||
| Attached Files | |||||||||
 Relationships [ Relation Graph ]
[ Dependency Graph ]
|
||||||
|
||||||
 Relationships |
|
Notes |
| Copyright © 2000 - 2017 MantisBT Team |
 |