Database Optimizer [NAT]: Keep Your Store Blazing Fast

Compatibility: OpenCart 2.3.x, 3.0.x, ocStore 2.3.x, 3.0.x, OpenCart 3.0 Cloud

DEMO:
Login/Password: demo / demo
ACCESS ADMIN PANEL (DEMO)




The Hidden Problem of OpenCart Databases
Over time, your OpenCart database quietly accumulates hundreds of thousands (sometimes millions) of useless rows. Expired sessions, abandoned carts from guests, endless search histories, and old user activity logs bloat your database. This leads to slow SQL queries, failed backups, increased hosting loads, and a sluggish storefront.

100% compatibility with Journal 3
The module dynamically locates fragmented Journal cache tables and defragments them, speeding up database response times without disrupting the theme's functionality!

What does Database Optimizer [NAT] do?
It is a comprehensive 7-in-1 maintenance tool that safely cleans up database garbage, compresses tables, creates smart chunked backups, manages heavy system logs, fixes data anomalies, detects missing indexes, and most importantly — features a built-in **Slow Query Analyzer (EXPLAIN)** to pinpoint the exact cause of database bottlenecks.

7 Powerful Tools in One Module:

1. Smart Garbage Cleanup

• Set flexible retention limits (e.g., keep sessions for 14 days, carts for 30 days, search history to 1000 rows).
  
• The module scans your DB and shows exactly how many junk rows you have.
  
• Safely delete millions of useless records in one click to drastically reduce your database size.
  

2. Smart Database Backup (NEW)

• Export large databases without server timeout errors or memory limits.
  
• AJAX chunking: Tables are exported step-by-step (2000 rows per request), bypassing PHP memory (`memory_limit`) and execution time restrictions.
  
• On-the-fly Archiving: SQL dumps are automatically packed into lightweight ZIP archives to save traffic.
  
• Download the ENTIRE database in 1 click, or select specific tables to export (each in a separate .sql file inside the archive).
  

3. Critical Indexes Analyzer

• Missing indexes are the #1 reason for slow OpenCart stores.
  
• The module analyzes key tables (products, categories, orders, customers, seo_url) and suggests missing database indexes.
  
• Click "Add Index" to instantly speed up your SQL queries and product filtering without writing any code.
  

4. Table Compression (Overhead)

• Deleting products or categories leaves fragmented, unused space (Overhead) in your database.
  
• The module finds tables with wasted space and runs an OPTIMIZE TABLE command to reorganize physical storage and free up disk space.
  

5. System Logs Manager

• Stop downloading huge log files via FTP just to clear them.
  
• View all files in your storage/logs/ directory directly in the admin panel (both .log and .txt formats supported).
  
• Read log contents and clear giant error files manually in one click, or set up fully automated clearing via Cron schedule.
  

6. Advanced Anomaly Scanner
A professional auditing tool to detect and fix logical database errors often caused by unstable imports or manual edits.

• Orphaned Data (NEW): Detect and safely delete "ghost" entries in related tables (product_image, product_attribute, review, etc.) where the main product no longer exists.
  
• Broken Images (Main & Additional): Scans your server storage to find products that have image paths in the DB but the physical files are missing.
  
• Stock Logic Audit: Find and fix products with 0 quantity but "In Stock" status, or products with negative stock values.
  
• Order Minimums: Detect and fix invalid minimum order quantities (<= 0).
  
• Price & Discount Audit: Identify products with zero/negative prices or invalid Specials where the discount is higher than the main price.
  
• Missing Data: Locate products without assigned Manufacturers or Categories.
  
• System Flags Control: Bulk verify "Subtract Stock" and "Requires Shipping" settings for your entire catalog.
  
• Date Control: Detect products with "Date Available" set in the future or expired Special offers.
  
• Duplicates: Find clone entries by SKU, Model, and overlapping SEO URLs (aliases).
  
