SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

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

    Hi all, I need your help.

    I've this two queries in MySQL database with time of execution very good:
    PHP Code:
    mysqlSELECT
        COUNT
    (*) AS q,
        
    application
    FROM
        
    `dotable__backup`
    WHERE
        application NOT IN 
    ('home page')
    GROUP BY
        application
    ORDER BY
        q DESC
    ;
    +-------+---------------+
    q     application   |
    +-------+---------------+
    22963 Report one    |
    13957 Report two    |
    |.......|...............|
    |.......|...............|
    |.......|...............|
    +-------+---------------+
    51 rows in set
    Time
    0.187ms




    SELECT
        COUNT
    (*) AS q,
        
    application
    FROM
        
    `dotable`
    WHERE
        application NOT IN 
    ('home page')
    GROUP BY
        application
    ORDER BY
        q DESC
    ;
    +-------+---------------+
    q     application   |
    +-------+---------------+
    23613 Report one    |
    13790 Report two    |
    |.......|...............|
    |.......|...............|
    |.......|...............|
    +-------+---------------+
    59 rows in set
    Time
    0.171ms 
    Now I need this output with the sum of values on two tables:
    PHP Code:
    +-------+---------------+
    q     application   |
    +-------+---------------+
    46576 Report one    |
    27747 Report two    |
    |.......|...............|
    |.......|...............|
    |.......|...............|
    +-------+---------------+ 
    And tried this query join:
    PHP Code:
    SELECT
        COUNT
    (*) AS q,
        
    u.application
    FROM
        
    `dotable__backupU
    JOIN 
    `dotabletmp ON U.application tmp.application
    WHERE
        u
    .application NOT IN ('home page')
    GROUP BY
        u
    .application
    ORDER BY
        q DESC

    In this case the output is:
    Code:
    [Err] 2013 - Lost connection to MySQL server during query
    Can you help me?
    The field `application` in all tables is key Normal BTREE and type CHAR lenght 100, not NULL and default empty string.

    With EXPLAIN SELECT this is the output, it all seems ok:
    Code:
    mysql> EXPLAIN 
    SELECT
    	COUNT(*) AS q,
    	u.application
    FROM
    	`dotable__backup` U
    JOIN `dotable` tmp ON U.application = tmp.application
    WHERE
    	u.application NOT IN ('home page')
    GROUP BY
    	u.application
    ORDER BY
    	q DESC;
    +----+-------------+-------+-------+---------------+--------------+---------+-----------------------+--------+-----------------------------------------------------------+
    | id | select_type | table | type  | possible_keys | key          | key_len | ref                   | rows   | Extra                                                     |
    +----+-------------+-------+-------+---------------+--------------+---------+-----------------------+--------+-----------------------------------------------------------+
    |  1 | SIMPLE      | U     | index | application  | application | 100     | NULL                  | 282144 | Using where; Using index; Using temporary; Using filesort |
    |  1 | SIMPLE      | tmp   | ref   | application  | application | 100     | db.U.application |   4133 | Using index                                               |
    +----+-------------+-------+-------+---------------+--------------+---------+-----------------------+--------+-----------------------------------------------------------+
    2 rows in set
    Thank you in advance.

    PS: congratulations for new graphic forum, beautiful work!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    you're doing a partial cross join

    there are 22963 rows for Report one in the first table, and 23613 rows for Report one in the second table, so in your combined query, there are 542,225,319 rows where the applications are both Report one

    and that's just for Report one

    no wonder the database never answered

    you want a union, not a join
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    thank you very much for help and suggestion, I understand.

    Now I tried this union query:
    PHP Code:
    mysqlSELECT
        SUM
    (q) AS thisSum,
        
    app
    FROM
        
    (
            
    SELECT
                COUNT
    (*) AS q,
                
    u.application AS app
            FROM
                
    `dotable__backupu
            WHERE
                u
    .application NOT IN ('home page')
            
    GROUP BY
                u
    .application
            UNION ALL
                SELECT
                    COUNT
    (*) AS q,
                    
    tmp.application
                FROM
                    
    `dotabletmp
                WHERE
                    tmp
    .application NOT IN ('home page')
                
    GROUP BY
                    tmp
    .application
        
    ) `x`
    GROUP BY
        app
    ORDER BY
        thisSum DESC
    ;

    +---------+----------------------+
    thisSum app                  |
    +---------+----------------------+
    46581   Report one           |
    27676   Report two           |
    |.......  |...............       |
    |.......  |...............       |
    |.......  |...............       |
    +---------+----------------------+
    110 rows in set 
    It's correct?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Miguel61 View Post
    It's correct?
    what is your opinion?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    what is your opinion?
    My opinion is that the query is correct
    But I would like to know your opinion leader ....
    PHP Code:
    SELECT
        FORMAT
    (SUM(q), 0'de_DE') AS thisSum,

    IF (
        
    app IS NULL,
        
    'Tot',
        
    app
    ) AS app
    FROM
        
    (
            
    SELECT
                COUNT
    (*) AS q,
                
    u.application AS app
            FROM
                
    `dotable__backupu
             WHERE
                 u
    .application NOT IN ('home page')
            
    GROUP BY
                u
    .application WITH ROLLUP
            UNION ALL
                SELECT
                    COUNT
    (*) AS q,
                    
    tmp.application AS app
                FROM
                    
    `dotabletmp
                 WHERE
                     tmp
    .application NOT IN ('home page')
                
    GROUP BY
                    tmp
    .application WITH ROLLUP
        
    ) `x`
    GROUP BY
        app
    ORDER BY
        ABS
    (SUM(q)) DESC;
    +---------+----------------------+
    thisSum app                  |
    +---------+----------------------+
    368.468 Tot                  |
    46.586  Report one           |
    27.681  Report two           |
    |.......  |...............       |
    |.......  |...............       |
    |.......  |...............       |
    +---------+----------------------+ 


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •