Jump to content
vekia

[module] database clean up - optimization of store DB - speed up your store

Recommended Posts

Database optimize module


This free addon and decreases your website load time, especially in old big stores, but also for new "fresh" installations with huge databases.


download link below (I attached .zip file to this post)


 


How it works?


PrestaShop stores a lot of  informations in the database, this module allows to clean all unwanted informations like abandoned carts, connections informations, guests informations etc.


With this addon you can remove them easily.


 


module works with prestashop 1.4.x - 1.5.x - 1.6.x - prestashop 1.7 


 


database-optimisation-prestashop.png


  • Like 8

Share this post


Link to post
Share on other sites

version 1.1 released

fixed problems with cart clean

enjoy!

  • Like 2

Share this post


Link to post
Share on other sites

Is not dangerous to delete all carts? or what carts do you empty?

Share this post


Link to post
Share on other sites

it's not dangerous to empty abandoned carts, the only one disadvantage: you will lost them :)

the same with stats etc.

Share this post


Link to post
Share on other sites

Seems like a great module. Do i need to backup only my database or whole site before using it?

How different is this module from the one posted by @shaker? http://www.prestashop.com/forums/topic/90075-moduledelete-connections-v18-save-space-on-databaseoptimize-all-tables-for-more-speed-ps1215/page-2

Edited by SeVi (see edit history)

Share this post


Link to post
Share on other sites

always make backups, even if you don't use this module :)

remember, that after using this module, you will lose informations about:

- abandoned carts

- stats

  • Like 1

Share this post


Link to post
Share on other sites

i created a backup. Where can i read what exactly is stored in Connection informations tables?  My older store DB is 130MB and it hasn't been optimized in 3 years, i only deleted carts every couple months. 

 

Connection informations (1522250)

Connection informations (source) (2309919)

 

Will your module help or i need to install module by moderator Shaker too? Are they similar or have different features?

Thanks in advance

Edited by SeVi (see edit history)

Share this post


Link to post
Share on other sites

i don't know shacker module.

in connection information tables prestashop script store information about visits in your store (stats) like ip address of visit, visit time, device informations (resolution of screen, browsers etc) of each visit in your store (both robot visits and guests visits)

Share this post


Link to post
Share on other sites

turn on error reporting please, then instead of blank page you will see full error code (with information what and where doesnt work)

Share this post


Link to post
Share on other sites

Here is what I get: 

 

Warning: Cannot modify header information - headers already sent by (output started at /home/magars25/public_html/modules/dboptimization/dboptimization.php:2) in/home/magars25/public_html/classes/Tools.php on line 141

Share this post


Link to post
Share on other sites

strange, nothing more? that's all?

what version of module you're trying to install? can you show exact number please? 

Share this post


Link to post
Share on other sites

Yeah, that's all, I don't get anything else. I tried installing the one attached on the first post of this topic, if I think it is version 1.1.

Share this post


Link to post
Share on other sites

good day I tested this module on my local server but i get

 

Warning: Cannot modify header information - headers already sent by (output started at C:\xampp\htdocs\koshersupercenter\modules\dboptimization\dboptimization.php:2) in C:\xampp\htdocs\koshersupercenter\classes\Tools.php on line 141

Share this post


Link to post
Share on other sites

i uploaded 1.2 version,

please verify it.

Share this post


Link to post
Share on other sites

glad to hear that it works now

thank you for patience

milos

Share this post


Link to post
Share on other sites

I have 1.2 version but always I have message:

 

  • Database Optimization: New version available, check MyPresta.eu for more informations

 

How can I solve this problem ?

Share this post


Link to post
Share on other sites

you changed  $actual_version to 99, change it to 9999 an reinstall module :)

Share this post


Link to post
Share on other sites

you changed  $actual_version to 99, change it to 9999 an reinstall module :)

 

Do not work :( Still:

 

  • Database Optimization: New version available, check MyPresta.eu for more informations

Share this post


Link to post
Share on other sites

you reinstalled module after these changes? it's necessary to reinstall it (uninstall it, then install it once again - with changed file)

Share this post


Link to post
Share on other sites

you reinstalled module after these changes? it's necessary to reinstall it (uninstall it, then install it once again - with changed file)

 

Yes I reinstalled. 

Share this post


Link to post
Share on other sites

Does this module delete all records in all tables associated with an abandoned shopping cart?

 

Or is it just toggling whether or not abandoned carts are displayed in the Shopping Carts list?

 

As I understand it, there are a number of tables that have to be updated when an abandoned cart is deleted.

 

Details on what is "cleaned" and what is not would be helpful.

Share this post


Link to post
Share on other sites

you changed  $actual_version to 99, change it to 9999 an reinstall module   :)

 

