mySQL long query question

Scruff

I'm running mySQL on a windowsXP machine and am having some problems runnig long queries.

I have a long query which is made up of 6 SELECT statements joined by UNIONs.
The java application that executes this query will hang once it tries to execute it and doesnt return any error.
If i run the query in MySQL Control Center 0.9.3-beta the same thing happens.

However if i run the query from mysql from the command prompt the query works perfectly.


the only way to get the query to run in either the java app or the control center is to remove one (any one) of the UNION SELECT queries.
I do see the following text in the Control Center history though: "[BIG QUERY] - Query not stored completely in memory.

I tried using SET SQL_BIG_SELECTS = 1 and it looks like it should work. In the Control Center the message come up in the message output screen "8 rows in (0.02) sec" but no results are returned, and the query qindow hangs.

any ideas?????

[CrimsonGhost]

It's quite possible that the API you are using to talk to the MySQL server isn't coping with the length of the query string being passed in. Is there anyway way at all you can shorted your query string?

Scruff

well i could split the query string up into a seperate query for each SELECT in the long query but that would involve making 6 seperate queries as opposed to just one.
The idea of using just the one was to speed it up and it was handier having just one set of returned results to handle.
but if it is the API choking on the long query i might have no other option.....

Scruff

bit of a development...
it appears that the problem might not have to do with the length of the query after all. The last SELECT query when run on its own returns an empty result set.
It seems to be this one when UNIONed with the other SELECTs that return results that is causing everything to hang in the java app and the Control Center.

I increased the length of the query by just adding a few of the UNION SELECT statements that do return results (changed them slightly) and it worked fine.
.......

<edit>
well i'm totally confused now because apparently the 4th SELECT query also returns an empty result and doesnt cause any problems when the last SELECT that also returns an empty result is omitted from the query.

</edit>

[CrimsonGhost]

Any chance you could post the SQL of your query and the expected results. It might help diagnosing the problem.

sceptre

An empty SELECT unioned with a non-empty SELECT should just return the contents of the non-empty SELECT as a rule. Like CrimsonGhost said, let's see it if that's possible.

Scruff

ok. i seem to have narrowed the problem down to one of the SELECT statements.

heres the query (with the problem one in red)

SELECT DISTINCT O.Name AS Name, '1' AS CountType, COUNT(*) AS Count FROM LogIncoming LogIn
INNER JOIN Owner O ON LogIn.OwnerID = O.OwnerID
INNER JOIN Applications a ON LogIn.ApplicationID = a.ApplicationID
GROUP BY c.Name
UNION
SELECT DISTINCT LogOut.Name, '2' AS CountType, COUNT(*) AS Count
FROM LogOutgoing LogOut
GROUP BY LogOut.Name
UNION
SELECT DISTINCT LogOut.Name, '3' AS CountType, COUNT(*) AS Count
FROM LogOutgoing LogOut
WHERE ( (Status = 1 AND Notifications = 0 ) OR Status = 6 OR Status = 8 )
GROUP BY LogOut.Name
UNION
SELECT DISTINCT LogOut.Name, '4' AS CountType, COUNT(*) AS Count
FROM LogOutgoing LogOut
WHERE Status = 10
GROUP BY LogOut.Name
UNION
SELECT DISTINCT LogOut.Name, '5' AS CountType, COUNT(*) AS Count
FROM LogOutgoing LogOut
WHERE Status = 1 AND Notifications > 0
GROUP BY LogOut.Name
UNION
SELECT DISTINCT LogOut.Name, '6' AS CountType, COUNT(*) AS Count
FROM LogOutgoing LogOut
WHERE Status > 20
GROUP BY LogOut.Name

if seems that WHERE Status > 20 is what is killing it.
also kills it if i limit the range WHERE Status between 20 and 30
same for WHERE Status > 20 AMD Statud < 30.

expected output for my db is
+

---

+

---

+

---

+
| Name | CountType | Count |
+

---

+

---

+

---

+
| Test | 1 | 101 |
| Test2 | 1 | 1 |
| Test | 2 | 102 |
| Test3 | 2 | 2 |
| Test2 | 2 | 2 |
| Test | 3 | 102 |
| Test2 | 3 | 2 |
| Test3 | 5 | 2 |
+

---

+

---

+

---

+

if i change it to = anInt it works fine

amen

what datatype is STATUS ?
you should also have an order by clause in your statement