View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0015417 | phpList 3 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 | ||
| 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. | ||||
