And yet another bug on Windows...
Thanks Peter for your reply.
I did indeed test the UNION variation and it helped me a lot. Seems to indicate that packet latency is the real issue here. Unfortunately this brought me to additional problems:
1. Even if I specifiy a LIMIT 1 on the UNION clause, every SELECT inside is executed. I only need it to do the queries until it finds a result.
2. IF only allows for a single column as a result. Wish there was more so I could do some conditional queries.
3. Windows has a problem with "IF..." constructs in remote connections. It will stall the server. Looks like quite a bug. Linux does not expose this behavior.The query is something like:
(SELECT (@a:=colA),(@b:=colB),(IF ((@c IS NULL AND @b>xx),@c:=ID,NULL)) FROM table1 WHERE colC=x AND colD=y AND colE>z ORDER BY colC,colD,ColE LIMIT 1)
UNION
(SELECT (@a:=colA),(@b:=colB),(IF ((@c IS NULL AND @b>xx),@c:=ID,NULL)) FROM table1 WHERE colC=x AND colD>y ORDER BY colC,colD,ColE LIMIT 1)
UNION
(SELECT (@a:=colA),(@b:=colB),(IF ((@c IS NULL AND @b>xx,@c:=ID,NULL)) FROM table1 WHERE colC>x ORDER BY colC,colD,ColE LIMIT 1) LIMIT 1;
SELECT IF ((@c IS NOT NULL),(SELECT colA FROM table2 WHERE ID=@c AND colB<yy ORDER BY ID,colB LIMIT 1),NULL);
SET @c:=NULL
The problem occurs during multiple query execution over C-API (so I send all three queries in a single run).
I have also seen the problem occur when run through the query browser. It works fine against Linux servers and localhost connections. On a remote connection against a Windows Server (I tested several), the "IF" part will totally stall the server (will only process in total over all connections less than 10 queries per second while queries like this are active - vs. 500 per second and connection under normal conditions)