MySQL Database Optimization
Learn how to optimize your OpenCart store database for maximum performance
Contents
Why is optimising the database important?
The database is the heart of your OpenCart store. An unoptimised base can lead to:
- Late loading of product and category pages
- Delays in order processing
- High use of server resources
- Performance problems in high traffic periods
InnoDB vs MyISAM
OpenCart supports both types of MySQL storage machines, but InnoDB offers significant advantages:
Comparison of Characteristics
| Feature | InnoDB | MyISAM |
|---|---|---|
| Transactions | ✓ | ✗ |
| Lock on line level | ✓ | ✗ |
| Automatic Recovery | ✓ | ✗ |
| Referential integrity | ✓ | ✗ |
Recommended setting
For new OpenCart facilities, we recommend the use of InnoDB for all tables. For existing facilities, you can convert the tables with the following command:
ALTER TABLE table_name ENGINE = InnoDB; Indexes Optimization
Properly designed indexes can dramatically improve the performance of questions.
Key tables that need indexes:
- oc_product (product_id, model)
- oc_product_description (product_id, name)
- oc_category (category_id, parent_id)
- oc_order (order_id, customer_id, date_added)
# Example of adding a complex index ALTER TABLE oc_product_description ADD INDEX product_search (product_id, name(255)); Attention!
Excessive indexes may slow INSERT and UPDATE commands. Add indexes only to columns often used in searches.
Optimizing Questions
Optimize OpenCart SQL questions for best performance:
Best practices:
- Use EXPLAIN to analyze questions
- Avoid using SELECT*
- Limit results with LIMIT
- Use JOINs correctly
# Example optimized query SELECT p.product_id, pd.name, p.price FROM oc_product p INNER JOIN oc_product_description pd ON (p.product_id = pd.product_id) WHERE pd.language_id = 1 LIMIT 20; Maintenance of a Database
Regular maintenance to maintain performance:
Basic maintenance work:
- Regular backup of base
- Optimising Tables
- Clear old data
- Refresh statistics
# Commands maintain OPTIMIZE TABLE oc production, oc Category? ANALYZE TABLE oc production, oc Category; DELETE FROM oc cart WHERE date added < DATE SUB(NOW(), INTERVAL 30 DAY)? Monitoring Tools
Use tools to monitor the performance of the database:
- MySQL Slow Query Log
- phpMyAdmin Query Analyzer
- MySQL Workbench
- Percona Monitoring and Management
Ready to optimize your database?
Contact OpenCart Greece for a professional analysis and optimization of your store database.