I had solved problem. Step by step:

 

1. Uninstall akctual module

2. Change: $actual_version to 0000,

3. Install changed module

 

And annoying message go away :D

Share this post


Link to post
Share on other sites

 

Database optimize module

This free addon and decreases your website load time, especially in old big stores, but also for new "fresh" installations with huge databases

 

How it works?

PrestaShop stores a lot of  informations in the database, this module allows to clean all unwanted informations like abandoned carts, connections informations, guests informations etc.

With this addon you can remove them easily.

 

module works with prestashop 1.4.x - 1.5.x

 

It works on Prestashop 1.2.5 version too...verified on my shop...Thanks

Edited by ADM777 (see edit history)
  • Like 1

Share this post


Link to post
Share on other sites

thank you for information, i never tested it on versions below 1.4 so i suppose that it works also on 1.3 :)

thank you

Share this post


Link to post
Share on other sites

Version 1.6 comes with the Prestashop Cleaner found in the Administration modules. The first two item will wipe your store after you have gotten past the learning curve I guess. The last two options optimize the database and clear the old bulging data.  

 

Disclaimer: Take a backup before playing with it. 

Share this post


Link to post
Share on other sites

Thanks a lot :) It workes on Prestashop 1.4.6.

A Tip for next Update if its possible, to delte from date xx.xx.xxxx to xx.xx.xxxx.

So i can delete everything but hold maybe last month :)

 

Thanks again!!! :)

Share this post


Link to post
Share on other sites

Hello Vekia and thank you very much for ANOTHER awesome free module :)

 

Would it be possible for you to add a date and delete carts before this date ?

 

That way, last customers don't loose their carts when the connect to the shop :)

Share this post


Link to post
Share on other sites
After installing the module, the following data are displayed to me:

 

unwanted carts (67)

Connection informations (575 448)

Connection informations (page) (4)

Connection informations (source) (17496)

Guests (2407235)

 

Apart from unwanted carts (which I deleted), Vekia you could tell me what I have to clean? Can anyone give me some advice?

post-755172-0-62094700-1415275103_thumb.png

Edited by marcellolaforgia (see edit history)

Share this post


Link to post
Share on other sites

Hi everybody. 

 

The cleaner module pscleaner that comes with prestashop 1.6 cannot handle a sigle store in a multistore installation. It wipes out all the stores. In my case, I have two stores in multistore mode, one with real data and the second with test data. I have finnished all the testing in the second store and want to wipe out all test data from it, products, categories, subcategories, orders, clients etc. How do I do that?

Pscleaner doesn't give you the option to select one of the stores.

Is there another way to do it?

Somebody knows of another module that handles this case?

 

Thank you.

Share this post


Link to post
Share on other sites

Hello Vekia and thank you very much for ANOTHER awesome free module :)

 

Would it be possible for you to add a date and delete carts before this date ?

 

That way, last customers don't loose their carts when they connect to the shop :)

 

Hi Vekia, can you take a look at this date option ? Delete all carts is annoying for last customers, it would be good to keep 1 months carts for example :) Thanks a lot

  • Like 1

Share this post


Link to post
Share on other sites

Hi again vekia, I just saw that the modules delete every carts, inclided orders carts, isn't it dangerous ?

 

Another thing, it doesn't delete ps_cart_product lines so the cleanup is not full ?

  • Like 1

Share this post


Link to post
Share on other sites

Hi Vekia, can you take a look at this date option ? Delete all carts is annoying for last customers, it would be good to keep 1 months carts for example :) Thanks a lot

 

 

Hi!

 

I agree... Is there any posibility to limit the carts cleaning up to 1 month? :rolleyes:

 

Thanks!

 

PS. Does this module work with PS 1.6.0.9?

Share this post


Link to post
Share on other sites

Hi any answer about Ps 1.6.0.9 compatibility wih this module? thanks

 

Somebody test it?

Share this post


Link to post
Share on other sites

Somebody test it?

 

I think we are alone with that question so I'm gonna try it in a local test server... and I will post the result

 

 

EDIT: I've tried to install it on prestashop 1.6.0.9 but the installation doesn't work

 

" Technical error: PrestaShop could not installed this module."  :huh: 

Edited by Antakarana (see edit history)

Share this post


Link to post
Share on other sites

Hello, i have try the module in prestashop 1.5.6.2 but it removes all carts ...

 

I want to remove only cart with 0 products inside, how can i do ?

Share this post


