Available for projects & agency overflow · Quick reply, from the person who does the work

Send a message contact@allaux.fr
FR EN TR
allaux.
Emergency Discuss my project

Database and index: definitions

A database is the system that stores and organises, in tables, all the information behind a shop: products, orders, customers and configuration. An index is a structure placed on those tables so the database can find a row without scanning them all.

Describe my issue Send a message

The shop's invisible core

PrestaShop and WooCommerce store their data in a relational database, usually MySQL or MariaDB. Each type of information lives in a dedicated table made of rows and columns: a products table, an orders table, a customers table, linked together by identifiers. This structure is what lets you instantly retrieve every order placed by a given customer.

For an online seller, the database is the most critical part of the site: the theme and modules can be reinstalled, but order history and product records only exist in the database. That's why a proper backup always includes the database, separate from the files, and why simply copying files is never enough to restore a working shop.

The index, or why the same database answers fast or slow

An index is a structure that speeds up searching a table, much like a book's index lets you find a page without reading the whole thing. Without an index, the database scans the table row by row to find what's asked for. With an index on a frequently searched column, for example id_product, it reaches the relevant rows directly. On a catalogue of several tens of thousands of products, a query that took several seconds can drop to a few milliseconds.

This gain isn't free: every index takes up disk space and slightly slows down writes, since the database has to update it on every row change. So not every column gets indexed, only those actually used by frequent searches. Missing indexes on the right columns is a classic cause of slowness on a catalogue that has grown large; too many indexes is another, less visible one.

Frequently asked questions

Can you edit data directly in the database?
It's possible with a tool like phpMyAdmin, but it's risky without precise knowledge of the table structure: a poorly targeted change can corrupt orders.
Why does the database sometimes slow a shop down?
With a large catalogue, certain queries become slow if the tables lack suitable indexes or have never been optimised.
How do you know if a table is missing an index?
By first spotting the slow queries, through PrestaShop's profiler (_PS_DEBUG_PROFILING_) or MySQL's slow query log. Neither the profiler nor debug mode tells you a query scans the whole table: you read that by prefixing the query with EXPLAIN and looking at the type column (ALL means a full scan) and the key column (NULL means no index was used).
Does adding an index require taking the shop offline?
Generally not on a reasonably sized table, but on a very large table it can temporarily lock writes: it's best done during off-peak hours.
Is the database hosted in the same place as the site's files?
Usually yes with a standard shared host, but some setups separate the database server from the web server for performance reasons.

Describe your need in one minute

A few targeted questions so I can reply with an estimate rather than another questionnaire.

contexte
plateforme (facultatif)
question (facultatif)
Please provide an email or a phone number so I can get back to you.

Please provide an email or a phone number so I can get back to you.

Related pages