3

Running this query:

select null, "hello" union all select sleep(4), "world";

on 5.5.29 doesn't return the first row right away as expected. Instead, I have to wait 4 seconds before getting anything. How can I make MySQL return the first row right away?

I am trying to test the handling of slow queries. It makes no sense to divide it, since I would then be testing something else.

Janus Troelsen
  • 20,267
  • 14
  • 135
  • 196
  • 1
    As this is only a **single** statement (and therefor a single result), you can't make it return the first row "right" away. –  Mar 06 '13 at 17:18
  • @a_horse_with_no_name: I doubt that result sets are always fully buffered. If you have a gigantic table and you do `SELECT *`, the results come right away, so this seems to prove it. So, why can't one row come before the other? Bill Karwin has an answer, but there is no reason why the SQL engine shouldn't be able to see how simple the query is, and know that no types need converting. – Janus Troelsen Mar 06 '13 at 17:22
  • Related: http://dba.stackexchange.com/q/7777 – Janus Troelsen Mar 06 '13 at 17:49
  • 1
    @Janus Troelsen - For example, `UNION` can have `ORDER BY` and therefore need to have all its rows before spitting the result. – PM 77-1 Mar 06 '13 at 17:52
  • @PM77-1: but it doesn't, in this case. So there is room for optimization in MySQL. – Janus Troelsen Mar 06 '13 at 17:54
  • The question "How can I make MySQL return the first row right away?" seems unrelated to the problem of "How do I test handling of slow queries?", which is what you really want to know. Exactly what behavior are you trying to test for? Consider that the question posed here may very well be answered "You can't, it's by design." – Bacon Bits Apr 19 '13 at 14:54

2 Answers2

4

How can I make MySQL return the first row right away?

Run two queries separately instead of using a UNION. I admit this answer may seem kind of trite, but it may be the only real answer to your question.

One reason it has to execute the second query is that a UNION requires that all the columns are the same in all unioned subqueries. That is, same in number and compatible in data type. It may even promote the data type based on what is returned by the second query (e.g. expand an INT to a BIGINT). So it has to get at least one row back from all subqueries before it can determine the data type for the first row of the result set.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
0

You have asked for a single records set by using the UNION ALL construct. If you want to see one part followed by the other, tell the SQL engine that; run the first part as one query to return a recordset, and then run the second half as another query to return the second recordset. The two can be joined together on the client side.

Pieter Geerkens
  • 11,775
  • 2
  • 32
  • 52
  • I am trying to test the handling of slow queries. It makes no sense to divide it, since I would then be testing something else. – Janus Troelsen Mar 06 '13 at 17:03
  • 1
    When a question presents wrong or incomplete facts, it solicits wrong or incomplete answers. Add this to your question so incoming readers see the entire problem at once. – Pieter Geerkens Mar 06 '13 at 17:07