Query taking 1 minute to run

Hey there!

On a 1GB RAM / 1 vCPU Droplet, it’s not too surprising that a query joining large tables is taking a while, especially if one table has 2.5 million records. Even if you’re just joining on primary keys, MySQL still needs some breathing room to process everything efficiently.

I’d recommend starting with an EXPLAIN plan to see if the indexes are actually being used. Sometimes it looks fine on paper, but MySQL might be doing a full table scan behind the scenes.

Also, keep in mind that with DBeaver or any external client, the query results have to travel across the network, so that adds a bit of latency too.

If this is more than just a dev/test setup, upgrading the Droplet to something like 2GB RAM and 1 or 2 vCPUs can help a lot. Even small bumps in resources can drastically improve query performance.

- Bobby

Heya, @775c69c916d345a7a367d867078ccb

You’re right — that query should not be taking a full minute if you’re joining on indexed primary keys and limiting the results to 1000 rows.

Run this before your query:

EXPLAIN SELECT ...;

It’ll show if any table is doing a full table scan instead of using an index. If you see "type: ALL" in the plan for any table, that’s your bottleneck.

Regards

Apart from what has been said, Ensure Indexes Are Present on Join Keys.

For all join conditions like:

ON a.id = b.a_id

Make sure:

• a.id is a PRIMARY KEY

• b.a_id has an INDEX

For your large tables:

-- For 2.5M record table
CREATE INDEX IF NOT EXISTS idx_table1_fk ON table1(foreign_key_column);

-- For 500K record table
CREATE INDEX IF NOT EXISTS idx_table2_fk ON table2(foreign_key_column);

Also, If you’re using LIMIT 1000 without an ORDER BY, the DB might still process all joins before applying the limit.

ORDER BY some_indexed_column
LIMIT 1000