Client

Litesource

Task

Speed Optimization

Project Overview

Litesource is an inventory management system built with PHP, Codeigniter, MySQL, and Apache server for the backend, and Vanilla JavaScript and jQuery for the frontend. Inventory management is the practice of overseeing and controlling the ordering, storage, and use of components that a company uses in production. As the project scaled up, one of the key challenges we faced was optimizing the system's speed to provide a seamless user experience.

Challenges
  • Low latency in the system
  • Inefficient performance of the system
  • Delay in the data processing
  • Issues with data integrity and system reliability
  • User dissatisfaction

Solution

To optimize the system’s speed we focused on two things, Mysql query optimization, and cache management.

  1. MYSQL Query Optimization
    To optimize the system’s speed, we focused on MySQL query optimization. We took the following steps:

    • Indexing: We identified various tables that were used in long subqueries and joins and added indexes to them. Indexing these tables helped improve the query performance significantly.
    • Query Refactoring: We refactored various SELECT statements and joins, replacing subqueries with joins where possible. This helped reduce the number of queries and improved query performance.
    • Stored Procedure: We also used stored procedures to optimize the database operations. Stored procedures helped us reduce network traffic by allowing us to execute multiple SQL statements with a single call. This helped improve the overall performance of the system.
  2. Cache Management
    We also implemented a cache management system using Redis to improve the system’s speed. We took the following steps:

    • Trigger: We created a trigger in a few tables that are frequently updated. As soon as new data is inserted or updated, the trigger calls a stored procedure that adds the data to the schedule table.
    • Cron Job: We set up a cron job that reads the schedule table every two seconds and updates the Redis cache if anything has changed in the database.

By using Redis as a caching layer, we were able to reduce the load on the database and improve the system’s speed. The cache management system also helped ensure that the system always showed the latest data to the users.

Conclusion:

By implementing MySQL query optimization and a cache management system using Redis, we were able to significantly improve the inventory management system’s speed and provide a seamless user experience. Our changes resulted in a faster and more reliable system that was able to handle a large amount of data and provide real-time updates to the users.