How to optimize product_download table with more then 190.000.000 records

[tomasz]

Hello guys,

This is my first post on the forum so if I do something wrong, just correct me so I will newer do this again.

 

Im a php programmer and web developer. One of my client have problem with his Prestashop 1.5.6.2 shop.

 

It is all about product_download table in database, which have more then 190.000.000 records (really!)

My client sell almost only free products with downloadable files. These all records were created in about 1 year of using shop.

 

At this time, when someone wants to download bought file, he waits and waits and see 500 error.

 

I have debuged get-file controller, and noticed that one sql statement is shuting down mysql server for a while.

This sql statement is connected with table product_download, it's simple SELECT with one condition which is filename='7618866a0bac630ba4fd3f5fa706f6e1b7a9edb6' for example.

 

I have noticed that table product_download do not have any index on this column (only for id_product and active).

This makes this sql select statment imposible to execute.

 

Now, after creating new table with another structure (field filename changed from varchar(255) to char(40) and set index on filename column) I am copying 1.000.000 records one by one from old table. I hope this will help with 500 error at front page.

 

If not, I will have to clean this table from unwanted records. Total size of this table is 19GB (15GB data, 4GB index) which is a lot to much. 

 

There is one problem, I am not sure which records I can delete, and maybe someone will know how to optimize this table for better performance and size. Maybe some cron job which will delete old records will be good option?

 

Thanks for any answer!

 

Thomas