Why MySQL Join Query Slowed to 30,000 Seconds and How Restoring an Index Fixed It

The author built a test‑service function that retrieves a login_id and user_id from two tables in separate databases using a simple SQL query. Initially the query executed quickly.

One day a tester reported that the same query took more than 30,000 seconds, prompting a deep investigation.

First, the author considered network latency because the workstation connects to the corporate network via VPN. Running the program locally and on the internal server produced similar timings, so network issues were ruled out.

Next, MySQL server metrics were examined; all load indicators appeared normal, eliminating server‑side performance problems.

The focus then shifted to the SQL statement itself. The query used was:

SELECT l.login_name,u.user_id,l.login_id,u.sex FROM alpha_login.login_info l LEFT JOIN alpha_user.user_info u ON l.login_id = u.login_id WHERE l.login_id= " + id + " OR u.user_id = " + id + ";

Running the statement directly in Navicat also exhibited the extreme delay, confirming that the issue was not a client‑side artifact.

When the join was removed and a single‑record lookup was performed, the response time dropped to a few hundred milliseconds, indicating that the join operation was the bottleneck.

Further inspection of the two tables (each under 100,000 rows) showed no obvious structural problems. However, checking the user_info table revealed that, aside from the primary key user_id, only a single index on user_id existed. The expected composite index on user_id and login_id had been missing, likely removed inadvertently.

Solution: recreate the missing composite index on user_info. After restoring the index, the join query returned to sub‑second performance, resolving the 30,000‑second slowdown.