Maintenance of the Database in PrestaShop for Everyone

A database (DB) is a set of data belonging to the same context and systematically stored for later use. In PrestaShop the database contains the registry of everything that happens on our site that must be stored. Either a catalog, customers, addresses, carts, orders, carriers, messages, configurations, in short, practically every fragment of information on our site.

How does the database work in a PrestaShop store?

As mentioned before, all PrestaShop information is stored in the database, except for physical files such as images, PDFs, templates, etc. The database is divided into tables that contain information about an entity in PrestaShop (example: cart, customer, address, order) and are in turn related to other tables. All information is stored or retrieved through "queries" to these tables, which offer extensive flexibility and power to manage the information.

To make it easier to understand, let's make the following analogy that we will use in the future throughout the article. Let's think that the database is a book in constant writing, change and adaptation. This book has an index to organize hundreds or thousands of pages, and every day we add more.

Why should I optimize the database of my store periodically?

Not everything we write in our book is finally going to be published, we also include drafts or ideas. On the other hand, as time goes by, many of its pages become obsolete with information that is no longer relevant. In addition, as we write anywhere in our book, the index is becoming increasingly complicated to understand and therefore more difficult and slower to find specific information in the book. That's why our book needs to be cleaned, reorganized and reindexed periodically.

Maintaining a database contributes to greater cleaning of data, improving the loading speed of your store and smaller size of backups. There are several elements that you must take into account, mainly: the storage engine of the tables, obsolete data and the indexes of the tables.

What is the storage engine of tables?

PrestaShop uses MySQL as a database engine, a software widely used worldwide in perfect duo with PHP mostly for web developments. Within a MySQL database each table can have its own data storage engine. We will not delve into the details of each one that you can find in the official documentation, just mention that the 2 most used are InnoDB and MyISAM. InnoDB is the recommended storage engine in most cases, for its flexibility, consistency and performance during read and write operations on tables. On the other hand, MyISAM is generally used in tables whose operations are mostly read-only, since due to its characteristics it has worse performance in write operations.

Most tables in PrestaShop have read and write operations. Therefore, all the native tables of PrestaShop database have InnoDB as a storage engine. This is the general correct recommendation. However, some versions of MySQL have MyISAM as the default storage engine. This means that if a new table is created, for example through the installation of a module, and the desired search engine is not specified, then that table will take MyISAM as a storage engine.

The solution, to avoid poor performance in the queries to the database of our store, is to verify the storage engine of each table and put InnoDB to everyone that does not have it.

How to identify obsolete data?

There is information that is stored in the tables of a store, which really has no significant relevance after a few days. However, there is no process that natively performs the automatic deletion of this data that becomes continuously obsolete. This does not prevent our site from working properly, but it definitely has a negative impact on the total size of the database and especially on the speed of execution of queries, mainly those of obtaining data. It is not the same to look for a word within a 100-page book as within a 50-page book.

Now, not all information is eliminable. There is data that has historical importance and we should never lose, for example the registration of customers, their addresses and orders. So how do we identify which data we can delete and which we can't? First we must identify which tables contain information that loses relevance over time. Some suggestions, among several others to consider, would be:

• Abandoned shopping carts
• Discounts completed
• Old visitors
• Connection statistics
• In-store search statistics
• Error logs
• Emails sent

But not all information related to these entities becomes obsolete immediately. For example, if we want to delete carts, we must verify that said cart is really abandoned (that it does not have an associated order) and that a reasonable time has elapsed since its creation that does not affect the recoverability of abandoned carts of our store. In the case of connections and statistics something similar happens, we must consider an elapsed time where their respective data no longer has practical relevance to us.

Additionally, we must take into account what I would call residual data. Many times we remove some language from our store, one of the stores in our multi-store or some customer group, just to name a few examples. This type of data has to do with many tables within our database, not just one or two. It happens that, when you delete them, the associated values in all the remaining PrestaShop tables are not always deleted. This type of residual data, related to values already removed from the store, should also be reviewed in order to contribute to the cleaning of the database.

What are the indexes of the tables for?

Well, as in a common book, an index is a tool that allows us to access the final location of certain information more quickly. For an index to fully accomplish its function, it needs to have a precise structure and organization. The constant change and writing in our book makes the index lose effectiveness and needs to be reorganized from time to time.

In a database the indexes work in a similar way, and constitute a kind of direct access to the information we try to obtain from our tables, making our searches much faster. But the progressive writing (insertion, modification and elimination) in the database tables causes the associated indexes to deform or deteriorate and lose efficiency. For this reason, from time to time (depending on the volume of data saved in the database), it is necessary to reconstruct the indexes of the tables of our database.

This is a relatively simple task to carry out if we use a database management client such as phpMyAdmin, which is the most common on all web servers. We only have to optimize the tables in our database. And this will not only make our queries faster, but in most cases it will reduce even the total size occupied by the table on the server's hard drive. I leave you a real example:

• Before optimizing
  • See:
    
  • Table size:
    
• After optimized
  • See:
    
  • Table size:
    

This optimization does not include cleaning obsolete data on this table. If cleaning is applied prior to optimization, the total size could be reduced even to less than half of the original.

IMPORTANT: The process of optimizing a table can take several minutes, depending on the size of the table, number of records you have and processing capacity of your server. It is recommended to run this process during the store's low traffic hours or when it is undergoing maintenance.

How to do it if I'm not an expert in DB?

If you don't know much about databases, or don't know which tables to touch or which data to delete, I invite you to use our free module PrestaShop Extra Database Cleaner. In it you will find a section for optimizing your database. It includes the option of specifying the time intervals in which you consider that the information for each entity is still relevant, so that only the really obsolete data is eliminated. With the possibility, in addition, of creating a scheduled task to do the cleaning automatically and periodically.

Always keep in mind that cleaning processes can take several minutes, especially in databases that have never been optimized before. Therefore try not to run them at times of peak traffic in your store. It is also possible that the first times the process gives you timeout and you must run it again until your database is completely clean. All that will remain later is to optimize the tables from your phpMyAdmin.

The DB of your store will be clean and revitalized, I guarantee it!