Help needed in optimizing a PHP script that processes data from two large MySql tables (about 400,000 records each). Table A contain records with a field value that is derived from running a query against Table B. The first query contains AVG, COUNT, and SUM functions for matching records. The results of this query are then used as inputs to a simple formula, which is then subsequently UPDATED back to TABLE A. Typically, this process will run once per day, and will calculate new field values for about 15,000 records for Table A, by querying a universe of about 400,000 records in Table B. We recently ported our system from Visual Foxpro to a MySQL /PHP environment, and although we have this portion of the project completed, the performance in the new environment is dramatically slower than the Visual Foxpro environement. This process previously took about 10 minutes to accomplish in VFP; it now takes over 5 hours to run in MySQL/PHP. We are somewhat new to the PHP/MySQL environment, and we need someone to optimize this process for us. Attached is a code snippet.
## Deliverables
1) All deliverables will be considered "work made for hire" under U.S. Copyright law. Employer will receive exclusive and complete copyrights to all work purchased. (No 3rd party components unless all copyright ramifications are explained AND AGREED TO by the employer on the site per the worker's Worker Legal Agreement).
2) We would like an optimized PHP script and MySQL query, and/or help with optimizing the schema to speed this processing up.
## Platform
Windows 7 64-BIT Apache 2.2.14 (IPv6 enabled) + OpenSSL 0.9.8l MySQL 5.1.41 + PBXT engine PHP 5.3.1