Sales modules and database optimisation for a video game shop

Perfect Blue sells games and accessories for consoles. The shop has several thousand products, and its traffic depends on new releases and pre-orders. Ready-made WooCommerce plugins could not do what the client expected. The shop needed lists of new releases, titles back in stock and price drops calculated from the actual reduction rather than from a crossed-out price, both on the home page and on separate pages, with tabs for each platform as in the client’s mock-up. Speed was a separate issue. With several hundred thousand entries in the order tables, the shop responded slowly to logged-in customers.

I wrote a plugin in which every module is a separate part that can be switched off:

• Release calendar: a monthly grid and a list of upcoming titles, built from the release dates stored with the products.
• Price drops and items back in stock: the plugin adds its own timestamps to products. A timestamp is created when the price really drops or an item comes back in stock, so the lists show what actually happened.
• New arrivals and bestsellers: bestsellers come from orders placed in the last 30 days, split by platform, with a separate tab for pre-orders.
• Sorting: sold-out products move to the end of every list, also after filtering.
• Settings: the client changes the section names, the order of the tabs and the limits without my help.

I developed the modules in a test environment, with automated tests and code quality checks on every change, and before the code went to the live shop, it passed a separate security audit. The performance audit ran in parallel. I reduced the amount of data loaded every time a page opens and sped up the sorting of product lists, so the shop now searches a few thousand rows instead of more than ten million. With the cache settings I chose, the prices in the modules are always up to date and the shop does not rebuild hundreds of product cards on every visit.