Various data new data will be added to your site everyday, like shopping carts, guests, logs, connection data, etc, as time goes by, they will gradually reduce the performance of your database, slow your site down. This PrestaShop database cleaner module is designed to help you clean out old useless data from your site database to keep your site database be always in very good condition.
Why it's necessary to clean up the database.
It's recommended for everyone to clean up your site database regularly. For a live site it's easy to reach 200,000+ logs, 500,000+ connections and 500,000+ guests, comparing with the amount of your product data, you would find that most of data in your database are useless, that's why you need to clean your site database up.
How come cleaning up the database reduces page loading time?
Finding a certain row in a database is no much difference with find a certain kid in a playground, the more children in the playground, the long it takes to find the kid you are looking for, that's why cleaning up database can speed your site up.
The MoSCoW method
The module uses the MoSCoW method (Must have, Should have, Could have, and Won't have) to categorize old useless data into 3 categories. They are "Must clean up", "Should clean up", "Could clean up". It's recommended to do clean up data belonging to the first two categories. Beside the cleaning up feature, this module also provides a cron job feature and some optimization tools.
(A1) Clean up connections
Connection data are for you to do data mining to find out thinks like, where visitors come from, which products are viewed the most. But old connections are useless, storing them in the database would reduce the overall performance of your site, so it's very important to clean old connection data out. Here are 3 types of connection data.
- Connections: A new connection record will be created by the "Data mining for statistics" module for every new connection to store data like when and who connects to your site. This table can become huge in a short time if your site has a lot of traffics.
- Connections source: The is also majorly used by the "Data mining for statistics" module, it stores data of where people come from, how people jump from one page to another.
- Connections page: This table can consume a lot of database space if the "Save page views for each customer" setting or the "Save global page views" setting is enabled in the "Data mining for statistics" module. It's used to store data of page views, like this, People A visited the Product B for 2 minutes. If this table is empty, then you didn't enable any of those settings, it's recommended to keep those settings be disabled.
- Old guests: The "Data mining for statistics" module creates a record for every new visitors stored in this table to store information like browser types, screen resolutions, operating systems, languages, etc
(B1) Customer data
Some amount of customer data will be added to your site every day, like shopping carts, search logs, temp addresses, guests. Those are very important data at the time when they are created, but old abandoned carts, old guests, etc are not important anymore, storing them in the database forever hurts the database performance.
- Abandoned cart: Old abandoned carts are mostly like not going to be re-engaged, so it\'s okay to delete them.
- Temp addresses: Clean out useless temp addresses which don\'t belong to anyone.
- Logs: All logs will be cleaned up, including system logs, admin operation logs, email logs, etc.
- Search history: The "Shop search" module uses this table to store keywords which have been searched on your site.
(B2) Theme data
This is for Transformer theme and Panda theme only to clean up all disabled rows of modules of those two themes. It's very useful if you ever used the One-click-demo-importer, this can effectively remove all disable data from your site.
- Advanced banner module.
- Advanced Custom content module.
- Instagram module.
- Custom links module.
- Newsletter popup module.
- Notification module.
- Owl Carousel slider module.
- Breadcrumbs module.
- Sidebar and mobile header bar module.
- Social share buttons module.
- Stickers module.
- Swiper module.
- Product video and size chart.
Some modules were transplanted to a lot of hooks, but just use a few of them, removing them from unused hooks can spread the front office up for a little.
(C1) Clean up catalog
Keeping expired vouchers, disabled products, out-of-stock products wouldn't hurt much of the database performance, because generally the amount of them is small, but why not clean them up.
- Out of stock products: Delete all out of stock product in one go.
- Expired specific prices: Delete all expired special prices you created on the back office product page, it would waist a lot of time to fine and delete them one by one.
- Expired catalog price rules: Delete expired catalog price rules you created on the "BO > Discounts > Catalog Price Rules" page.
- Expired vouchers: Delete expired vouchers you created on the "BO > Discounts > Cart Rules" page.
- Customers with no addresses
- Customers with no orders
- Delete disabled products
- Delete disabled categories.
- Delete ghost categories: Sometimes some subcategories might not be deleted along with the deletion of their parent categories, it's okay to delete them.
(C2) Disable modules you don't use
It's recommended to disable modules you don't use, here are a list of modules which can be disabled, most of them are statistic modules, disabling them can make your back office load a little bit faster.
(D1) Cron job
Use a cron job to clean up these data form your site database regularly.
- Connection sources.
- Connection page.
- Search history.
- Abandoned carts.
Redundant data could be added to the database due to issues like network interruptions, sever related issues, bugs. Cleaning them out to avoid having more issues.
- Redundant rows from the ps_category_product table: This table links products to categories, there may be some redundant rows after long time of using.
- Redundant rows from the ps_stock_available table.
- Optimize tables: All useless, redundant and bad rows will be removed from these 4 types of tables. 1) All PrestaShop' native optimizable tables (about 150 tables). 2) Tables of Transformer theme and Panda theme (about 40 tables). 3) All tables for the multi-language feature. 4) All tables for the multi-store feature.
Some useful tools you can use to optimize your site.
- Remove useless temporary images: Removing temporary images to free hard dish space. Why there are temporary images files? PrestaShop will upload images to a temp folder to wait for other actions to run, if something goes wrong, then temporary images will be stay there forever.
- Rebuild category tree: If some subcategories not showing out on the front office, then try fixing it by using this tool to rebuild category tree
- Rebuild product positions in categories: If the "Prev" and "Next" module not working on your site, then try fixing it by using this tool to rebuild product positions.
- Uninstall the Merchant expertise module: May help with the back office slow loading issue.
- Stop PrestaShop advertising you modules: Speed the back office loading speed up.
(F2) Google Fonts
This is for Transformer theme and panda theme. Using too many Google fonts would slow the front office down, all google fonts you used on your site will be listed out here for you to delete some of them easily.
- PrestaShop 1.6.
- PrestaShop 1.7.
- All themes.
How to install and upgrade.
The "PrestaShop database cleaner module" can be installed easily without editing any files.
How to install? Use the "Upload a module" button on "BO > Modules" page to install the module.
How to upgrade? Use the "Upload a module" button on "BO > Modules" page to upload the new version to your site, it's the same as how to install, don't worry all your data will be safe, PrestaShop will just upload updated files to your site, it won't reinstall this module.
How to get support.
If you have any questions with this module, post them out on the PrestaShop module support forum, we'll help you out.
Several different reasons could cause the slow loading of back office, so we don't have one-for-all solution, here are some possible solutions you can try.
- Use this database cleaner module to clean out all connection data.
- You can find a feature under the Tools tab in this module to remove the default_country_modules_list.xml file and the must_have_modules_list.xml from the config/xml folde to stop PrestaShop advertising you modules.
- Change protected static $is_addons_up = true; to protected static $is_addons_up = false; in the /classes/Tools.php file.
- Uninstall the Gamification module.
Yes, it's highly recommended that you backup your database before using this module to clear up your database, because you can\'t roll back through a database cleaning up operation. Except you are just going to clean up connection data and logs. We've fully tested the module, but there still has a small chance of going wrong due to your customizations or modules you've installed
Yes, a slight difference is that the module always clean up data from all stores.
Don't close or refresh after you start a clean-up till you see a success/error message.
How long does it take to clean up data depends on two things: 1) The amount of data. 2) Your server performance. We did tests on some live sites, it toke 2 minutes to clean up 1 million connections with the "Keep last" set to 1 month. It toke 3 minutes to clean up 700,000 guests. So if you have a large amount of data, you can set the "Keep last" option to "Clean up all", which short the time needed to be less than 30 seconds.
It's not recommend to uninstall the "Data mining for statistics" module, the module manages the guest feature which is useful and important. So the solution is to clean up data generated by the "Data mining for statistics" module to stop the module slowing your site down.
You can use the PrestaShop's native free Cron tasks manager module. Since most servers provide a server side cron job feature, so we recommend you to use the server side cron job feature to run Cron jobs, because of server side cron job feature is more reliable than the Cron tasks manager module.