[Err] 2013 - Lost connection to MySQL server during query

Databases
1

Hi there, I need your appreciated help.

My tables:

  1. tbl_A = 8667 rows
  2. tbl_B = 8509 rows
  3. tbl_CA = 395760 rows
  4. tbl_CB = 9362 rows

This is query SELECT in MySQL:


SELECT 

    A.c_L
  , CA.L_r
  , CB.c_L

  , DATE(CA.`start event`)
  , TIME(CA.`start event`)

  , CAST(CB.ntc AS UNSIGNED)
  , IF(CA.clients>=0, CA.clients, 0)
  , CAST(CB.ntc-IFNULL(CA.clients,0) AS UNSIGNED)

    FROM tbl_A A

JOIN tbl_CA CA
ON   A.c_L = CONCAT(SUBSTRING_INDEX(CA.L_r,'-',1)
                   ,SUBSTRING_INDEX(CA.L_r,'-',-1), '')

JOIN tbl_CB CB
ON   CA.L_r = CB.c_L

WHERE 1

GROUP BY 
    A.c_L
  , CA.L_r
  , CB.c_L
  , DATE(CA.`start event`)
  , TIME(CA.`start event`);

Output:


Affected rows: 0
Time: 64.529ms

If try this query UPDATE I have this response :


[Err] 2013 - Lost connection to MySQL server during query

Are fields is indexes in all three tables.

Can you help me?
Thanks in advance, cheers.


 UPDATE 

     tbl_B B
   , tbl_A A 

 JOIN tbl_CA CA
 ON   A.c_L = CONCAT(SUBSTRING_INDEX(CA.L_r,'-',1)
                   , SUBSTRING_INDEX(CA.L_r,'-',-1), '')

 JOIN tbl_CB CB 
 ON   CA.L_r = CB.c_L

 SET
     A.NTC  = CAST(CB.ntc AS UNSIGNED) 
   , B.NTC  = CAST(CB.ntc AS UNSIGNED)
2

you haven’t joined B properly, so you’re getting a humoungous cross join

3

I’m sorry… I don’t understand your suggestion…

4

you’ve join A to CA, and you’ve joined CA to CB

but you haven’t joined B properly

that means, for every joined row A-CA-CB, every single row of B will match that row

do a search for cross join or cartesian product to see what that means

5

Thanks for your reply.

I try this version:

 UPDATE 
    tbl_A A 

 JOIN tbl_CA CA
 ON   A.c_L = CONCAT(SUBSTRING_INDEX(CA.L_r,'-',1)
                   , SUBSTRING_INDEX(CA.L_r,'-',-1), '')
 JOIN tbl_CB CB 
 ON   CA.L_r = CB.c_L

 JOIN tbl_B B 
 ON   A.C_L = B.C_L

 SET
     A.NTC  = CAST(IFNULL(CB.ntc, A.cl_A) AS UNSIGNED) 
    ,B.NTC  = CAST(IFNULL(CB.ntc, B.cl_B) AS UNSIGNED)

Output:

Affected rows: 7352
Time: 20.547ms

You are great!
:slight_smile: