So you have a Prestashop store, that’s slow. And you’re not a developer. But you’d like to improve its performance. Assuming everything works fine and there’s not a severe problem with your store, and especially if a while ago everything was fast, you might want to look over to your database.
Some of your Prestashop store’s performance might be brought back by constantly emptying some tables that hold pointless data. And you can go even further if you don’t need some statistics.
Now, mind you, there are plugins that do this. But most of them are not free. So if you just want a cheap (as in free) way to free up your database of pointless stuff, read on.
Depending on your server’s hardware and how much traffic you have, you’ll want to keep an eye on these and clear them up once in a while. Usually once every other month if your traffic is not that high.
How to do a free and fast database cleanup/maintenance?
You’ll need access to Phpmyadmin or a similar tool. Usually found in your hosting account, in Cpanel/Webuzo/Plesk or whatever your server is running. Or hey, you can even run some commands via command line after logging into Mysql but… if you can do that I presume you don’t need this guide.
The things below apply to Prestashop 1.6.x and 1.7.x.
The easy way
1. Log in to Phpmyadmin and select your store’s database. (if you don’t know its name, you can find id via FTP or File Manager by going to public_html/config/settings.inc.php and look for define(‘DB_NAME’, ‘NAME’);)
2. Click on Rows to sort the tables by the number of rows. It will be easier to spot the problems.
3. Select the tables you want to clear by clicking on their checkboxes on the left.
4. At the bottom of the page choose: With Selected: Empty.
5. Click yes and done.
The SQL tab way:
After selecting the database just like in ‘the easy way’ above, you’ll see a SQL tab on the top. Just click there and paste the code from below that applies to your case. Then go and yes. All good.
So what tables can we clear to optimize performance?
Well, it really depends. There are a few tables that are utterly pointless and can be safely emptied without losing any data. And then, there are some that hold some statistics that you might find useful or not, depending on how and if you use tools like Google Analytics and Google Search Console. (you should use them, amazing free tools)
So I’m going to list below what can you clear and what those tables do.
Safe to clear, every time
These tables are pointless. Just clear them and enjoy a faster database. I’ve seen stores with >1 million rows here. On shared hosting! Needless to say, the cleanup was very well received.
The tables are:
Or if you prefer to empty them by the SQL tab:
TRUNCATE TABLE ps_connections; TRUNCATE TABLE ps_connections_source; TRUNCATE TABLE ps_connections_page; TRUNCATE TABLE ps_guest;
note: truncate = empty in SQL in case you are concerned 🙂
Safe to clear, at the expense of some statistics
If you don’t use the Prestashop dashboard for the metrics below, you can safely clear them or the ones that you don’t need.
- ps_pagenotfound – holds the not found URL’s in your store. You will most likely find these in the Google Search Console;
- ps_log – some logs regarding who did what. Who edited a product, changed a setting, etc. It might be useful if you have multiple admin users/employees. If not, empty it.;
- ps_statssearch – search data. Basically information about what searches were performed by customers. If set up ok, you should have this data in Google Analytics. Anyway, you might want to clear this one if it’s huge. But take note of what most people are searching for, you might find useful stuff.
That’s about it! Even with zero coding or developer experience, you can do this. It takes a couple of minutes at most, and it might prove to be useful in certain scenarios. And hey, it’s free!
I hope it helps.
Tech jack of all trades. Programmer, sysadmin and a general tech enthusiast. I work with a lot of stuff, and love learning and talking about new or interesting stuff.
2 thoughts on “Prestashop performance – quick & easy win with database cleanup”
I have also the table ps8n_layered_price_index very big, what is it?
That is the table that holds the required data for the price filter to work on a category page. (that price slider that allows people to see products between two target prices)
You should leave that one alone probably unless you don’t want to use the price filter.
Comments are closed.