执行指定SQL查询时出现#2013错误：与MySQL服务器连接中断

Hey there, let's tackle this frustrating #2013 connection loss issue with your query. I’ve looked over your SQL, and there are a couple of key problems that are almost certainly making your MySQL server choke and drop the connection.

What’s Wrong With the Original Query? #

The main culprits are a logical flaw in the subquery and an inefficient query structure:

• Your subquery references employees.username LIKE '%bha%' but doesn’t join call_history to employees in that subquery. This forces MySQL to create a huge Cartesian product (matching every call_history row to every matching employees row without any real connection), which instantly blows up memory usage and triggers a timeout.
• Using IN with a grouped MAX(id) subquery is really slow for large datasets. MySQL has to first generate all the max IDs, then match them back to the outer query—this drags on and uses way more resources than necessary.

Optimized Query to Fix the Issue #

Let’s rewrite the query using joins instead of subqueries, and fix the logical flaw to avoid that massive unnecessary dataset. Here’s a better version:

SELECT 
    cl.id,
    cl.contact_number,
    ch.id,
    ch.user_id,
    ch.contact_number,
    ch.call_type,
    ch.call_duration,
    ch.call_date,
    ch.call_time,
    ch.audio_file,
    e.username
FROM call_history ch
JOIN contact_list cl ON cl.contact_number = ch.contact_number
JOIN employees e ON e.id = ch.user_id
-- Join to get only the latest call per contact number
JOIN (
    SELECT contact_number, MAX(id) AS max_call_id
    FROM call_history
    -- Make sure we only include calls from matching users
    WHERE EXISTS (
        SELECT 1 FROM employees e_sub 
        WHERE e_sub.id = call_history.user_id 
        AND e_sub.username LIKE '%bha%'
    )
    GROUP BY contact_number
) ch_max ON ch.id = ch_max.max_call_id
-- Double-check the user filter to ensure accuracy
WHERE e.username LIKE '%bha%'
ORDER BY ch.id DESC;

If you’re using MySQL 8.0+, you can use a CTE for even cleaner, more readable code:

-- First get all matching users to filter calls upfront
WITH filtered_users AS (
    SELECT id FROM employees WHERE username LIKE '%bha%'
)
SELECT 
    cl.id,
    cl.contact_number,
    ch.id,
    ch.user_id,
    ch.contact_number,
    ch.call_type,
    ch.call_duration,
    ch.call_date,
    ch.call_time,
    ch.audio_file,
    e.username
FROM call_history ch
JOIN filtered_users fu ON ch.user_id = fu.id
JOIN employees e ON e.id = fu.id
JOIN contact_list cl ON cl.contact_number = ch.contact_number
-- Get latest call per contact for filtered users only
JOIN (
    SELECT contact_number, MAX(id) AS max_call_id
    FROM call_history
    WHERE user_id IN (SELECT id FROM filtered_users)
    GROUP BY contact_number
) ch_max ON ch.id = ch_max.max_call_id
ORDER BY ch.id DESC;

Extra Steps to Prevent This From Happening Again #

• Add critical indexes (this is non-negotiable for performance):
  • Create a composite index on call_history(user_id, contact_number, id)—this covers the filtering, grouping, and joining needs for the subquery.
  • Add an index on contact_list(contact_number) to speed up the join with call_history.
  • Index employees(id, username) to make the user filter faster.
• Tweak timeout settings (only if you need to run large queries):
  You can temporarily increase MySQL’s timeout values to give longer queries time to finish:

  SET GLOBAL wait_timeout = 300;
  SET GLOBAL interactive_timeout = 300;
  

  Just don’t set these too high long-term—they can leave idle connections hanging and waste resources.
• Optimize the fuzzy search:
  The LIKE '%bha%' pattern can’t use indexes. If your business allows it, switch to LIKE 'bha%' (prefix search) which can leverage indexes. If you need middle-of-string searches, consider adding a full-text index on employees.username.

内容的提问来源于stack exchange，提问作者Ankit mehra