I have a PHP app that accesses a SQLite database using PDO (PHP Data Objects). One page in this app requires repeated execution of some complex SELECT queries (with different args for each execution). My problem is that each execution of one of these SELECTs takes 1.3 - 2.5 seconds, and a real-world-sized page requires 100 or more executions – so the app times out long before it can return anything. Here are some things I’ve tried:
- I’ve established that the PDO statements that execute the query (PDO::query() or PDO::execute()), rather than anything else in my code, are what gobble up all the time.
- I’ve run these queries in my database management tool (SQLite Studio). A query that takes 1.3 seconds when submitted from my PHP app completes in 0.2 seconds in the DB tool. This tells me that it’s the PDO functions, not the SQLite database, that are taking most of the time.
- The PDO manual (PHP: PDO::query - Manual) says that a repeatedly executed SELECT will run faster if you prepare it just once using PDO::prepare() and then execute the prepared statement each time with PDO::execute(), rather than doing each execution with PDO::query(), which prepares and executes it each time. Not so, in my case: Each PDO::execute() took just as much time as each PDO::query().
I can provide code snippets illustrating this stuff if they would help. At the end of this post I include one of the queries.
So my question is whether there’s anything I can change that would seriously improve the performance of these queries. Maybe different settings or args for the PDO objects? Maybe replace the SQLite database with Postgres or MySQL? Rewrite the quesries in some way? Maybe something else I haven’t thought of?
~ Thanks in advance
FYI, here’s an example query. It takes 1m30s to execute. The “possible_shifts_for_workers” and “open_offers_count” in the FROM clause are views, already installed in the SQLite database:
p.worker as worker_name,
FROM possible_shifts_for_workers p, open_offers_count o
WHERE p.shift_id = 2311 – This shift.
and p.season_id = 11 – This season.
and o.worker_id = p.worker_id – The open offers of this worker
and o.job_id = p.job_id – to do this job
and o.season_id = 11 – in this season.
ORDER BY open_offers_count desc, pref desc, worker asc