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

Databases
1

Hi all, I need your help.

I’ve this two queries in MySQL database with time of execution very good:

mysql> SELECT
	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:

+-------+---------------+
| q     | application   |
+-------+---------------+
| 46576 | Report one    |
| 27747 | Report two    |
|.......|...............|
|.......|...............|
|.......|...............|
+-------+---------------+

And tried this query join:

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;

In this case the output is:

[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:

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! :slight_smile:

2

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

3

thank you very much for help and suggestion, I understand.

Now I tried this union query:

mysql> SELECT
	SUM(q) AS thisSum,
	app
FROM
	(
		SELECT
			COUNT(*) AS q,
			u.application AS app
		FROM
			`dotable__backup` u
		WHERE
			u.application NOT IN ('home page')
		GROUP BY
			u.application
		UNION ALL
			SELECT
				COUNT(*) AS q,
				tmp.application
			FROM
				`dotable` tmp
			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

what is your opinion?

5

My opinion is that the query is correct :cool:
But I would like to know your opinion leader …

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__backup` u
 		WHERE
 			u.application NOT IN ('home page')
		GROUP BY
			u.application WITH ROLLUP
		UNION ALL
			SELECT
				COUNT(*) AS q,
				tmp.application AS app
			FROM
				`dotable` tmp
 			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           |
|.......  |...............       |
|.......  |...............       |
|.......  |...............       |
+---------+----------------------+