MySQL performance – Multiple queries or one inefficient query?

Question

Hi.

I have three tables, each contain some common information, and some information that is unique to the table. For example: uid, date are universal among the tables, but one table can contain a column type while the other contains currency. I need to query the database and get the last 20 entries (date DESC) that have been entered in all three tables. My options are:

1. Query the database once, with one large query, containing three UNION ALL clauses, and pass along fake values for columns, IE:

   FROM    (
       SELECT  uid, date, currency, 0, 0, 0
   

   and later on

   FROM    (
       SELECT  uid, date, 0, type, 0, 0
   

   This would leave me with allot of null-valued fields..

2. OR I can query the database three times, and somehow within PHP sort through the information to get the combined latest 20 posts. This would leave me with an excess of information - 60 posts to look through (LIMIT 20) * 3 - and force me to preform some type of addtional quicksort every time.

What option is better/any alternate ideas?

Thanks.

Answer 1

It all depends of how big your tables are. If each table has a few thousands records, you can go with the first solution (UNION), and you'll be fine.

On bigger tables, I'd probably go with the second solution, mostly because it will use much less ressources (RAM) than the UNION way, and still be reasonably fast.

But I would advise you to think about your data model, and maybe optimize it. The fact you have to use UNION-based queries usually means there's room for optimization, typically by merging the three tables, with an added "type" field (names isn't good at all, but you see my point).

Answer 2

Those two options are more similar than you make it sound.

When you perform the single large query with UNIONs, MySQL will still be performing three separate queries, just as you propose doing in your alternative plan, and then combining them into a single result.

So, you can either let MySQL do the filtering (and LIMIT) for you, or you can do it yourself. Given that choice, letting MySQL do all the work sounds far preferable.

Having extra columns in the result set could theoretically hinder performance, but with so small a result set as your 20 rows, I wouldn't expect it to have any detectable impact.

Answer 3

if you know your limits you can limit each query and had union only run on little data. this should be better as mysql will return only 20 rows and will make the sorting faster then you can in php...

select * from (
  SELECT  uid, date, currency, 0, 0, 0 from table_a order by date desc limit 20
  union
  SELECT  uid, date, 0, type, 0, 0  from table_b order by date desc limit 20
...
) order by date desc limit 20