Union query and C++

Question

I use MySQL database and C++ programming language.

In my database there are 20 tables. I produce different messages from these tables. All tables include dateTime columns and I want to sort produced messages.

I did it by using union. But query is so long. Do you think it has another way or my way is correct.

Thans a lot.

Answer 1

It sounds like you're trying to get a sorted list of items from multiple unrelated tables.

In that case I would get the messages from each table individually and then in C++ programmatically sort them into the order you desired. That might make your queries much less complex than having them do the union and sort across many tables.

EDIT: Unfortunately I don't think there is an absolute objective answer as to whether to do this in SQL or programatically in C++. I would suggest a pragmatic approach: Code it the way that's easiest and most clear (usually the two are the same) and see how it performs. If performance is acceptable then you're done. If it's slow, then profile it. If you discover your method is the slow point then you can try the other approach and see how it performs instead.

Answer 2

I don't know if MySQL suports this, but you could put all your unions as a sub-select and do the sorting from the outer select, like this:

select
  f1, f2
from (
  select
    f1, f2
  from
    T1
  union
  select
    f5, f6
  from
    T2
)
order by
  2