Problem Summary
I have a page on a web application in PHP when the page loads that stalls the page quite a bit on load. Currently there is a query, that fetches records on a page, individual records for a page, that amounts to about 160 records. When I add a query in this loop record fetch (the query underneath), each record fetch takes about 20-30 seconds on individual fetch.
Current Configuration
Our application does not own or host the database and it merely can query for records in the web PHP application, and we do not have access to add indices or change stored procedures, etc...
Bottleneck Query
SELECT t1.tablefield,
t1.tableid,
t1.fieldname as license
t2.fieldname as state
t3.fieldname as excluded
FROM
tableofdata t1,
tableofdata t2,
tableofdata t3
WHERE
t1.org = :location
AND t2.org = :t1.org
AND t1.customer = t2.customer
AND t1.sequence = t2.sequence
AND t1.sequence2 = t2.sequence2
AND t3.org = t2.org
AND t3.customer = t2.customer
AND t1.code = 'license'
AND t2.code = 'state'
AND t3.code = 'status'
AND t1.value = :license
AND t2.value = :state
AND t3.value = 'Excluded';
How do I fix this?
A) Optimize Query?
- Do I try speeding up either the query (modifying self joins / predicates, composing a long predicate condition of record values) if feasible, can speed up the page to load faster? Right now code is not active but I have queried by individual values in a query browser and each record fetch takes ~20 seconds.*
B) Use Asynchronous Select PHP Select Statements?
- Should I try to fork out asynchronous selects in PHP to produce records in the loop and join the results into a MySQL table that I do have root access to?
C) Load data from MySQL / Synchronize with Oracle, using a Scheduled Task/Cron Job?
- Should I save all data from Oracle data in bulk to MySQL & run a synchronization script in a scheduled task (every 30 minutes or so)?
