Performance problems with complex SQL SELECT queries

[kenatsun]

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
~ Ken

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:
SELECT p.worker_id,
p.worker as worker_name,
p.job_name,
o.open_offers_count
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

[benanamen]

The problem is your query. You need to use a JOIN.

[kenatsun]

Solved. I restructured my code so that, instead of repeated execution of its complex SELECT queries (with different args for each execution), it executes each of these complex queries just once at the start of each run, stashing the result into a table. Then the repeatedly executed queries (with different args for each execution) are executed against these stashed tables. That way, the complex joins only have to be executed once. It now runs – literally –100x faster, which is the level of improvement I needed to get this page to work at all.

[ErnieAlex]

Well, if it works…

But, as Benanamen mentioned, a simple JOIN would do it quickly, too. You do not have a join in your query. It is just has two tables named, not a real join. FROM table1, table2 is NOT a join, it’s just two tables.
A JOIN uses indexes to join two tables and therefore are much much faster than complicated WHERE’s.