PHP and MySQL Optimization

MySQL database containing records of 2000 staff members and their duties for the past three years. Current PHP code, spanning around 300 lines, allows searching for staff members and their respective duties. However, the execution time is excessively long, taking approximately 7 seconds to complete the search for each staff member. Objective: The primary goal is to optimize the existing PHP code to significantly reduce the execution time required for searching staff members and their duties in the MySQL database. Requirements: 1- Performance Optimization: The optimized solution should dramatically reduce the execution time compared to the existing 7-second delay per search. Consider employing advanced MySQL querying techniques and PHP optimizations to achieve faster processing. 2- Algorithmic Efficiency: Analyze the existing PHP code to identify potential bottlenecks and inefficiencies. Refactor or redesign algorithms to improve efficiency without sacrificing accuracy. 3- Database Indexing: Implement proper indexing strategies in the MySQL database to enhance query performance. Identify key fields for indexing based on the search patterns and data access requirements. 4- Code Refactoring: Review the existing PHP codebase to identify areas for improvement, such as redundant operations, unnecessary loops, or inefficient data structures. Refactor the code to streamline execution and improve readability while maintaining functionality. 5- Query Optimization: Optimize SQL queries to leverage MySQL's query optimizer effectively. Utilize appropriate SQL clauses, such as WHERE, JOIN, GROUP BY, and ORDER BY, to minimize data retrieval and processing overhead. 6- Testing and Benchmarking: Conduct rigorous testing to ensure the optimized solution meets performance expectations. Benchmark the optimized code against the original implementation to quantify performance improvements. Deliverables: 1- Optimized PHP code for searching staff members and their duties, significantly reducing the execution time. 2- Documentation detailing the optimizations applied, including changes made to the codebase, database schema, and query strategies. 3- Test reports and benchmarks demonstrating the performance improvements achieved through optimization efforts. Features: - Data Filtering: The central task for this project is to help in filtering staff duties data in an efficient and user-friendly manner. You are expected to create an intuitive UI that allows easy data querying. Ideal qualifications: - Proficiency in mySQL and PHP