• Direct Management: Quick-action buttons to Edit in Admin or View on Storefront directly from the results list.
  

7. Slow Query Analyzer & Auto-Indexer (EXPLAIN) - NEW!
KILLER FEATURE: Stop guessing why your store is slow! A built-in professional database profiler that hunts down bottlenecks and fixes them for you.

• Intelligent Auto-Suggestions (Magic!): The module automatically parses complex slow queries (WHERE, JOIN, ORDER BY) and identifies exactly which indexes are missing. You do not need to be a DBA — just click the "Create Index" button to resolve the bottleneck instantly!
  
• EXPLAIN Execution: Run a deep MySQL EXPLAIN analysis on any slow query directly from the admin panel with one click.
  
• Safe DB Proxy Logger: Intercept and record any SQL query taking longer than your specified threshold (e.g., 0.5 seconds). Uses 100% safe "Magic Methods" proxying with auto-disable protection via Cron to prevent server overload.
  
• Smart Filters: Easily filter logs to display "Full Table Scans", "Optimal" queries, or queries with actionable suggestions.
  
• False-Positive Detection: It recognizes queries that select all data without conditions, or queries on tiny tables where MySQL intentionally ignores indexes, preventing unnecessary database clutter.
  

Automation & Engine Optimization

This module includes built-in cron support, allowing you to automate maintenance tasks without manual intervention. You can configure it to automatically clean up database garbage and delete bloated system log files based on your retention limits.

It also provides a safe and efficient conversion of database tables from the outdated MyISAM to the modern InnoDB engine. The conversion process runs through a step-by-step AJAX queue (chunks), which guarantees it won't crash or timeout even on tables with millions of rows. This significantly improves performance under load, prevents table locking during checkout, and increases overall database stability on live stores.

Other Modules in the [NAT] Series:

• Database Optimizer [NAT]: [Current] Intelligent DB garbage cleanup, smart backups, system log management, slow query analysis, and critical indexer to speed up your site.
  
• Catalog Optimizer [NAT]: Automatically hide empty categories and brands, push out-of-stock products to the end of the list (SEO pessimization), and clean up unused attributes.
  
• Image Optimizer [NAT]: Compress and resize original photos to WebP and bulk delete unused or junk images from your hosting server.
  
• Domain Scanner [NAT]: Search and safely replace external links and HTTP images in your database and theme files. Essential for HTTPS migration.
  
• New Arrivals [NAT]: Display products by true chronological sorting (Date Added). Features auto-generated SEO pages, smart widgets, and date archives.
  
• Poll Widget [NAT]: Create simple polls, custom voting forms, and view real-time statistics directly on your store pages to boost customer engagement.
  
• Easy Import/Export [NAT]: Fast import/export of prices and stock from xls/xlsx/csv/xml/json files or via link. Powerful field mapping and Cron support.
  

Terms & Conditions (License & Support Policy)
By purchasing and downloading this extension, you strictly agree to the following terms:

• Single Domain License: One purchase allows you to install and use the extension on exactly one (1) live domain and one (1) local/test environment. You need a separate License Key for each additional live project.
  
• License Activation: The module requires a License Key to function. Please email info@nat.od.ua with your Order ID and Domain Name after purchase to receive your key.
  
• Support: We provide free technical support for bugs and standard installation issues. Support is available via email. Please allow up to 24-48 hours for a response.
  
• Compatibility: The module is guaranteed to work on standard OpenCart / ocStore installations. We cannot guarantee out-of-the-box compatibility with heavily customized core files or third-party checkout/theme modules, though we will do our best to assist you.
  
• Refund Policy: We offer a 100% refund if the extension is completely non-functional on a standard OpenCart setup and our support team is unable to fix it. We do not issue refunds for "change of mind" or if you have already requested and received a domain license key.
  
• Distribution: The code is open-source (no IonCube), meaning you can modify it for your own needs. However, you may not resell, redistribute, share, or publish this extension in any form.