Link to post
Share on other sites

I think we are alone with that question so I'm gonna try it in a local test server... and I will post the result

 

 

EDIT: I've tried to install it on prestashop 1.6.0.9 but the installation doesn't work

 

" Technical error: PrestaShop could not installed this module."  :huh: 

 

 

hello

you can install it with anyway, even with this message

Share this post


Link to post
Share on other sites

Hi Vekia,

 

thanks for your comment, however i wasn't able to install it on the local test server (it is a problem with that server because it also happens with other modules that I wanna test) so I will have to found the solution to fix the modules installation issue or try to reinstall the whole server and try again...

 

Anyway, how can I install the module even with that error message? I've tried to manualy copy/paste the folder of the module on the modules directory from server but at BackOffice-->modules it only gives me the option of install it and then is when I got the problem.

 

thanks

Share this post


Link to post
Share on other sites

hello

you're on localhost and you have this issue?

what ps version ? from official package?

Share this post


Link to post
Share on other sites

hello, i asked Antakarana ;)

in your case: module hasn't got feature to remove only certain part of carts

it removes each cart.

Share this post


Link to post
Share on other sites

Hi Vekia,

 

Thanks for answering... it is localhost.

 

It is PS. 1.6.0.9 I will reinstall the localhost again to check if it works

Share this post


Link to post
Share on other sites

Hi Vekia,

 

Thanks for sharing this module with the community, its fantastic.

Any chances of adding links on the bottom of the module configuration page to create cron jobs for this actions?

It will be useful for shops which want to automatically reset the tables once or twice each month because they receive high volume traffic.

 

Best Regards.

  • Like 1

Share this post


Link to post
Share on other sites

Hi Vekia,

 

Thanks for sharing this module with the community, its fantastic.

Any chances of adding links on the bottom of the module configuration page to create cron jobs for this actions?

It will be useful for shops which want to automatically reset the tables once or twice each month because they receive high volume traffic.

 

Best Regards.

Best idea !!!!!

  • Like 1

Share this post


Link to post
Share on other sites

Thank you, it has decreased the size of database from 20mb to 5mb!

Share this post


Link to post
Share on other sites

@Vekia

 

I would like to report a bug (potential bug to AdminOrdersController.php ?) if we empty unwanted carts from your module.

To reproduce the error/bug is simple. Please clean install PS 1.5.6.2 locally.

Visit BO => Orders, click John DOE's order.

Add $cartRuleObj = 1 (reduction_percent), it will work.

Add $cartRuleObj = 2 (reduction_amount), it will work.

 
OK, now use your module and clean unwanted carts. It will empty the ps_cart table.

Visit BO => Orders, click John DOE's order.

Add $cartRuleObj = 1 (reduction_percent), it will survive.

Add $cartRuleObj = 2 (reduction_amount), it will throw Fatal error.

 

What shall I do Vekia? I use $cartRuleObj = 2 (reduction_amount) a lot to give disc to my customer.

And I use your module too, need to clean-up the unpaid cart.

Share this post


Link to post
Share on other sites

Thanks for this module. Seems to have worked well.

 

We successfully ran it on PrestaShop 1.6.1.0. It took our client's total database size from 30mb to 2.3Mb - which is a massive reduction.

Share this post


Link to post
Share on other sites

Great tool!! It reduced my ps 1.6.06 from 2.6 MB  to 1.6 MB in the blink of an eye

After the work was done I uninstalled until next year and double checked everything and everything was fine.

:)

Share this post


Link to post
Share on other sites

Just used this module on PS 1.6.0.13 and it's improved my average page load time from about 3 seconds to about 500ms. Unbelievable.

Reduced DB size from 70MB to 25MB.

Some customer details pages in the back end took over 10 seconds to load (most regular customers) and are now loading in under a second.

Thanks so much for this amazing module :D

Share this post


Link to post
Share on other sites

new version of this free module is available!

- added improvements to module configuration page

- added feature to check updates directly from module configuration page

- added new better support of prestashop 1.6.x

- module works with prestashop 1.7 RC (release candidate version)

  • Like 1

Share this post


Link to post
Share on other sites

Hi,

 

Why do you think I get this error?

An error occurred while copying the archive to the module directory.

while installing this module... :ph34r:

Thanks

Share this post


Link to post
Share on other sites

Like usual for modules, back-office / modules / upload add a module / upload ;)

Share this post


Link to post
Share on other sites

Hi Vekia,

Thanks for this free module. 

