ps_product_download table

[RobS]

I tried posting this in a somewhat related discussion but thought best to start anew being the issue there was quite different although the topic title was the same. 

 

My main issue is at end of post which has to do with emptying the ps_product_download table but I thought a little history might help. 

 

Upon monitoring our account's mysql usage, I noticed multiple queries of the following form being invoked when the script, /home3/hgeem/public_html/index.php was being executed:

 

SELECT `display_filename` FROM `ps_product_download` WHERE `filename` = 'a80863632311e78081ad5ebdd352eb099b568bdc' LIMIT 1

 

Each query of this form was traversing over 5 million rows in the ps_product_download table of our hgeem_prst1 database, thereby taking a massive amount of time to complete its execution. To help with this, I added an index to the ps_product_download table. Observe the before and after.

 

Before:

 

mysql> EXPLAIN SELECT `display_filename`

-> FROM `ps_product_download`

-> WHERE `filename` = 'a80863632311e78081ad5ebdd352eb099b568bdc' LIMIT 1 ;

+----+-------------+---------------------+------+---------------+------+---------+------+---------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+---------------------+------+---------------+------+---------+------+---------+-------------+

| 1 | SIMPLE | ps_product_download | ALL | NULL | NULL | NULL | NULL | 5188032 | Using where |

+----+-------------+---------------------+------+---------------+------+---------+------+---------+-------------+

1 row in set (0.01 sec)

 

After:

 

mysql> EXPLAIN SELECT `display_filename` FROM `ps_product_download` WHERE `filename` = 'a80863632311e78081ad5ebdd352eb099b568bdc' LIMIT 1;

+----+-------------+---------------------+------+---------------+------+---------+-------+------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+---------------------+------+---------------+------+---------+-------+------+-------------+

| 1 | SIMPLE | ps_product_download | ref | i1 | i1 | 768 | const | 1 | Using where |

+----+-------------+---------------------+------+---------------+------+---------+-------+------+-------------+

1 row in set (0.00 sec)

 

Notice that the total rows traversed by the sample query dropped from 5188032 to just 1, indicating a marked improvement in its overall efficiency.

 

My problem now, however, is something else. Looking over the structure of the ps_product_download table, I observed the following fields:

 

+---------------------+---------------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+---------------------+---------------------+------+-----+---------+----------------+

| id_product_download | int(10) unsigned | NO | PRI | NULL | auto_increment |

| id_product | int(10) unsigned | NO | MUL | NULL | |

| display_filename | varchar(255) | YES | | NULL | |

| filename | varchar(255) | YES | MUL | NULL | |

| date_add | datetime | NO | | NULL | |

| date_expiration | datetime | YES | | NULL | |

| nb_days_accessible | int(10) unsigned | YES | | NULL | |

| nb_downloadable | int(10) unsigned | YES | | 1 | |

| active | tinyint(1) unsigned | NO | | 1 | |

| is_shareable | tinyint(1) unsigned | NO | | 0 | |

+---------------------+---------------------+------+-----+---------+----------------+

10 rows in set (0.00 sec)

 

I'm not 100% on this but it looks like this table is tracking statistical information relating to our site's product downloads. Likely any time a download is initiated on our site, one or more rows are being added to this database table resulting in the heavy accumulation of data. The following prestashop forum thread also seems to agree with this: http://www.prestasho...download-table/

 

I tried to empty this database table and immediately noticed a problem. I really hoped that emptying the ps_product_download would resolve this issue, but unfortunately it looks like this table also affects our visitors' ability to download their purchases from within their own user accounts on our shop. It also prevented emails from being sent to our customers with their attached download links. I had to restore from backup and now everything is back to the way it was. 

 

Does anyone know how to empty the ps_product_download table without this problem occurring? Thanks

[RobS]

This post has been picked up here: http://www.prestashop.com/forums/topic/310139-problem-with-ps-product-download-table/