I just downloaded the version 1.3.1 in our PS 1.6.1.16 and ran the optimisations scripts. In our Test site, the database went from 493Mb to 231Mb, excellent. However then ran it also in our production database (590Mb), and it went up to 925Mb!!! What we can do?

Best regards,

Gonzalo

Share this post


Link to post
Share on other sites

Hi there, I am wondering if this will help a problem I see happening. I have a customer that is trying to place a large order approx 150 to 200 individual items. It seems now that she has that many items in her cart, pages will time out saying this page isn't working. metalclayalchemist.ca is currently unable to handle the request http error 500. 

 

 

 

Or anyone have any suggestions on how I can rectify this?

Thanks

Carrie

Share this post


Link to post
Share on other sites

Hi Vekia

 

I have found a bug or problem with your module.

 

Let's say that I have cart number 3500 (date of this cart is 12.2.2012) and this cart was succesfuly made to order id 587. Now this order and cart is stayed in databese till today. At the start of this year 2017 I have use your module to clean up database and it realy help to reduce database. But now I again came to cart number 3500 and inside this cart is product added today wit one product, but when you click on order you get totaly different content of order whish was made in the year 2012. PLease see also attached pictures. I think that when tables are emptyed new entries should start with continious ID

post-33924-0-71993400-1506892979_thumb.jpg

post-33924-0-18185300-1506892989_thumb.jpg

post-33924-0-64258700-1506892996_thumb.jpg

Share this post


Link to post
Share on other sites

Hello,

Will you ever add the ability to enter a date so we don't delete carts that are only 30 or 60 days old? Can I hardcode this in if that's a much easier way to handle? And I would love to be able to set up cron jobs for this. Will that ever be added? Maybe make a paid version with these features? I would be willing to pay.

Thanks for this great module!

Alex

Share this post


Link to post
Share on other sites

You are right ajensen27, delete all carts is just too much.

 

We should at least keep one month old carts and not delete them.

Share this post


Link to post
Share on other sites

Please add to dboptimization.php for clear  page_viewed and pagenotfound. Or download attachment :-)

 

        <form action="' . $_SERVER['REQUEST_URI'] . '" method="post" style="margin-top:20px">
            <input type="hidden" name="dbtabletoclean" value="page_viewed" />
            <div style="display:block; margin:auto; overflow:hidden; width:100%; vertical-align:top;">
                <fieldset style=" margin-right:10px; vertical-align:top;">
                    <legend><img src="' . $this->_path . 'logo.gif" alt="" title="" />' . $this->l('Page viewed') . ' (' . $this->dbcounter('page_viewed') . ')</legend>
                    <input type="submit" name="dbtableclean" value="' . $this->l('clean') . '" class="button"/>
                    ' . $this->l('date of last clean') . ': <b>' . (Configuration::get('dbopt_guest') == null ? $this->l('not cleaned yet') : Configuration::get('dbopt_guest')) . '</b>
                </fieldset>
            </div>
        </form>      

       <form action="' . $_SERVER['REQUEST_URI'] . '" method="post" style="margin-top:20px">
            <input type="hidden" name="dbtabletoclean" value="pagenotfound" />
            <div style="display:block; margin:auto; overflow:hidden; width:100%; vertical-align:top;">
                <fieldset style=" margin-right:10px; vertical-align:top;">
                    <legend><img src="' . $this->_path . 'logo.gif" alt="" title="" />' . $this->l('Page not found') . ' (' . $this->dbcounter('pagenotfound') . ')</legend>
                    <input type="submit" name="dbtableclean" value="' . $this->l('clean') . '" class="button"/>
                    ' . $this->l('date of last clean') . ': <b>' . (Configuration::get('dbopt_guest') == null ? $this->l('not cleaned yet') : Configuration::get('dbopt_guest')) . '</b>
                </fieldset>
            </div>
        </form>      
 

dboptimization.zip

Share this post


Link to post
Share on other sites

Hi, I just tried your module on ps 1.5 store and I saw that the rows from those tables are deleted, but the size of the tables is not changing! Is it possible to rebuild the database so the size refreshes? If I download the database and then upload it again, the size refreshes but I don't want to experiment with my live site.

Share this post


Link to post
Share on other sites

Greetings,

 

First of all many thanks to @vekia for his great modules and continuous support in the Prestashop community for so many years now! Big UP man!

 

On 10/1/2017 at 11:21 PM, malcek said:

Hi Vekia

 

I have found a bug or problem with your module.

 

Let's say that I have cart number 3500 (date of this cart is 12.2.2012) and this cart was succesfuly made to order id 587. Now this order and cart is stayed in databese till today. At the start of this year 2017 I have use your module to clean up database and it realy help to reduce database. But now I again came to cart number 3500 and inside this cart is product added today wit one product, but when you click on order you get totaly different content of order whish was made in the year 2012. PLease see also attached pictures. I think that when tables are emptyed new entries should start with continious ID

 

 

What you describe here @malcek is kind of strange because here even if I delete all carts (which I never do) it restart at last used id + 1.

Db::getInstance(_PS_USE_SQL_SLAVE_)->execute('DELETE FROM `' . _DB_PREFIX_ . Tools::getValue('dbtabletoclean') . '`');

After all this line simply wipe all carts from database but does not reset ids.

Now I am unsure what happens if you dump that DB right away with 0 values inside, then import it straight back with the DROP TABLE set first. With no values inside, might reset ids to 0, all I can think of, but I have not tested this.

 

 

On 10/27/2017 at 9:05 PM, ajensen27 said:

Hello,

Will you ever add the ability to enter a date so we don't delete carts that are only 30 or 60 days old? Can I hardcode this in if that's a much easier way to handle? And I would love to be able to set up cron jobs for this. Will that ever be added? Maybe make a paid version with these features? I would be willing to pay.

Thanks for this great module!

Alex

 

On 10/28/2017 at 12:40 AM, KevinNash said:

You are right ajensen27, delete all carts is just too much.

 

We should at least keep one month old carts and not delete them.

 

@ajensen27 @KevinNash I totally agree with you, that too suits my needs better, and I always prefer to keep some info, and might be also useful for returning visitors/customers who had an active cart.

The time thing is quite easy to solve you only have to add something like this at the end of the query:

WHERE date_upd < DATE_SUB(NOW(), INTERVAL 1 MONTH)

Many use date_add here but to me it makes more sense to check versus last updated date for the cart therefore date_upd instead, because as long as cart is alive, by all means it ain't dead.

But to be honest here, for me 'unwanted carts' also means cart  which were not used to make orders, because in the present case it just gets rid of all carts in history, wanted or not. That's not what I wanted myself for my website, so I made some quick edits in the dboptimisation.php file and will now sharing them here with you for those who might be interested and/or if OP feels like it could be a good addition to his FREE module:

--- dboptimization_original.php 2018-04-17 10:44:59.238174097 +0000
+++ dboptimization_updated.php  2018-04-17 10:32:44.315877426 +0000
[spam-filter] -131,7 +131,12 [spam-filter]
         if (Tools::isSubmit('dbtabletoclean'))
         {
             Configuration::updateValue('dbopt_' . Tools::getValue('dbtabletoclean'), date("Y-m-d h:i:s"));
-            Db::getInstance(_PS_USE_SQL_SLAVE_)->execute('DELETE FROM `' . _DB_PREFIX_ . Tools::getValue('dbtabletoclean') . '`');
+                                               if (Tools::getValue('dbtabletoclean') == 'cart') {
+                                                       Db::getInstance(_PS_USE_SQL_SLAVE_)->execute('DELETE FROM `' . _DB_PREFIX_ . Tools::getValue('dbtabletoclean') . '` WHERE id_cart NOT IN (SELECT id_cart FROM `' . _DB_PREFIX_ .  "orders" . '`) AND date_upd < DATE_SUB(NOW(), INTERVAL 1 MONTH)');
+                                               }
+                                               else {
+                                                       Db::getInstance(_PS_USE_SQL_SLAVE_)->execute('DELETE FROM `' . _DB_PREFIX_ . Tools::getValue('dbtabletoclean') . '`');
+                                               }
         }
         $output .= "";
         return $output . $this->displayForm() . $this->checkforupdates(0,1);
[spam-filter] -258,4 +263,4 [spam-filter]
     }
 }

-?>
\ No newline at end of file
+?>

What this differentiated query for cart does, is to remove carts which were not used to create orders and which are older than 1 month from date now(). Of course that 1 month delay can be changed to anything you feel like suiting you better.

Keep on doing all the great work @vekia!

 

Hope this helps,

  • Like 1

Share this post


Link to post
Share on other sites

Hi,

I cannot add a product to my shopping cart after cleaning the shop from abandoned shopping carts.
Unfortunately, in the database in the table ps_cart create entries with id_cart = 0.
ALTER TABLE ps_cart AUTO_INCREMENT='next number of carts' does not work.

I have restored the order from a backup, but I can't add products to my cart because id_cart is still 0.

Please help me with this error.

Prestahop 1.7.4.4

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

Important Information

Cookies ensure the smooth running of our services. Using these, you accept the use of cookies. Learn More